需求：实现每日触发IMPORTXML公式的Google Sheets脚本

问题背景 #

大量行使用IMPORTXML实时拉取数据会导致表格卡顿、加载超时，改用定时脚本批量更新数据可彻底解决这个问题，同时支持手动触发更新。

完整脚本代码 #

打开Google Sheets的扩展 > Apps 脚本，替换默认代码为以下内容：

function updateTwitchViewerStats() {
  // 获取当前表格实例
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  // 定位到"Database"工作表
  const dbSheet = spreadsheet.getSheetByName('Database');
  if (!dbSheet) {
    throw new Error("找不到名为'Database'的工作表");
  }

  // 获取A列的频道数据（跳过第1行表头）
  const lastRow = dbSheet.getLastRow();
  const channelData = dbSheet.getRange('A2:A' + lastRow).getValues().flat();
  // 过滤空行，避免无效请求
  const validChannels = channelData.filter(channel => channel.trim() !== '');

  // 遍历每个频道拉取数据
  validChannels.forEach((channel, index) => {
    try {
      // 构造Sullygnome的查询URL（和你原IMPORTXML公式的URL逻辑一致）
      const targetUrl = `https://sullygnome.com/channel/${encodeURIComponent(channel)}/30`;
      // 发起网络请求获取页面内容
      const response = UrlFetchApp.fetch(targetUrl, { muteHttpExceptions: true });
      const pageContent = response.getContentText();

      // 解析页面XML（替换成你原IMPORTXML公式使用的XPath）
      // 示例XPath对应原公式：=IMPORTXML(..., "//div[@class='statPanelValue'][1]")
      const xmlDoc = XmlService.parse(pageContent);
      const xpathQuery = "//div[@class='statPanelValue'][1]";
      const resultNodes = XmlService.getNamespaceManager().getXPathResult(
        xmlDoc.getRootElement(),
        xpathQuery,
        XmlService.XPathResultType.ORDERED_NODE_SNAPSHOT_TYPE
      );

      // 提取数据或标记无结果
      const viewerCount = resultNodes.getLength() > 0 
        ? resultNodes.snapshotItem(0).getText().trim()
        : "无数据";

      // 将结果写入E列对应行（第2行开始，对应A列的频道位置）
      dbSheet.getRange(2 + index, 5).setValue(viewerCount);
    } catch (err) {
      // 出错时记录错误信息
      dbSheet.getRange(2 + index, 5).setValue(`错误: ${err.message}`);
    }
    // 避免请求频率过高被网站限制，每处理一个频道暂停1秒
    SpreadsheetApp.flush();
    Utilities.sleep(1000);
  });
}

操作步骤 #

1. 配置脚本 #

• 打开你的Google Sheets文档，点击顶部菜单栏扩展 > Apps 脚本
• 删除默认的myFunction代码，粘贴上面的脚本
• 如果你原IMPORTXML使用的XPath和示例不同，修改脚本中的xpathQuery变量为你公式里的第二个参数

2. 测试脚本 #

• 点击脚本编辑器顶部的运行按钮（▶️）
• 第一次运行会触发授权流程，按提示完成授权（需允许脚本访问表格和网络）
• 返回Database工作表，检查E列是否已填充数据

3. 设置定时自动更新 #

• 在脚本编辑器左侧点击触发器（⏰），然后点击右下角添加触发器
• 配置参数：
  • 选择函数：updateTwitchViewerStats
  • 事件源：时间驱动
  • 触发器类型：选日计时器或周计时器，设置合适的运行时间（推荐凌晨时段）
• 点击保存完成定时配置

4. 添加手动触发按钮 #

• 切换到Database Stats工作表
• 点击菜单栏插入 > 绘图，绘制一个带文字的按钮（比如“更新数据”），完成后点击保存并关闭
• 右键点击插入的绘图，选择分配脚本，输入updateTwitchViewerStats后确定
• 以后点击该按钮即可手动触发数据更新

注意事项 #

• 如果遇到网站访问限制，可将Utilities.sleep(1000)改为2000（暂停2秒）
• 脚本运行时会覆盖E列现有数据，符合“直到下次触发”的需求
• 若频道名变更或网站结构调整，需对应修改脚本中的URL构造或XPath

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