如何解决Google API调用自定义表格函数时的间歇性失败问题

问题背景 #

通过Python调用Google Sheets API访问包含自定义函数DeadlinePassed的表格时，间歇性出现Unknown function: 'DeadlinePassed'错误，错误返回在col["effectiveValue"]["errorValue"]中，相同表格和数据下时好时坏。

相关Python代码：

from google.oauth2.service_account import (
    Credentials as ServiceAccountCredentials,
)
from googleapiclient import discovery
import json

# 假设SCOPES、jsonText、spreadsheet_id、ranges、col_headers已定义
credentials = json.loads(jsonText)
creds = ServiceAccountCredentials.from_service_account_info(
            info=credentials,
            scopes=SCOPES,
        )
gService = discovery.build("sheets", "v4", credentials=creds)
request = gService.spreadsheets().get(
                spreadsheetId=spreadsheet_id,
                ranges=ranges,
                includeGridData=True,
            )
response = request.execute()
sheet_data = response["sheets"][0]["data"][0]["rowData"]
num_cols = 10
for row_idx in range(4, len(sheet_data)):
    row = sheet_data[row_idx]
    for col, colHeader in zip(row["values"], col_headers):
         try:
             gValue = col["formattedValue"]
         except KeyError:
             # 处理错误情况
             error = col.get("effectiveValue", {}).get("errorValue", {})
             print(f"Error in cell: {error.get('message')}")
         ...

自定义函数DeadlinePassed代码：

/**
 * @param {Date} inputDate Date to check
 * @return Date or ''
 * @customfunction
 */
function DeadlinePassed(inputDate) {
  // Get the current date and subtract one day
  var yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  
  // Check if the input date is valid
  if (!inputDate || 
       (Object.prototype.toString.call(inputDate) !== "[object Date]" && 
        Object.prototype.toString.call(inputDate) !== "[object Number]")) {
    return '';
  }

  // If the input date is yesterday or after, return it. Otherwise return ''.
  if (inputDate >= yesterday) {
    return inputDate;
  } else {
    return '';
  }
}

可能原因 #

1. 自定义脚本加载延迟：Google Sheets的绑定脚本在服务端有加载缓存机制，当API请求触发时，脚本可能尚未完成初始化，导致函数无法被识别。
2. API请求时序问题：短时间内多次请求可能触发服务端的资源限制，导致脚本执行被延迟或跳过。
3. 权限或缓存异常：服务账号的权限缓存可能出现临时异常，导致无法访问绑定的自定义函数。

解决方案 #

方案1：用内置函数替代自定义函数（最稳定） #

自定义函数的逻辑完全可以用Google Sheets内置函数实现，彻底避免脚本依赖问题。将=DeadlinePassed(DATEVALUE("2024-03-23"))替换为：

=IF(DATEVALUE("2024-03-23")>=TODAY()-1, DATEVALUE("2024-03-23"), "")

这个公式完全复刻了原自定义函数的逻辑，无需依赖Apps Script，API访问时不会出现函数识别问题。

方案2：添加API请求重试机制 #

针对间歇性错误，在代码中捕获特定错误并进行重试：

import time
from googleapiclient.errors import HttpError

def get_sheet_data(gService, spreadsheet_id, ranges, max_retries=3, retry_delay=2):
    for attempt in range(max_retries):
        try:
            request = gService.spreadsheets().get(
                spreadsheetId=spreadsheet_id,
                ranges=ranges,
                includeGridData=True,
            )
            response = request.execute()
            # 检查是否存在函数未识别错误
            has_error = False
            sheet_data = response["sheets"][0]["data"][0]["rowData"]
            for row_idx in range(4, len(sheet_data)):
                row = sheet_data[row_idx]
                for col in row["values"]:
                    error = col.get("effectiveValue", {}).get("errorValue", {})
                    if error.get("type") == "NAME" and "Unknown function: 'DeadlinePassed'" in error.get("message", ""):
                        has_error = True
                        break
                if has_error:
                    break
            if not has_error:
                return sheet_data
        except HttpError as e:
            # 处理HTTP错误，如429限流
            if e.resp.status in [429, 500, 503]:
                time.sleep(retry_delay * (attempt + 1))
                continue
            raise
        # 等待后重试
        time.sleep(retry_delay * (attempt + 1))
    raise Exception(f"Failed to get valid sheet data after {max_retries} retries")

# 使用示例
sheet_data = get_sheet_data(gService, spreadsheet_id, ranges)

方案3：提前触发单元格计算 #

在调用spreadsheets().get之前，发送一个空的batchUpdate请求触发表格重新计算，确保自定义函数先执行完毕：

def trigger_recalculation(gService, spreadsheet_id, sheet_id):
    request = gService.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheet_id,
        body={
            "requests": [
                {
                    "updateCells": {
                        "range": {
                            "sheetId": sheet_id
                        },
                        "fields": "userEnteredValue"
                    }
                }
            ]
        }
    )
    request.execute()

# 先获取sheetId，再触发计算
pre_response = gService.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
sheet_id = pre_response["sheets"][0]["properties"]["sheetId"]
trigger_recalculation(gService, spreadsheet_id, sheet_id)
time.sleep(1)  # 给计算留一点时间

# 再获取数据
response = gService.spreadsheets().get(
    spreadsheetId=spreadsheet_id,
    ranges=ranges,
    includeGridData=True,
).execute()
sheet_data = response["sheets"][0]["data"][0]["rowData"]

方案4：检查权限与脚本状态 #

• 确认服务账号邮箱已被添加到表格的共享列表中，权限至少设置为编辑（仅查看权限可能无法触发自定义函数执行）。
• 打开表格的Apps Script编辑器，确认脚本没有被禁用，且最近一次保存后没有报错。

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