Python中API嵌套JSON写入Google Sheets失败的解决方案求助

问题根源 #

Google Sheets API的批量写入接口（如values.append/values.update）仅接受纯二维数组作为数据输入——外层数组对应表格行，内层数组对应每行的单元格值。当API返回的JSON包含嵌套数组（如custom_field_collection）时，直接转换为数组会保留嵌套结构，导致Sheets API无法解析，触发HttpError 400。

解决方案：扁平化嵌套JSON #

编写递归函数将嵌套JSON转换为扁平的键值对结构，把嵌套路径拼接为扁平键名（如custom_field_collection[0].name转为custom_field_collection_0_name），最终生成符合要求的二维数组。

扁平化函数实现 #

def flatten_json(nested_json, parent_key='', sep='_'):
    flattened = {}
    for k, v in nested_json.items():
        new_key = f"{parent_key}{sep}{k}" if parent_key else k
        if isinstance(v, dict):
            flattened.update(flatten_json(v, new_key, sep=sep))
        elif isinstance(v, list):
            for i, item in enumerate(v):
                list_key = f"{new_key}{sep}{i}"
                if isinstance(item, dict):
                    flattened.update(flatten_json(item, list_key, sep=sep))
                else:
                    flattened[list_key] = item
        else:
            flattened[new_key] = v
    return flattened

修改后的完整代码 #

import gspread
from google.oauth2.service_account import Credentials
import requests

# 初始化Google Sheets连接
SCOPE = ["https://www.googleapis.com/auth/spreadsheets"]
CREDS = Credentials.from_service_account_file('service_account.json', scopes=SCOPE)
CLIENT = gspread.authorize(CREDS)
SPREADSHEET = CLIENT.open('Your_Sheet_Name')
WORKSHEET = SPREADSHEET.worksheet('Sheet1')

# 调用目标API获取数据
def fetch_api_data():
    response = requests.get('https://your-api-endpoint.com/data')
    response.raise_for_status()
    return response.json()

# 处理数据并写入Sheets
def write_to_sheets(data):
    # 扁平化每条数据
    flattened_items = [flatten_json(item) for item in data]
    if not flattened_items:
        print("No data to write")
        return
    
    # 提取表头（所有扁平键的集合）
    headers = list({k for item in flattened_items for k in item.keys()})
    # 转换为二维数组：表头 + 每行数据
    rows = [headers]
    for item in flattened_items:
        row = [item.get(header, '') for header in headers]
        rows.append(row)
    
    # 写入Sheets
    WORKSHEET.clear()
    WORKSHEET.update('A1', rows)
    print("Data written successfully")

if __name__ == "__main__":
    api_data = fetch_api_data()
    write_to_sheets(api_data)

示例验证 #

正常JSON（无嵌套）处理后 #

原JSON：

{
  "id": 123,
  "name": "Test Project",
  "status": "active"
}

扁平化后转为行数据：[123, "Test Project", "active"]，可直接写入Sheets。

含嵌套数组的JSON处理后 #

原JSON：

{
  "id": 456,
  "name": "Nested Project",
  "custom_field_collection": [
    {
      "name": "Priority",
      "value": "High"
    },
    {
      "name": "Category",
      "value": "Development"
    }
  ]
}

扁平化后的键值对：

{
  "id": 456,
  "name": "Nested Project",
  "custom_field_collection_0_name": "Priority",
  "custom_field_collection_0_value": "High",
  "custom_field_collection_1_name": "Category",
  "custom_field_collection_1_value": "Development"
}

转换为行数据后可正常写入Sheets，不会触发400错误。

错误日志说明 #

HttpError 400的核心原因是请求体中的数据结构不符合Sheets API要求——API无法识别嵌套数组，仅接受单层数组构成的二维表格结构。通过扁平化处理后，数据结构完全匹配API要求，即可解决该错误。

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