优化Google AppScript URL请求循环，解决18K行数据脚本超时问题

我的Google Sheet已经演变为拥有18000行数据的数据库，但现有循环脚本处理约500行后就会超时。涉及两个表格：一个存储约40个API密钥，另一个用于写入获取到的数据。目标网站API有严格限制：每个密钥每分钟最多100次请求，因此我设置每个密钥仅调用50次。当前脚本在5分钟执行时限内仅能处理约500行，原脚本如下：

function GetLastAction1() {
      
  //Get API from spreadsheet
  var spreadsheet = SpreadsheetApp.getActive();
  //spreadsheet.setActiveSheet(spreadsheet.getSheetByName('Target List - Stats'), true);
  var Targetsheet = SpreadsheetApp.openById('targetsheet');
  var ApiSheet = SpreadsheetApp.openById('apisheet');
  var ApiTab = ApiSheet.getSheetByName('API-Stats');
  var TargetTab = Targetsheet.getSheetByName('Target List - Stats');
  var Apilength = ApiTab.getLastRow();
  var ApiCounter =2;
  var StartRow =2;
   
  for (ApiCounter; ApiCounter<Apilength;ApiCounter++){
    var api = ApiTab.getRange(ApiCounter,3).getValue();
    for (var x=0;x<=50;x++){
      var id = TargetTab.getRange(StartRow,15).getValue();
  

        // Call the website API
        var response = UrlFetchApp.fetch("https://api.website.com/user/"+id+"?selections=profile&key="+api );

        // Parse the JSON reply
        var json = response.getContentText();
        var data = JSON.parse(json);
        
        //Get Last Action and Write Data
        console.log(data.last_action)
        var ActionTime = data.last_action.timestamp;
        lastAction = new Date(ActionTime*1000);
        lastAction = Utilities.formatDate(lastAction, "GMT", "MM-dd-yyyy HH:mm:ss");
        TargetTab.getRange(StartRow,8).setValue(lastAction); 
        StartRow++   
    }
    }
};

核心优化方向 #

• 批量读写数据：原脚本每次循环单独读写单元格，这是拖慢速度的核心原因。改为一次性读取所有待处理ID，处理完成后批量写入结果，大幅减少与Google Sheets的交互次数。
• 缓存API密钥：一次性读取所有API密钥到数组，避免循环中重复读取单元格。
• 严格控制请求频率：每个密钥调用50次后，等待1分钟再切换下一个密钥（留冗余空间，避免触发API限制）。
• 断点续跑：记录每次处理到的行号，下次运行直接从断点开始，无需从头执行。
• 错误容错：单个ID处理出错时仅记录日志，不中断整个脚本流程。

优化后的脚本 #

function GetLastActionOptimized() {
  // 配置参数
  const TARGET_SHEET_ID = 'targetsheet'; // 替换为你的目标表格ID
  const API_SHEET_ID = 'apisheet'; // 替换为你的API表格ID
  const MAX_REQUESTS_PER_KEY = 50; // 每个密钥最多调用次数
  const API_RATE_LIMIT_WAIT = 60000; // 切换密钥前等待1分钟（毫秒）
  const TIME_ZONE = "GMT";
  const DATE_FORMAT = "MM-dd-yyyy HH:mm:ss";

  // 获取表格和工作表对象
  const targetSheet = SpreadsheetApp.openById(TARGET_SHEET_ID).getSheetByName('Target List - Stats');
  const apiSheet = SpreadsheetApp.openById(API_SHEET_ID).getSheetByName('API-Stats');

  // 一次性读取所有API密钥（第3列，从第2行开始）
  const apiKeys = apiSheet.getRange(2, 3, apiSheet.getLastRow() - 1, 1).getValues().flat();
  
  // 获取需要处理的ID范围：从上次断点开始，到最后一行
  const properties = PropertiesService.getScriptProperties();
  let startRow = parseInt(properties.getProperty('LAST_PROCESSED_ROW')) || 2;
  const lastRow = targetSheet.getLastRow();
  
  // 一次性读取所有需要处理的ID（第15列）
  const ids = targetSheet.getRange(startRow, 15, lastRow - startRow + 1, 1).getValues().flat();
  
  // 准备结果数组，用于批量写入
  const results = [];
  let currentKeyIndex = 0;
  let requestsWithCurrentKey = 0;

  for (let i = 0; i < ids.length; i++) {
    const id = ids[i];
    if (!id) { // 跳过空ID
      results.push(['']);
      continue;
    }

    try {
      // 调用API
      const apiKey = apiKeys[currentKeyIndex];
      const url = `https://api.website.com/user/${id}?selections=profile&key=${apiKey}`;
      const response = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      
      // 解析响应
      const data = JSON.parse(response.getContentText());
      const actionTime = data.last_action?.timestamp;
      
      // 处理时间格式
      let formattedTime = '';
      if (actionTime) {
        const lastActionDate = new Date(actionTime * 1000);
        formattedTime = Utilities.formatDate(lastActionDate, TIME_ZONE, DATE_FORMAT);
      }
      results.push([formattedTime]);

      // 更新请求计数，达到上限则切换密钥并等待
      requestsWithCurrentKey++;
      if (requestsWithCurrentKey >= MAX_REQUESTS_PER_KEY) {
        currentKeyIndex = (currentKeyIndex + 1) % apiKeys.length;
        requestsWithCurrentKey = 0;
        if (currentKeyIndex !== 0) { // 不是第一个密钥时才等待
          SpreadsheetApp.flush();
          Utilities.sleep(API_RATE_LIMIT_WAIT);
        }
      }

      // 每处理100行保存一次断点，避免意外中断丢失进度
      if ((i + 1) % 100 === 0) {
        const currentProcessedRow = startRow + i;
        properties.setProperty('LAST_PROCESSED_ROW', currentProcessedRow);
        // 批量写入当前结果
        targetSheet.getRange(startRow, 8, results.length, 1).setValues(results);
        results.length = 0; // 清空结果数组
        SpreadsheetApp.flush();
      }

    } catch (e) {
      console.error(`处理ID ${id}时出错: ${e.message}`);
      results.push(['']); // 出错时写入空值
    }
  }

  // 处理剩余的结果
  if (results.length > 0) {
    targetSheet.getRange(startRow, 8, results.length, 1).setValues(results);
  }

  // 更新最终断点
  properties.setProperty('LAST_PROCESSED_ROW', lastRow + 1);
  SpreadsheetApp.getUi().alert('数据处理完成！');
}

// 重置断点（需要重新处理时运行）
function resetProcessedRow() {
  const properties = PropertiesService.getScriptProperties();
  properties.setProperty('LAST_PROCESSED_ROW', 2);
  SpreadsheetApp.getUi().alert('断点已重置，将从第2行开始处理');
}

使用说明 #

• 断点续跑：脚本会自动记录上次处理到的行号，下次运行直接从断点继续。如果需要重新处理所有数据，运行resetProcessedRow函数重置断点。
• 批量写入：每处理100行就批量写入一次，既减少表格交互次数，又避免内存占用过高。
• 频率控制：严格遵守API限制，每个密钥调用50次后自动切换并等待1分钟，不会触发限流。

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