Multiple update requests to the same smartsheet using the python sdk API call

Question

I am using the Python SDK API Call update rows in a Smartsheet sheet. We are issuing simultaneous API calls, and hence some requests are failing with error:

Process finished with exit code 0
{"response": {"statusCode": 500, "reason": "Internal Server Error", "content": {"errorCode": 4004, "message": "Request failed because sheetId ##### is currently being updated by another request that uses the same access token.  Please retry your request once the previous request has completed.", "refId": "####"}}}

Here is the code that's causing this error when running multiple times simultaneously to update different rows in the same sheet:

import smartsheet

SMARTSHEET_ACCESS_TOKEN = "XXXXXXXXXXXXXXXXXXXXXXX"
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
sheet = smartsheet_client.Sheets.get_sheet('XXXXXXXXXXXXXX')

column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id
# print(column_map)
row_map = {}
i = 0
# counter
for rows in sheet.rows:
    row_map[i] = rows.id
    i = i + 1
# print(row_map)
new_cell = smartsheet_client.models.Cell()
# Update column Remaining

error = 0
new_cell.column_id = column_map['Last End Time']
new_cell.value = '02/23/2023 12:13:57 AM'
new_cell.strict = False
get_row = smartsheet.models.Row()
get_row.id = row_map[int(5) - 1]
get_row.cells.append(new_cell)
api_response = smartsheet_client.Sheets.update_rows('xxxxxxxxxxxxxxxxxxxx', [get_row])
print(api_response)

How can we avoid getting this error if we want to use the Python SDK to update multiple rows in a sheet?

Answer 1

import smartsheet

SMARTSHEET_ACCESS_TOKEN = "XXXXXXXXXXXXXXXXXXXXXXX"
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
sheet = smartsheet_client.Sheets.get_sheet('XXXXXXXXXXXXXX')

column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id
# print(column_map)
row_map = {}
i = 0
# counter
for rows in sheet.rows:
    row_map[i] = rows.id
    i = i + 1
# print(row_map)
new_cell = smartsheet_client.models.Cell()
# Update column Remaining

error = 0
new_cell.column_id = column_map['Last End Time']
new_cell.value = '02/23/2023 12:13:57 AM'
new_cell.strict = False
get_row = smartsheet.models.Row()
get_row.id = row_map[int(5) - 1]
get_row.cells.append(new_cell)
api_response = smartsheet_client.Sheets.update_rows('xxxxxxxxxxxxxxxxxxxx', [get_row])
print(api_response)

This is code to update a particular cell in the row. We will be calling this simultaneously to update different rows.

Answer 2

The way to avoid the concurrency error (errorCode: 4004) that you're getting is to not call the Update Row(s) operation one time for each and every row you want to update, but rather, just call it ONCE to request all row updates in one batch.

The following code example implements this approach I've described:

• It specifies data for the first row to update, then adds that Row object to the rows_to_update list.
• Then it specifies data for the next row to update, and adds that Row object to the rows_to_update list.
• Once all row updates have been added to the rows_to_update list, it calls the Update Row(s) operation (smartsheet_client.Sheets.update_rows) ONE TIME to apply the entire batch of updates at once.

# get the sheet
sheet_id = 3932034054809476
sheet = smartsheet_client.Sheets.get_sheet(sheet_id) 

# create map of column names (title) to column IDs (id)
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

# create map of row indexes to row IDs (id)
row_map = {}
i = 0
for rows in sheet.rows:
    row_map[i] = rows.id
    i = i + 1

# initialize a list to hold the Row objects for we'll want to update
rows_to_update = []

#---------------
# specify data for first row update
#---------------
# create Cell object that contains info about the cell to update
new_cell = smartsheet_client.models.Cell()
new_cell.column_id = column_map['Last End Time']
new_cell.value = '02/23/2023 12:13:57 AM'
new_cell.strict = False

# create Row object that contains info about the first row to update
row_update = smartsheet.models.Row()
row_update.id = row_map[int(5) - 1]
row_update.cells.append(new_cell)

# add the Row object to the rows_to_update list
rows_to_update.append(row_update)

