从CSV向Google Sheets特定列追加数据遇API错误及异常值问题

问题 #

我有一个包含日期列和其他数据列的CSV文件，需要将其数据追加到Google Sheets中：日期列对应追加至A列，其余数据从S列开始追加，中间列留空。

我使用以下代码尝试实现：

import pandas as pd
import pygsheets
import gspread
from gspread_dataframe import set_with_dataframe
from google.oauth2.service_account import Credentials

def csv_to_sheets():
   tokenPath ='path for service account file.json'
   scopes = ['https://www.googleapis.com/auth/spreadsheets',
             'https://www.googleapis.com/auth/drive']

   credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
   gc = gspread.authorize(credentials)
   gs = gc.open('csv_to_gSheet')
   workSheet1 = gs.worksheet('sheet1')
   my_csv = pd.read_csv("my csv file path")
   my_csv_values = my_csv.values.tolist()
   for ele in my_csv_values:
       workSheet1.update("A" + str(len(workSheet1.get_all_values()) + 1), ele[0], value_input_option="USER_ENTERED")
       workSheet1.update("S" + str(len(workSheet1.get_all_values()) + 1), ele[1:], value_input_option="USER_ENTERED")

csv_to_sheets()

运行时出现错误：

gspread.exceptions.APIError: {'code': 400, 'message': "Invalid value at 'data.values[0]' (type.googleapis.com/google.protobuf.ListValue)

此外，首次运行脚本时，A列和S列以外的其余列数据追加正常，但S列17-23行出现CSV中不存在的无关值；第二次运行时，S列及其他列数据追加均正常。

想了解首次运行时S列异常值的成因，以及如何实现正确的数据追加。

解答 #

一、首次运行S列异常值的成因 #

1. 行号计算逻辑错误：循环中每次调用len(workSheet1.get_all_values())获取当前行数，但更新A列后，工作表行数已增加1，此时计算S列行号会比实际应插入的行号大1，导致数据错位覆盖到已有行（或填充到错误位置）。
2. API缓存/同步延迟：首次运行时，get_all_values()可能读取的是缓存的旧数据，进一步加剧行号计算偏差，最终把数据写入错误行，出现无关值（大概率是工作表中这些行的原有旧数据）。

二、正确的数据追加实现方案 #

针对API错误和行号错位问题，通过提前计算总行数、批量构造完整行、批量更新API的方式解决：

优化后的代码

import pandas as pd
import gspread
from google.oauth2.service_account import Credentials

def csv_to_sheets():
    tokenPath = 'path for service account file.json'
    scopes = ['https://www.googleapis.com/auth/spreadsheets',
              'https://www.googleapis.com/auth/drive']

    credentials = Credentials.from_service_account_file(tokenPath, scopes=scopes)
    gc = gspread.authorize(credentials)
    gs = gc.open('csv_to_gSheet')
    workSheet1 = gs.worksheet('sheet1')
    
    # 仅调用一次API获取当前总行数，避免循环中重复计算的偏差
    current_total_rows = len(workSheet1.get_all_values())
    # 读取CSV数据
    my_csv = pd.read_csv("my csv file path")
    
    # 构造完整行数据：日期 + 17个空值（对应B-R列） + 其余数据列
    rows_to_insert = []
    for _, row in my_csv.iterrows():
        date_value = row.iloc[0]
        other_data = row.iloc[1:].tolist()
        full_row = [date_value] + [''] * 17 + other_data
        rows_to_insert.append(full_row)
    
    # 批量写入数据
    if rows_to_insert:
        start_row = current_total_rows + 1
        # 计算结束列号：A(1) + 17空列 + 其他数据列数
        end_col_num = 1 + 17 + len(other_data)
        end_col_letter = chr(64 + end_col_num)
        # 构造写入范围，例如"A2:X10"
        write_range = f"A{start_row}:{end_col_letter}{start_row + len(rows_to_insert) - 1}"
        # 批量更新，避免循环调用API的问题
        workSheet1.update(write_range, rows_to_insert, value_input_option="USER_ENTERED")

csv_to_sheets()

关键优化点

1. 提前计算总行数：仅调用一次get_all_values()获取当前行数，避免循环中频繁调用导致的行号计算错误。
2. 构造完整行数据：每行包含日期、中间17个空值（对应B-R列）、其余数据，确保A列和S列的数据一一对应，不会错位。
3. 批量API更新：一次性写入所有行，减少API调用次数，提升效率同时避免多次调用带来的缓存同步问题。
4. 修复API类型错误：原代码中给单个单元格范围传递列表值导致类型不匹配，批量写入整行的方式从根源解决了这个400错误。

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