使用Smartsheet Python SDK并发更新同表格遇4004错误的解决方法

使用Smartsheet Python SDK调用API更新表格行时，并发发起针对同一张表格不同行的更新请求，会触发以下内部服务器错误：

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": "####"}}}

触发错误的并发执行代码示例：

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

row_map = {}
i = 0
for rows in sheet.rows:
    row_map[i] = rows.id
    i = i + 1

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
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)

请问如何使用Python SDK更新表格多行时避免该错误？

解决方案 #

1. 批量提交多行更新（最优方案） #

Smartsheet API支持在单个update_rows请求中提交多行更新，这是官方推荐的方式，可完全避免并发冲突。将所有需要更新的行对象放入同一个列表，一次性发送请求。

示例代码（基于原有代码修改）：

import smartsheet

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

# 简化映射构造
column_map = {col.title: col.id for col in sheet.columns}
row_map = {i: row.id for i, row in enumerate(sheet.rows)}

# 存储所有待更新行的列表
rows_to_update = []

# 构造第5行的更新
cell1 = smartsheet_client.models.Cell()
cell1.column_id = column_map['Last End Time']
cell1.value = '02/23/2023 12:13:57 AM'
cell1.strict = False
row1 = smartsheet.models.Row()
row1.id = row_map[4]  # 对应原代码的row_map[int(5)-1]
row1.cells.append(cell1)
rows_to_update.append(row1)

# 可继续添加更多行的更新
cell2 = smartsheet_client.models.Cell()
cell2.column_id = column_map['Last End Time']
cell2.value = '02/23/2023 01:15:30 AM'
cell2.strict = False
row2 = smartsheet.models.Row()
row2.id = row_map[9]  # 示例更新第10行
row2.cells.append(cell2)
rows_to_update.append(row2)

# 一次性提交所有更新请求
api_response = smartsheet_client.Sheets.update_rows(sheet.id, rows_to_update)
print(api_response)

2. 必须并发时实现重试机制 #

如果业务场景无法避免并发请求，需针对4004错误添加重试逻辑，捕获错误后等待一段时间再重新发起请求。

示例代码（带指数退避的重试逻辑）：

import smartsheet
import time

SMARTSHEET_ACCESS_TOKEN = "XXXXXXXXXXXXXXXXXXXXXXX"
smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
sheet_id = 'xxxxxxxxxxxxxxxxxxxx'

def update_row_with_retry(row_obj, max_retries=3, initial_delay=2):
    delay = initial_delay
    for attempt in range(max_retries):
        try:
            return smartsheet_client.Sheets.update_rows(sheet_id, [row_obj])
        except smartsheet.exceptions.ApiError as e:
            if e.error_code == 4004:
                print(f"请求冲突，等待{delay}秒后重试...")
                time.sleep(delay)
                delay *= 2  # 指数退避，避免频繁重试
            else:
                # 非4004错误直接抛出
                raise
    raise Exception("重试次数耗尽，更新失败")

# 构造待更新行对象
sheet = smartsheet_client.Sheets.get_sheet(sheet_id)
column_map = {col.title: col.id for col in sheet.columns}
row_map = {i: row.id for i, row in enumerate(sheet.rows)}

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
target_row = smartsheet.models.Row()
target_row.id = row_map[4]
target_row.cells.append(new_cell)

# 调用带重试的更新函数
response = update_row_with_retry(target_row)
print(response)

3. 调整客户端并发配置 #

检查并限制Smartsheet客户端的并发连接数，避免同一令牌下同时发起过多请求。可通过修改客户端的重试和超时参数，或调整底层HTTP连接池大小：

smartsheet_client = smartsheet.Smartsheet(SMARTSHEET_ACCESS_TOKEN)
# 调整客户端重试次数和超时时间
smartsheet_client.configuration.max_retries = 2
smartsheet_client.configuration.timeout = 10

内容的提问来源于stack exchange，提问作者Chandralekha G