如何用Apps Script优化Google Sheets的importXML调用规避加载错误

步骤1：清理原有公式 #

先清空B列所有的importXML公式，后续将通过脚本直接写入结果值，避免公式重复触发请求。

步骤2：添加Apps Script代码 #

打开Google Sheets，点击「扩展程序」→「Apps Script」，替换默认代码为以下内容：

function fetchSteamPlayerNames() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const urlRange = sheet.getRange("H:H"); // H列存储拼接好的API URL
  const urls = urlRange.getValues().filter(row => row[0] !== ""); // 过滤空行
  const batchSize = 25; // 每批次处理行数
  const delayMs = 60000; // 批次间隔时间（毫秒，这里设为1分钟，可根据Steam API限制调整）
  const xmlPath = "//steamID"; // 替换为Steam API返回XML中玩家名称的实际XPath，需根据API返回调整

  for (let i = 0; i < urls.length; i += batchSize) {
    const batchUrls = urls.slice(i, i + batchSize);
    const startRow = i + 2; // 假设第1行是表头，数据从第2行开始，若不是则修改为对应行号

    // 处理当前批次
    batchUrls.forEach((urlRow, index) => {
      try {
        const response = UrlFetchApp.fetch(urlRow[0]);
        const xml = XmlService.parse(response.getContentText());
        const playerName = xml.getRootElement().getChildren(xmlPath)[0].getText();
        // 将结果写入B列对应行
        sheet.getRange(startRow + index, 2).setValue(playerName);
      } catch (e) {
        // 捕获错误，写入提示信息
        sheet.getRange(startRow + index, 2).setValue(`获取失败: ${e.message}`);
      }
    });

    // 最后一批处理完不需要延迟
    if (i + batchSize < urls.length) {
      Utilities.sleep(delayMs);
    }
  }
}

// 设置每日触发（可手动添加，或运行此函数创建触发）
function createDailyTriggers() {
  // 删除已有同名触发器
  const existingTriggers = ScriptApp.getProjectTriggers().filter(trigger => trigger.getHandlerFunction() === "fetchSteamPlayerNames");
  existingTriggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));

  // 创建每日3次的触发（可调整时间）
  ScriptApp.newTrigger("fetchSteamPlayerNames")
    .timeBased()
    .atHour(8)
    .everyDays(1)
    .create();

  ScriptApp.newTrigger("fetchSteamPlayerNames")
    .timeBased()
    .atHour(14)
    .everyDays(1)
    .create();

  ScriptApp.newTrigger("fetchSteamPlayerNames")
    .timeBased()
    .atHour(20)
    .everyDays(1)
    .create();
}

关键配置说明 #

• xmlPath：必须替换为Steam Web API返回XML中玩家名称对应的XPath。比如如果API返回的XML结构是<player><steamID>玩家名称</steamID></player>，则路径应为player/steamID，请根据实际返回内容调整。
• batchSize：当前设为25，可根据Steam API的调用频率限制微调（比如Steam API通常限制每分钟最多20-30次请求）。
• delayMs：当前设为1分钟（60000毫秒），确保批次间的请求不会触发频率限制。
• startRow：如果你的数据不是从第2行开始（比如第1行是表头），请修改为数据起始行号。

使用方法 #

1. 保存脚本，先手动运行fetchSteamPlayerNames测试是否能正常获取数据，检查B列结果是否正确。
2. 若测试正常，运行createDailyTriggers函数，创建每日3次的自动触发（时间分别为8点、14点、20点，可根据需求修改）。
3. 后续脚本会自动分批处理所有H列的URL，避免请求过多报错。

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