Google Sheets API批量更新报错：请求列表无法被Body识别

问题描述 #

使用Google Sheets API v4时，通过service_account完成认证后，将Pandas DataFrame转成列表构建updateCells请求列表，传入batchUpdate的body执行时出错。已确认requests列表有有效值，但API无法正确读取。

原代码 #

# Authenticate into Target worksheet
# Provide the right methods from google and the right scopes (app services)
SERVICE_ACCOUNT_FILE = 'keys.json'
SCOPES = ['https://www.googleapis.com/auth/spreadsheets']

creds = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# The ID for the target spreadsheet.
TARGET_WORKBOOK = 'xxx'

service = build('sheets', 'v4', credentials=creds)

sheet_id = 2083229665

# Build the service object for the Google Sheets API
service = build('sheets', 'v4', credentials=creds)

# Load the DataFrame
data = [['Name', 'Age'], ['Alice', 25], ['Bob', 30],['steve','55'], ['gayle','54']]

df = pd.DataFrame(data)

datalist = df.values.tolist()


# Define the range of the data
range_ = 'ExpenseLog!A1:' + chr(ord('A') + len(df.columns) - 1) + str(len(df)) 


# Define the request body Execute the request
requests = []

for i, row in enumerate(datalist):
# Format the row values into the proper structure
    values = [{'userEnteredValue': {'stringValue': cell}} for cell in row]

# Create the updateCells request for the current row

    request = {
        'updateCells': {
            'range': {
                'sheetId' : sheet_id,
                'startRowIndex': i,
                'endRowIndex': i + 1,
                'startColumnIndex': 0,
                'endColumnIndex': len(row)
                },
            'rows': [
                {
                'values': values
                }
                ],
             'fields': 'userEnteredValue'
            }
        }
# Append the request to the list of requests
requests.append(request)

# Perform the batch update
result = service.spreadsheets().batchUpdate(spreadsheetId=TARGET_WORKBOOK, 
        body={'requests': requests}).execute()

问题原因 #

原代码中所有单元格都强制使用stringValue字段，但DataFrame里包含整数类型数据（如25、30），Google Sheets API要求不同数据类型对应不同的字段标识，类型不匹配会导致API解析失败。

解决方案 #

1. 修正数据类型映射逻辑 #

将构建单元格值的代码改为根据数据类型选择对应字段：

# 替换原values构建的循环
values = []
for cell in row:
    if isinstance(cell, str):
        values.append({'userEnteredValue': {'stringValue': cell}})
    elif isinstance(cell, (int, float)):
        values.append({'userEnteredValue': {'numberValue': cell}})
    # 如需支持布尔、日期等类型，可添加对应分支

2. 优化请求结构（可选） #

原代码为每一行创建一个updateCells请求，可合并为单个请求减少API调用次数：

# 合并为单个batchUpdate请求
requests = [{
    'updateCells': {
        'range': {
            'sheetId': sheet_id,
            'startRowIndex': 0,
            'endRowIndex': len(datalist),
            'startColumnIndex': 0,
            'endColumnIndex': len(df.columns)
        },
        'rows': [
            {'values': [
                {'userEnteredValue': {'stringValue': c} if isinstance(c, str) else {'numberValue': c}}
                for c in row
            ]}
            for row in datalist
        ],
        'fields': 'userEnteredValue'
    }
}]

3. 其他检查点 #

• 确认service_account邮箱已被添加到目标表格的共享列表，权限设为编辑者。
• 删除重复创建的service对象，保留一行service = build('sheets', 'v4', credentials=creds)即可。

验证方法 #

1. 打印修正后的requests列表，检查每个单元格的userEnteredValue字段是否与数据类型匹配。
2. 重新执行batchUpdate代码，查看目标表格是否成功写入数据。

内容的提问来源于stack exchange，提问作者Testerfirst Testerlast