Moving many rows to another sheet?
My task is to move 3500+ rows to an archive sheet every two weeks. I'm using the python SDK Sheets.move_rows() command but am running into a timeout error (4002) after just 250 rows (granted there are a lot of columns). Can anyone recommend a better method than telling python to loop through the list, moving 250 at a time? 3500 / 250 = a lot...
I attempted to use the automation workflow first but it threw an error too (it didn't say which).
The project allows the rows to be added and edited until a supervisor marks the range of rows as complete on Monday afternoon every other week. Once completed all the rows need to be archived.
Python Script: (my apologies to all the professionals - I'm a newbie)
import os, smartsheet
token = '[API TOKEN]'
os.environ['SMARTSHEET_ACCESS_TOKEN'] = token
sheet_id_Intake = '...........0000' # Sheet_ID can be found by going to the Sheet -> Properties -> Sheet ID
sheet_id_Archive = '...........0001'
filter_ReadytoArchive # this is a sheet filter created in smartsheets. I used a postman api call to get the filterID
# Initializes a smartsheets instance
Sheet = smartsheet.Smartsheet()
Sheet.errors_as_exceptions(True)
# gets the list of rows needing to be archived from the intake sheet
archive_rows = Sheet.Sheets.get_sheet(sheet_id_Intake, exclude=["filteredOutRows"], filter_id=Filter_ReadytoArchive)
# creates a python list of the row id's - a maximum of 250 rows
row_list = []
i=0
for row in archive_rows.rows:
if i >= 250:
break
else:
row_list.append(row.id)
i += 1
# sends request to move rows
try:
response = Sheet.Sheets.move_rows(sheet_id_Intake,smartsheet.models.CopyOrMoveRowDirective({'row_ids': row_list,'to': smartsheet.models.CopyOrMoveRowDestination({'sheet_id': int(sheet_id_Archive )})})) # note I had to convert the Archive sheet id to an integer - for some reason it wouldn't take a string like everywhere else
print(response)
except Exception as e:
print (e)
Best Answer
-
Hi @KenR
To simulate your situation, I attempted to use "Move row" automation workflow with 12PM every day scheduling as screenshots below. It works well with moving 4000 rows from the source sheet to the target sheet.
You may want to double check your workflow and share the screenshot so that we can help.
Gia Thinh Technology - Smartsheet Solution Partner.
Answers
-
Hi @KenR
To simulate your situation, I attempted to use "Move row" automation workflow with 12PM every day scheduling as screenshots below. It works well with moving 4000 rows from the source sheet to the target sheet.
You may want to double check your workflow and share the screenshot so that we can help.
Gia Thinh Technology - Smartsheet Solution Partner.
-
Hello Gia, thanks for the reply! I have taken another look at the workflow: This is actually a smaller sheet with 327 rows being moved (46 columns). To create the Archive sheet I right clicked the original sheet and selected "save as new". Then removed the formulas because they index/match to the larger source sheet which will also be archived. Column validations were also removed because the selections will change over time.
Interesting... When retry the workflow it succeeded. So I reset the tables and tried again. I got the same result - 1st try threw a failed message but the retry succeeded - rather quickly too. I am continuing to troubleshoot but any ideas?
-
"Run workflow now" is used mainly for testing with a limited rows I thought.
For testing, you can create one more move rows workflow that is triggered when Completed change to checked. This is more natural testing.
Gia Thinh Technology - Smartsheet Solution Partner.
-
Yep that's it. The workflow ran successfully the first time but due to the size it took a while. Because "Run workflow now" was intended for testing with limited rows, it displayed an error like it had timed out. Then when I attempted the workflow a second time, all the rows required had already been moved - naturally it completed the workflow very quickly.
Thank you Gia! I will mark your answer as correct - taking another look at the workflow and examining the error proved the correct solution.
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.1K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 450 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 34 Webinars
- 7.3K Forum Archives