Python调用Google Sheets API追加数据时遇JSON格式错误排查

尝试将Places API提取的数据存入Google Sheets，使用服务账号传递凭据构建请求时，出现Invalid JSON payload received. Unknown name错误，具体为HttpError 400，提示根元素必须为消息体。

代码 #

from __future__ import print_function

import requests
import urllib.parse as urlparse
from googleapiclient import discovery
import os.path
from google.auth.transport.requests import Request
from google.oauth2.credentials import Credentials
from google_auth_oauthlib.flow import InstalledAppFlow
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError


SCOPES = ['https://www.googleapis.com/auth/spreadsheets',
          'https://www.googleapis.com/auth/drive',
          'https://www.googleapis.com/auth/drive.file']

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1_oKFw7gYmUWDUZxZ6Dgo1uLM9Tf_Bc-4bnq4jiJbQUs'
SAMPLE_RANGE_NAME = 'Sheet1'
creds = None

if not creds or not creds.valid:
    if creds and creds.expired and creds.refresh_token:
        creds.refresh(Request())
    else:
        flow = InstalledAppFlow.from_client_secrets_file(
            'credentials.json', SCOPES)
        creds = flow.run_local_server(port=0)
    # Save the credentials for the next run
    with open('token.json', 'w') as token:
        token.write(creds.to_json())
        
spreadsheet_id = '***************'
range_ = 'Sheet1!'
value_input_option = 'RAW'        



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


value_range_body = ['a']


request = service.spreadsheets().values().append(spreadsheetId=spreadsheet_id, range=range_,                
valueInputOption=value_input_option, body=value_range_body)
response = request.execute()

错误信息 #

请求对应接口时返回HttpError 400，错误信息："Invalid JSON payload received. Unknown name "": Root element must be a message."。详情："[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations': [{'description': 'Invalid JSON payload received. Unknown name "": Root element must be a message.'}]}]"

问题原因及解决方案 #

• 核心问题：value_range_body的格式不符合Google Sheets API要求。API规定请求体必须是包含values字段的字典，而非直接传入列表。
• 修正步骤：
  1. 将value_range_body改为字典结构，把要写入的数据放在values键对应的二维列表中（Google Sheets以行列结构存储数据，即使单个单元格也需要用二维列表表示一行一列的数据）。
  2. 可选优化：range_参数可简化为'Sheet1'，API会自动定位到工作表的第一个空白行完成追加操作。

修正后的关键代码片段：

# 修正后的请求体格式
value_range_body = {
    "values": [["a"]]  # 二维列表，子列表代表一行数据，每个元素对应一列
}

# 执行追加请求
request = service.spreadsheets().values().append(
    spreadsheetId=spreadsheet_id, 
    range=range_,                
    valueInputOption=value_input_option, 
    body=value_range_body
)
response = request.execute()

内容的提问来源于stack exchange，提问作者hitesh goyal