I use Smartsheet to govern a project that does ~10k tasks per year so automating as much of the project is extremely helpful. I have multiple python scripts that are updating sheets and grabbing data form sheets but this is the first time i have tried to move data in a sheet.
I am trying to move a row below another row when the text in the primary cell of one row is completely in another. this way that card will always be directly below the other. this way the "Vent" card is always next to the task card that requires the vent. the code is pulling all the right data and rows and then assigning the proper rows to move but the move fails with the following error
{"response": {"statusCode": 500, "reason": "Internal Server Error", "content": {"errorCode": 4000, "message": "An unexpected error has occurred. Please contact the Support team at https://help.smartsheet.com/contact for assistance.", "refId": "cs5d1q"}}}
this is the response return
response = <smartsheet.models.error.Error object at 0x0000025AC047CA50>,
sheet_id = 2763833039867780,
directive = <smartsheet.models.copy_or_move_row_directive.CopyOrMoveRowDirective object at 0x0000025ABFEBB0E0>
this is my code:
smartsheet_client = smartsheet.Smartsheet(access_token)
sheet_id = 2763833039867780
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
tool_col_id = next((col.id for col in sheet.columns if col.title == "Tool"), None)
task_col_id = next((col.id for col in sheet.columns if col.title == "Task"), None)
if not tool_col_id or not task_col_id:
raise Exception("Columns 'Tool' and 'Task' not found.")
tool_task_map = {}
for row in sheet.rows:
tool_value = next((cell.value for cell in row.cells if cell.column_id == tool_col_id), None)
task_value = next((cell.value for cell in row.cells if cell.column_id == task_col_id), None)
if tool_value and task_value:
tool_task_map[row.id] = (tool_value, task_value)
rows_to_move = []
for row_id, (tool_value, task_value) in tool_task_map.items():
for other_id, (other_tool_value, other_task_value) in tool_task_map.items():
if (
row_id != other_id and
tool_value == other_tool_value and
task_value in other_task_value
):
rows_to_move.append((row_id, other_id))
break
for row_id, target_id in rows_to_move:
directive = smartsheet.models.CopyOrMoveRowDirective()
directive.row_ids = [row_id]
directive.to = smartsheet.models.CopyOrMoveRowDestination()
directive.to.row_id = target_id
directive.to.position = 'Below'
response = smartsheet_client.Sheets.move_rows(sheet_id, directive)
print(f"Moved row {row_id} below {target_id}: {response.message}")
print("All matching rows processed.")
any help would be much appreciated