批量导入API数据到Google Sheet触发UrlFetch调用限制求助

问题背景 #

使用Google Apps Script将25000+条API数据导入Google Sheet构建数据库时，测试少量数据（10条）正常，但全量执行触发以下错误：

Exception: Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls.

添加Utilities.sleep(1000)后问题仍存在，还偶发：

Exception: Service Spreadsheets failed while accessing document...

当前代码基于UrlFetchApp.fetchAll实现，已解决属性映射问题，需针对大数据量优化方案。

原代码 #

function dataImport() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Import1");
  var countRow = 1;
  var exportSheet = ss.getSheetByName("Data1");
  var reqs = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().reduce((ar, [url]) => {
    if (url) {
      ar.push({ url, muteHttpExceptions: true });
    }
    return ar;
  }, []);

  var responses = UrlFetchApp.fetchAll(reqs);
  Utilities.sleep(1000)
  var temp = responses.reduce((ar, r) => {
    if (r.getResponseCode() == 200) {
      var { id, firstName, lastName, fullName, displayName, shortName, weight, height, position: { abbreviation }, team: { $ref }, birthPlace: { city }, birthPlace: { state, country }, experience: { years, displayValue }, jersey, active } = JSON.parse(r.getContentText());
      ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, $ref, city, state, country, years, displayValue, jersey, active]);
    }
    return ar;
  }, []);
  var res = [['IDs', 'First Name', 'Last Name', 'Full Name', 'Display Name', 'Short Name', 'Weight', 'Height', 'Position', 'Team URL', 'City', 'State', 'Country', 'Years', 'Class', 'jersey', 'Active'], ...temp];
  exportSheet.getRange(countRow, 1, res.length, res[0].length).setValues(res);
}

解决方案 #

核心问题分析 #

1. UrlFetchApp.fetchAll单次批量请求数量超限：官方虽未明确上限，但25000条远超出合理范围，直接触发频率限制
2. 一次性写入全量数据给Spreadsheet服务造成过大负载，引发访问错误
3. 无断点机制，中途出错需重新执行全量任务

优化后的代码 #

function dataImport() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const importSheet = ss.getSheetByName("Import1");
  const exportSheet = ss.getSheetByName("Data1");
  const batchSize = 100; // 每批次处理100条，可根据实际情况调整
  const startRow = PropertiesService.getScriptProperties().getProperty('lastProcessedRow') 
    ? parseInt(PropertiesService.getScriptProperties().getProperty('lastProcessedRow')) + 1 
    : 2;
  const lastRow = importSheet.getLastRow();
  
  // 首次运行写入表头
  if (startRow === 2) {
    const headers = ['IDs', 'First Name', 'Last Name', 'Full Name', 'Display Name', 'Short Name', 'Weight', 'Height', 'Position', 'Team URL', 'City', 'State', 'Country', 'Years', 'Class', 'jersey', 'Active'];
    exportSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
  }

  for (let i = startRow; i <= lastRow; i += batchSize) {
    const endRow = Math.min(i + batchSize - 1, lastRow);
    const urls = importSheet.getRange(`A${i}:A${endRow}`).getDisplayValues().flat().filter(url => url);
    
    if (urls.length === 0) continue;

    const reqs = urls.map(url => ({ url, muteHttpExceptions: true }));
    
    try {
      const responses = UrlFetchApp.fetchAll(reqs);
      const temp = responses.reduce((ar, r) => {
        if (r.getResponseCode() === 200) {
          const data = JSON.parse(r.getContentText());
          // 处理嵌套属性可能为空的情况，避免解构报错
          const id = data.id || '';
          const firstName = data.firstName || '';
          const lastName = data.lastName || '';
          const fullName = data.fullName || '';
          const displayName = data.displayName || '';
          const shortName = data.shortName || '';
          const weight = data.weight || '';
          const height = data.height || '';
          const abbreviation = data.position?.abbreviation || '';
          const teamRef = data.team?.$ref || '';
          const city = data.birthPlace?.city || '';
          const state = data.birthPlace?.state || '';
          const country = data.birthPlace?.country || '';
          const years = data.experience?.years || '';
          const displayValue = data.experience?.displayValue || '';
          const jersey = data.jersey || '';
          const active = data.active || '';
          
          ar.push([id, firstName, lastName, fullName, displayName, shortName, weight, height, abbreviation, teamRef, city, state, country, years, displayValue, jersey, active]);
        }
        return ar;
      }, []);

      // 写入当前批次数据
      if (temp.length > 0) {
        const nextExportRow = exportSheet.getLastRow() + 1;
        exportSheet.getRange(nextExportRow, 1, temp.length, temp[0].length).setValues(temp);
      }

      // 更新已处理行号，实现断点续传
      PropertiesService.getScriptProperties().setProperty('lastProcessedRow', endRow);
      
      // 批次间添加延迟，降低调用频率
      Utilities.sleep(2000);
    } catch (e) {
      console.error(`处理行 ${i} 至 ${endRow} 时出错: ${e.message}`);
      // 出错后延长延迟，避免连续触发错误
      Utilities.sleep(5000);
      // 如需中断执行可取消注释下面一行
      // throw e;
    }
  }

  // 全部完成后清除断点记录
  PropertiesService.getScriptProperties().deleteProperty('lastProcessedRow');
  SpreadsheetApp.getUi().alert('数据导入完成！');
}

关键优化点 #

• 分批请求：将25000+条请求拆分为每100条一批次，避免单次fetchAll触发频率限制
• 断点续传：通过PropertiesService记录已处理的最后行号，中途中断后可从断点继续，无需重新执行全量
• 分批写入：每批次处理完成后立即写入Sheet，减少单次操作对Spreadsheet服务的负载
• 容错处理：添加try-catch捕获单批次错误，同时对嵌套属性做空值判断，避免解构时报错
• 合理延迟：批次间设置2秒延迟，出错时延长至5秒，降低服务调用频率

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