Google Apps Script脚本执行结果不一致的原因排查

在Google表单关联Google表格的项目中，设置onFormSubmit触发器调用库脚本。相同代码、相同表单输入、未改动表格数据及命名范围的情况下，脚本有时执行成功，30秒后再次执行却失败。成功时无控制台日志，失败时日志显示在script.google.com的执行窗口中。

补充说明 #

• 故意将一个命名范围设为#REF，用于测试错误处理机制。
• 相关代码如下：

客户端脚本（Google表格内） #

// ON FORM SUBMIT
function collectFormData(e) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    const rangesMap = Api.loadNamedRanges(ss);
    Api.checkNamedRangesErrors();
}

客户端脚本清单文件 #

{
  "timeZone": "Europe/Warsaw",
  "exceptionLogging": "STACKDRIVER",
  "runtimeVersion": "V8",
  "dependencies": {
    "enabledAdvancedServices": [
      {
        "userSymbol": "Gmail",
        "serviceId": "gmail",
        "version": "v1"
      }
    ],
    "libraries": [
      {
        "userSymbol": "API",
        "version": "0",
        "libraryId": "id of api script here",
        "developmentMode": true
      }
    ]
  }
}

API库项目代码（loadNamedRanges方法） #

let _stackedErrors = [];

/**
 * Loads values for all named ranges in Spreadsheet and stores them in NamedRangeMap.
 * @param  {Spreadsheet}  ss  Active spreadsheet
 * @returns  {Map}  Map loaded with values of all named ranges
 */
function loadNamedRanges(ss) {
    const namedRangesMap = new Map();

    // helper variables start
    const namedRanges = ss.getNamedRanges();
    const namedRangesDict = {};
    // helper variables end

    // load named ranges dict start
    namedRanges.forEach((namedRange) => {
        const name = namedRange.getName();
        namedRangesDict[name] = namedRange.getRange();
    });
    // load named ranges dict end

    // see if map is available in script cache start
    const cacheKey = "ranges-map";
    var cache = CacheService.getScriptCache();
    var cached = cache.get(cacheKey);
    if (!!cached) {
        return JSON.parse(cached);
    }
    // see if map is available in script cache end

    // load all named ranges start
    // for simplicity we have 2 named ranges: EMAIL and NAME
    // EMAIL is "#REF", NAME is valid named range with single cell
    let emailColumn = 0;
    const emailRange = namedRangesDict["EMAIL"];
    if ("EMAIL" in namedRangesDict) {
        emailColumn = emailRange.getColumn();
    } else {
        const error = new Error("Range with name EMAIL does not exist or refers to wrong cell !!!");
        _stackedErrors.push(error);
    }

    const nameRange = namedRangesDict["NAME"];
    let nameColumn = 0;
    if ("NAME" in namedRangesDict) {
        nameColumn = nameRange.getColumn();
    } else {
        const error = new Error("Range with name NAME does not exist or refers to wrong cell !!!");
        _stackedErrors.push(error);
    }

    namedRangesMap["emailCol"] = emailColumn;
    namedRangesMap["nameCol"] = nameColumn;
    // load all named ranges end

    // update cache start
    cache.put(cacheKey, JSON.stringify(rangesMap));
    // update cache end

    return namedRangesMap;
}

API库项目代码（checkNamedRangesErrors方法） #

/**
 * Checks whether any errors happened during loading of named ranges.
 * If there were some, then they were saved in stackedErrors array.
 * Then they are checked here and if there were any, main error is thrown here.
 *
 * @trows  {Error}  Throws error only, when any errors happened during loading named ranges
 */
function checkNamedRangesErrors() {
    if (_stackedErrors.length > 0) {
        throw new Error("Some named ranges are missing or refer to wrong cells !!!");
    }
}

核心原因分析 #

1. 全局变量未重置导致状态污染
   库中的_stackedErrors是全局变量，Google Apps Script的执行环境可能复用脚本实例：

   • 若某次执行向_stackedErrors添加了错误，后续复用同一实例的执行即使命中缓存、未触发新错误，checkNamedRangesErrors仍会读取到之前的错误并抛出。
   • 若使用新的执行实例，_stackedErrors为空，此时若命中缓存就会执行成功，最终导致结果不一致。

2. 缓存逻辑存在变量名错误
   loadNamedRanges末尾的缓存代码使用了未定义的rangesMap变量（实际应为namedRangesMap），导致缓存存入的是undefined：

   • 命中缓存时，JSON.parse(undefined)会抛出解析错误，直接导致脚本失败。
   • 缓存未命中时，会重新执行命名范围加载逻辑，此时的执行结果依赖于当前实例的_stackedErrors状态。

3. Map对象序列化错误
   代码中用new Map()创建namedRangesMap，但通过普通属性赋值（namedRangesMap["emailCol"] = ...）而非Map.set()方法添加数据。JSON序列化Map对象时默认只会保留普通属性，导致缓存的内容为空对象，后续执行返回空对象时，若_stackedErrors为空就会“成功”。

4. #REF命名范围的未捕获异常
   当命名范围为#REF时，调用namedRange.getRange()会抛出异常：

   • 该异常会终止namedRanges.forEach循环，导致namedRangesDict无法包含EMAIL，后续代码会向_stackedErrors添加错误。
   • 若Google表格服务对无效命名范围的处理存在延迟/不一致（如某次执行未返回该无效范围），namedRangesDict会包含EMAIL，此时调用emailRange.getColumn()会抛出未捕获异常，直接终止脚本，不会触发checkNamedRangesErrors，导致日志显示的错误类型不同，进一步加剧结果的不一致性。

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