Apps Script表单提交触发器延迟高且偶发失败求助

问题背景 #

有一个内嵌Apps Script的谷歌表格，关联2个表单，通过可安装表单提交触发器判断提交来源并执行对应操作。目前触发器约20%概率执行失败，成功运行也需耗时约5分钟，代码仅涉及小范围数据读取、表单附件收集和邮件发送。

错误现象 #

两种失败场景：

1. 5-6分钟后抛出：Exception: Service Spreadsheets timed out while accessing document with id xxxxxxxx.（对应代码第58行）
2. 约90秒后抛出：Exception: Document xxxxxxx is missing (perhaps it was deleted, or you don't have read access?)（对应代码第50行）
   注：两种错误涉及的表格ID均为脚本内嵌表格，权限和文件存在性无问题，80%情况可正常运行。

关键代码及上下文 #

第50行代码（访问表格标签页） #

let sht = sSht.getSheetByName("Key Status Form");

上下文：

let sSht;
...
sSht = SpreadsheetApp.getActiveSpreadsheet();

第58行代码（读取数据区域） #

keyCompletionEmails = constantsSheet.getRange(keyCompletionEmailsStart).getDataRegion().getValues().flat();

上下文：

let constantsSheetNm = "User Inputs";
let constantsSheet = sSht.getSheetByName(constantsSheetNm);

let keyCompletionEmailsStart = "J1";

完整触发函数片段 #

function formSubmission(e) {
  let vals = e.namedValues;
  sSht = SpreadsheetApp.getActiveSpreadsheet();

  // 过滤非目标表单提交
  if (vals["QUESTION NAME"] == undefined) {
    console.log("Wrong form");
    // 格式化单元格为文本格式
    let sht = sSht.getSheetByName("SHEET NAME");
    let lastRow = sSht.getLastRow();
    sht.getRange(lastRow - 2, 2, 3).setNumberFormat("@STRING@");
    return;
  }

  let var1 = vals["qu 1"][0];
  let var2 = vals["qu 2"][0];
  let var3 = vals["qu 3"][0];
  let var4 = vals["qu 4"][0];
  let imageUrl = vals["Please consider uploading a photo"][0];
  console.log(imageUrl);

  // 再次格式化单元格为文本格式
  let sht = sSht.getSheetByName("SHEET NAME");
  let lastRow = sSht.getLastRow();
  sht.getRange(lastRow - 2, 2, 3).setNumberFormat("@STRING@");


  constantsSheet = sSht.getSheetByName(constantsSheetNm);
  
  keyCompletionEmails = constantsSheet.getRange(keyCompletionEmailsStart).getDataRegion().getValues().flat();
}

表格补充信息 #

• 每个表单已有超过4000条响应
• 表格包含6个标签页，存在颜色设置、条件格式、复杂VLOOKUP公式，以及约500行30列的数据
• 成功运行时，第50行耗时约115秒，第58行耗时约125秒（通过console.time()计时）

已尝试的修复动作 #

删除并重新安装所有触发器，问题未解决；此前处理过更高响应量的表单无此问题。

排查思路与解决步骤 #

1. 优化表格性能，降低脚本访问耗时 #

表格本身的性能问题是核心诱因，脚本访问慢才会触发超时：

• 清理冗余格式与公式：删除未使用的条件格式、颜色设置；将复杂VLOOKUP替换为更高效的XLOOKUP，或改用数组公式批量计算；移除空白行/列，缩小数据范围。
• 拆分表单响应数据：将两个表单的响应数据移到独立谷歌表格，原表格仅保留脚本所需的配置数据（如User Inputs标签页），减少主表格负载。
• 重置表格环境：复制表格为新文件，清除原表格的历史格式残留，避免隐性性能损耗。

2. 优化脚本执行逻辑，减少Spreadsheet API调用 #

目前脚本存在重复调用和低效操作：

• 合并重复操作：原脚本中两次执行getSheetByName("SHEET NAME")和setNumberFormat，合并为一次操作，减少API调用次数。
• 明确数据范围：将getDataRegion()改为预先指定的固定范围（如J1:J100），避免自动检测数据区域带来的耗时。
• 缓存配置数据：如果User Inputs中的邮件列表不常变动，用PropertiesService缓存数据，无需每次触发都读取表格：

  function getKeyCompletionEmails() {
    let cache = CacheService.getScriptCache();
    let cachedEmails = cache.get("keyCompletionEmails");
    if (cachedEmails) return JSON.parse(cachedEmails);
    
    let constantsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("User Inputs");
    let emails = constantsSheet.getRange("J1:J100").getValues().flat().filter(Boolean);
    cache.put("keyCompletionEmails", JSON.stringify(emails), 3600); // 缓存1小时
    return emails;
  }
  

• 改用局部变量：将全局变量sSht、constantsSheet改为函数内局部变量，确保每次触发都获取有效实例，避免上下文异常。

3. 增加容错与兜底机制 #

• 添加错误重试逻辑：在关键代码块（获取表格、读取数据）添加重试逻辑，应对临时访问异常：

  let sht;
  let retryCount = 3;
  while (retryCount > 0) {
    try {
      sht = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Key Status Form");
      break;
    } catch (e) {
      retryCount--;
      Utilities.sleep(1000);
      if (retryCount === 0) throw e;
    }
  }
  

• 设置兜底触发器：添加每15分钟运行一次的时间驱动触发器，检查未处理的表单响应并补执行脚本，减少漏处理概率。

4. 排查服务环境异常 #

• 查看详细执行日志：检查Apps Script执行日志的完整报错信息，确认是否存在谷歌服务临时限流或区域故障。
• 切换脚本运行环境：将内嵌脚本转为独立脚本项目，绑定表格后重新设置触发器，避免内嵌环境的限制。

内容的提问来源于stack exchange，提问作者Lle.4