如何通过脚本分批调用googlefinance()规避查询限制并每日更新？

问题背景 #

我在Google Sheet中使用GOOGLEFINANCE()函数收集股票数据，示例公式如下：

• 当前价格：=if($D$1=true,googlefinance(_ticker(A3),"price"),D3)
• 5年最低价：=if($D$1=true, min(index(googlefinance(_ticker(A3), "price", date(year(today()) - 5, month(today()), day(today())), today()), 0, 2)),E3)
• 5年趋势图：=if($D$1=true, sparkline(googlefinance(_ticker(A3), "price", today()-1825, today(), "weekly"), {"charttype","line";"linewidth",1;"color","#5f88cc"}), J3)

由于股票代码超过1000条，即使通过D1单元格的复选框控制公式启用，单次触发仍会产生约5000次查询，频繁触发速率限制或出现「Internal Error: xx returned no result」错误。

需求 #

• 每次仅调用GOOGLEFINANCE()查询10个股票代码
• 每5分钟执行一次查询，按顺序分批处理
• 在K列记录对应股票代码的数据查询日期
• 仅每日更新一次数据，不关注日内变化
• 遍历完成后从头开始，若当前日期与K列最后查询日期一致则停止操作

当前困境 #

尝试通过Google Apps脚本直接调用GOOGLEFINANCE()失败，发现该函数仅能在单元格内直接调用，寻求可行解决方案。

解决方案 #

因为脚本无法直接调用GOOGLEFINANCE()，可采用「临时写入公式→取值固定→清除公式」的思路，结合时间触发器分批处理：

1. 编写分批处理脚本 #

function batchUpdateStockData() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const lastRow = sheet.getLastRow();
  const startRow = 3; // 假设股票代码从第3行开始
  const batchSize = 10; // 每次处理10条
  
  // 获取当前日期（仅保留日期部分）
  const currentDate = new Date();
  currentDate.setHours(0, 0, 0, 0);
  
  // 检查今日是否已完成全量更新
  const lastQueryDate = sheet.getRange(`K${lastRow}`).getValue();
  if (lastQueryDate instanceof Date) {
    const normalizedLastDate = new Date(lastQueryDate);
    normalizedLastDate.setHours(0, 0, 0, 0);
    if (normalizedLastDate.getTime() === currentDate.getTime()) {
      return; // 今日已更新，直接退出
    }
  }
  
  // 定位下一批未更新的起始行
  let nextStart = startRow;
  for (let i = startRow; i <= lastRow; i++) {
    const cellDate = sheet.getRange(`K${i}`).getValue();
    if (!(cellDate instanceof Date) || new Date(cellDate).setHours(0,0,0,0) < currentDate.getTime()) {
      nextStart = i;
      break;
    }
  }
  
  // 计算当前批次的结束行
  const endRow = Math.min(nextStart + batchSize - 1, lastRow);
  
  // 处理当前价格列（D列）
  const priceRange = sheet.getRange(`D${nextStart}:D${endRow}`);
  priceRange.setFormulaR1C1(`=GOOGLEFINANCE(_ticker(R[0]C[-3]),"price")`);
  SpreadsheetApp.flush(); // 强制刷新计算
  const priceValues = priceRange.getValues();
  priceRange.setValues(priceValues); // 固定数值
  
  // 处理5年最低价列（E列）
  const lowRange = sheet.getRange(`E${nextStart}:E${endRow}`);
  lowRange.setFormulaR1C1(`=MIN(INDEX(GOOGLEFINANCE(_ticker(R[0]C[-4]),"price",DATE(YEAR(TODAY())-5,MONTH(TODAY()),DAY(TODAY())),TODAY()),0,2))`);
  SpreadsheetApp.flush();
  const lowValues = lowRange.getValues();
  lowRange.setValues(lowValues);
  
  // 处理趋势图列（J列）
  const sparklineRange = sheet.getRange(`J${nextStart}:J${endRow}`);
  sparklineRange.setFormulaR1C1(`=SPARKLINE(GOOGLEFINANCE(_ticker(R[0]C[-9]),"price",TODAY()-1825,TODAY(),"weekly"),{"charttype","line";"linewidth",1;"color","#5f88cc"})`);
  SpreadsheetApp.flush();
  const sparklineValues = sparklineRange.getValues();
  sparklineRange.setValues(sparklineValues);
  
  // 更新K列查询日期
  const dateRange = sheet.getRange(`K${nextStart}:K${endRow}`);
  dateRange.setValue(currentDate);
}

2. 设置时间触发器 #

• 打开Google Sheets的「扩展程序」→「Apps脚本」
• 粘贴上述脚本后保存项目
• 点击左侧「触发器」图标，选择「添加触发器」
• 配置参数：
  • 选择函数：batchUpdateStockData
  • 事件源：「时间驱动」
  • 类型：「分钟计时器」
  • 间隔：「每5分钟」
• 保存并完成授权流程

关键说明 #

• 脚本会自动跳过今日已更新的行，避免重复查询
• 通过临时写入公式并立即固定数值，既利用GOOGLEFINANCE()的功能，又避免公式长期存在引发的重复查询
• 每次仅处理10条数据，大幅降低单次查询量，规避速率限制

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