如何规避Apps Script自定义函数30秒超时，完成多API数据拉取？

核心问题分析 #

自定义函数（单元格直接调用的=ImportJSONAdvancedDrivers()）存在30秒执行时间硬上限，而处理100个驾驶员（每个请求间隔200ms）加上网络请求耗时，总时间必然超过限制。同时API的每秒5次速率限制必须遵守，无法通过取消sleep缩短执行时间，因此必须更换执行逻辑的载体。

最佳解决方案 #

1. 改用手动/时间驱动触发器执行脚本 #

将逻辑从自定义函数改为独立的可执行脚本，普通Apps Script的执行时间上限为6分钟，完全覆盖处理100个请求的需求（100个请求+间隔总耗时约20秒）。

修改后的代码示例

function fetchAndUpdateDriverData() {
  var url = 'https://api.samsara.com/v1/fleet/drivers';
  var fetchOptions = {
       'headers': {
           "Authorization": "XYZ Token",
       },
  };
  var jsondata = UrlFetchApp.fetch(url, fetchOptions);
  var drivers = JSON.parse(jsondata.getContentText())['drivers'];

  // 时间参数固定，可按需调整
  var dateStart = new Date(2023, 0, 1, 2, 0, 0, 0);
  var dateStartMs = dateStart.getTime();
  var dateEnd = new Date(2023, 1, 1, 1, 59, 59, 0);
  var dateEndMs = dateEnd.getTime();

  var results = [];
  for (var i = 0; i < drivers.length; i++){
    try {
      var driver = drivers[i];
      var driverId = driver.id;
      var driverName = driver.name;

      // 第一个请求无需等待，后续间隔200ms遵守速率限制
      if (i > 0) {
        Utilities.sleep(200);
      }

      // 拼接详情请求URL
      var detailUrl = `https://api.samsara.com/v1/fleet/drivers/${driverId}/safety/score?startMs=${dateStartMs}&endMs=${dateEndMs}`;
      var detailResp = UrlFetchApp.fetch(detailUrl, fetchOptions);
      var detailData = JSON.parse(detailResp.getContentText());
      
      // 转换里程单位并保留两位小数
      var miles = Math.round(detailData.totalDistanceDrivenMeters * 0.00062137 * 100) / 100;
      var safetyScore = detailData.safetyScore;

      results.push([driverId, driverName, miles, safetyScore]);
    } catch (error){
        console.error(`处理驾驶员${driverId}失败: ${error.message}`);
        results.push([driverId, driverName, '获取失败', '获取失败']);
    }
  };

  // 将结果写入指定工作表
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('DriverData');
  if (!sheet) {
    sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet('DriverData');
    // 写入表头
    sheet.getRange(1, 1, 1, 4).setValues([['驾驶员ID', '姓名', '里程（英里）', '安全评分']]);
  }
  // 清空旧数据并写入新结果
  sheet.getRange(2, 1, sheet.getLastRow()-1, 4).clearContents();
  if (results.length > 0) {
    sheet.getRange(2, 1, results.length, 4).setValues(results);
  }
}

使用方式

• 手动触发：在Apps Script编辑器中直接运行fetchAndUpdateDriverData函数；
• 自动触发：点击编辑器左侧闹钟图标，创建时间驱动触发器，选择该函数并设置执行频率（如每天一次）。

2. 添加缓存机制减少重复请求 #

如果驾驶员数据不需要实时更新，可将已获取的数据缓存到Script Properties中，避免重复调用API，进一步缩短执行时间。

缓存工具函数

// 从缓存获取驾驶员数据
function getCachedDriver(driverId) {
  var props = PropertiesService.getScriptProperties();
  var cached = props.getProperty(`driver_${driverId}`);
  return cached ? JSON.parse(cached) : null;
}

// 将驾驶员数据存入缓存
function setCachedDriver(driverId, data) {
  var props = PropertiesService.getScriptProperties();
  props.setProperty(`driver_${driverId}`, JSON.stringify(data));
}

在循环中使用缓存

// 替换原循环中的请求逻辑
var cachedData = getCachedDriver(driverId);
if (cachedData) {
  results.push([driverId, driverName, cachedData.miles, cachedData.safetyScore]);
} else {
  // 原请求逻辑...
  // 请求完成后缓存数据
  setCachedDriver(driverId, {miles: miles, safetyScore: safetyScore});
}

3. 分批次处理（应对超大规模数据） #

如果驾驶员数量远超100个，超过6分钟执行上限，可将数据分成多个批次，通过触发器分阶段执行：

• 用Script Properties记录已处理的驾驶员索引；
• 每次处理固定数量的驾驶员（如20个）；
• 未处理完成时，自动创建下一次执行的触发器。

关键注意事项 #

• 自定义函数的30秒限制是Google的硬规则，无法突破，必须改用普通脚本执行；
• 严格遵守API速率限制，避免被临时封禁；
• 缓存数据可添加时间戳标记过期时间，兼顾性能与数据时效性。

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