Moving many rows to another sheet?

KenR

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)

Gia Thinh

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

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.

KenR

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?

Gia Thinh

"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.

KenR

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.