使用Python无法在Google Sheet列中应用公式的问题求助

已成功通过Python将CSV数据导入Google Sheet的S至AE列，但在给AF列应用=SUM(S:AE)公式时触发错误：

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

同时遇到参数数量错误提示，且需要从第17行开始应用该公式（前16行已有预填充数据）。

原代码如下：

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


  row = str(len(workSheet1.get_all_values()) + 1)
  workSheet1.batch_update([
  {"range": "A" + row,"values": [[r[0]] for r in my_csv_values]},
  {"range": "S" + row,"values": [r[1:] for r in my_csv_values]},
  ],value_input_option="USER_ENTERED")

  workSheet1.batch_update("AF","=SUM(S:AE)")
  csv_to_sheets()

错误根源 #

1. batch_update用法违规：该方法要求传入操作字典列表，直接传范围和公式的方式不符合API规范，导致参数格式错误。
2. 公式范围逻辑错误：原公式=SUM(S:AE)会计算整列总和，而非当前行的S-AE列数据；且未指定从第17行开始应用。
3. 无限递归调用：函数末尾的csv_to_sheets()会导致无终止递归，需移除。

修复方案 #

1. 调整公式为逐行计算：将公式改为=SUM(S{row}:AE{row})，确保每行计算自身对应列的总和。
2. 规范batch_update调用：按API要求构造操作列表，公式需包裹在二维列表中（符合values参数的格式要求）。
3. 从第17行批量写入公式：根据CSV数据行数，生成对应AF列的范围和公式集合。
4. 移除递归调用：删除函数末尾的csv_to_sheets()。

修正后完整代码 #

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')
    
    # 读取CSV数据并获取行数
    my_csv = pd.read_csv("my csv file path")
    my_csv_values = my_csv.values.tolist()
    csv_row_count = len(my_csv_values)
    
    # 固定从第17行开始写入数据和公式
    start_row = 17
    end_row = start_row + csv_row_count - 1

    # 批量写入CSV数据到A列和S-AE列
    workSheet1.batch_update([
        {"range": f"A{start_row}:A{end_row}", "values": [[r[0]] for r in my_csv_values]},
        {"range": f"S{start_row}:AE{end_row}", "values": [r[1:] for r in my_csv_values]},
    ], value_input_option="USER_ENTERED")

    # 构造AF列的公式操作列表
    formula_updates = []
    for row in range(start_row, end_row + 1):
        formula_updates.append({
            "range": f"AF{row}",
            "values": [[f"=SUM(S{row}:AE{row})"]]
        })
    
    # 批量写入公式
    workSheet1.batch_update(formula_updates, value_input_option="USER_ENTERED")

# 执行函数
csv_to_sheets()

补充说明 #

• 若需要根据已有数据动态计算起始行（而非固定17行），可将start_row改为start_row = len(workSheet1.get_all_values()) + 1，但需确保前16行已存在数据。
• value_input_option="USER_ENTERED"确保公式被解析为可执行的函数，而非纯文本。

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