#---------------
# specify data for second row update
#---------------
# create Cell object that contains info about the cell to update
new_cell = smartsheet_client.models.Cell()
new_cell.column_id = column_map['Last End Time']
new_cell.value = '02/23/2023 12:13:57 AM'
new_cell.strict = False

# create Row object that contains info about the first row to update
row_update = smartsheet.models.Row()
row_update.id = row_map[int(5) - 2]
row_update.cells.append(new_cell)

# add the Row object to the rows_to_update list
rows_to_update.append(row_update)

#---------------
# continue with this approach, 
# specifying data for each row update,
# and adding each one to the rows_to_update list,
# until all rows to update have been added to the list
#---------------

#---------------
# FINALLY: 
# now that we've added all of the rows to update to the rows_to_update list,
# call the UPDATE ROWS operation ONCE, passing in that list of rows as the second parameter
#---------------
api_response = smartsheet_client.Sheets.update_rows(sheet_id, rows_to_update)
# print(api_response)

The approach shown in example code above is rather tedious -- because there's separate code to specify each cell to update and each row to update. It'd be much more efficient if you could build the rows_to_update list by using a loop. This next code example illustrates this approach -- in this example:

• The same column (Last End Time) in every row of the sheet gets updated with the same value (02/23/2023 12:13:57 AM).
• the for x in range(0, i) loop iterates through all the rows in the sheet, specifying the same column data for each row and then adding the updated row to the rows_to_update list.
• Once the loop has finished, and all row updates have been added to the rows_to_update list, the Update Row(s) operation (smartsheet_client.Sheets.update_rows) is called ONE TIME to apply the entire batch of updates at once.

# get the sheet
sheet_id = 3932034054809476
sheet = smartsheet_client.Sheets.get_sheet(sheet_id) 

# create map of column names (title) to column IDs (id)
column_map = {}
for column in sheet.columns:
    column_map[column.title] = column.id

# create map of row indexes to row IDs (id)
row_map = {}
i = 0
for rows in sheet.rows:
    row_map[i] = rows.id
    i = i + 1

# initialize a list to hold the Row objects for we'll want to update
rows_to_update = []

# create Cell object that contains info about the cell to update
new_cell = smartsheet_client.models.Cell()
new_cell.column_id = column_map['Last End Time']
new_cell.value = '02/23/2023 12:13:57 AM'
new_cell.strict = False

#---------------
# loop through all rows in the sheet,
# specifying data for each row update
#
# note: in this example, the same column in each row gets updated with the same value 
# -- if you wanted to update different columns and/or specify different values in various
# rows, you'd need to move the "new_cell" lines above into this loop
# so that you could specify different column(s) and or value(s) to update for each row
#---------------
for x in range(0, i):

    row_update = smartsheet.models.Row()
    row_update.id = row_map[x]
    row_update.cells.append(new_cell)
    
    # add the Row object to the rows_to_update list
    rows_to_update.append(row_update)

#---------------
# FINALLY: 
# now that we've added all of the rows to update to the rows_to_update list,
# call the UPDATE ROWS operation ONCE, passing in that list of rows as the second parameter
#---------------
api_response = smartsheet_client.Sheets.update_rows(sheet_id, rows_to_update)

This example code is meant to illustrate how to update rows in 'bulk'. Please note that your specific implementation may vary, depending on your criteria for updating cells and rows. For example:

• If you want to update a different column and/or specify a different update value for the various rows, you'd want to move the lines of code that begin with new_cell. to inside the for loop.
• If you only want to update certain rows in the sheet, you may need to add an if statement within the for loop to determine whether or not the current row meets the criteria for update.
• etc.

But again -- the key is this: don't call the Update Row(s) operation once for every row you want to update; instead, build up a list of your rows to update and call the Update Row(s) operation ONE TIME to update all of the rows at once.

PS - I've edited your original post to include the code you added as an 'Answer'. (Please delete the Answer where you added the code.) In the future, when you need to add additional information about a post, you should Edit your original post to add the info, rather than adding it as an answer or a comment.

PPS - If this answer sufficiently addresses your question, please mark it as "Accepted" -- as doing so will make others more likely to benefit from this info in the future. Thanks!