可安装触发器触发时Sheets.Spreadsheets.Values.update偶发无法写入数据

问题场景 #

我编写了SetGetData函数，通过Sheets API实现将子表格数据同步到Master Sheet处理后回传的逻辑：

• 手动运行或按钮触发时完全正常
• 20个子表格通过10分钟一次的时间驱动可安装触发器运行时，偶发Sheets.Spreadsheets.Values.update执行后无数据写入，但日志无报错
• 此前用getValues/setValues实现时因数据量过大频繁超时，故改用Sheets API

核心原因分析 #

1. 并发请求冲突/限流：20个表格同时触发，短时间内向Master Sheet发起大量API请求，触发Google Sheets API的并发限制或配额阈值，导致部分请求被静默拒绝（无报错日志）
2. 数据范围与实际数据不匹配：Sheets.Spreadsheets.Values.get返回的values数组会忽略全空行，导致计算的目标范围（如A1:EE+lastrow）行数大于实际数据行数，写入时出现静默失败
3. 无错误重试机制：偶发的网络波动或临时限流未被捕获，直接导致写入失败且无记录
4. 脚本属性调用笔误：代码中scriptproperties应为PropertiesService.getScriptProperties()，手动运行时可能因上下文变量临时存在未暴露问题，但触发器运行时可能引发隐藏异常

修复方案 #

1. 添加并发控制与指数退避重试

分散请求压力并处理临时限流：

• 给每个子表格设置偏移触发时间（如第1个表格0分触发，第2个1分触发，以此类推），避免20个请求同时冲击Master Sheet
• 封装带重试的API写入函数：

function updateWithRetry(spreadsheetId, range, values, options) {
  const maxRetries = 3;
  let retries = 0;
  while (retries < maxRetries) {
    try {
      return Sheets.Spreadsheets.Values.update({values: values}, spreadsheetId, range, options);
    } catch (e) {
      retries++;
      if (retries >= maxRetries) throw e;
      // 指数退避等待，避免频繁重试加剧限流
      Utilities.sleep(Math.pow(2, retries) * 1000);
    }
  }
}

将原代码中所有Sheets.Spreadsheets.Values.update调用替换为updateWithRetry。

2. 修正数据范围计算逻辑

确保目标范围与实际数据行数匹配：

// 获取子表格数据后，修正范围计算
var mydata = Sheets.Spreadsheets.Values.get(myssID, sheetname+"!A1:EE").values;
const actualRows = mydata ? mydata.length : 0;
const mydatarangeNotation = sheetname+"!A1:EE"+actualRows;

// 回写数据时同样处理
var masterdata = Sheets.Spreadsheets.Values.get(masterID, "Master!A1:EE").values;
const actualMasterRows = masterdata ? masterdata.length : 0;
const masterrangeNotation = "Master!A1:EE"+actualMasterRows;

3. 完善错误捕获与日志记录

添加异常捕获，记录详细错误信息便于排查：

// 替换写入Master的代码块
try {
  updateWithRetry(masterID, mydatarangeNotation, mydata, {valueInputOption: "USER_ENTERED"});
  Logger.log("Values on Master Spreadsheet updated successfully.");
} catch (e) {
  Logger.log("ERROR updating Master: " + e.message + "\nStack: " + e.stack);
  // 可选：添加邮件通知，及时知晓失败情况
  // MailApp.sendEmail("your-email@example.com", "Sheet Sync Failed", "Error details: " + e.message);
}

// 回写子表格的代码块同样添加捕获逻辑
try {
  updateWithRetry(myssID, masterrangeNotation, masterdata, {valueInputOption: "USER_ENTERED"});
  Logger.log("Local Master Sheet updated successfully.");
} catch (e) {
  Logger.log("ERROR updating local Master: " + e.message + "\nStack: " + e.stack);
}

4. 修正脚本属性调用笔误

将代码中所有scriptproperties替换为正确的API调用：

const scriptProps = PropertiesService.getScriptProperties();
var myssID = scriptProps.getProperty("ssID");
var sheetno = JSON.parse(scriptProps.getProperty('TeamSheetNo'));

总结 #

以上调整可解决并发冲突、数据结构不匹配、临时网络问题等导致的偶发写入失败，同时完善日志体系便于后续问题排查。

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