如何让定时触发的Google Apps Script等待表格公式计算完成再取数？

问题原因 #

SpreadsheetApp.flush()仅能同步脚本对表格的修改到服务器，无法等待依赖外部API（如股票价格）的单元格完成异步计算；Utilities.sleep()的固定等待时长无法适配外部请求的不稳定延迟，因此这两种方法都无效。

可行解决方案 #

方案1：循环检查目标单元格，直到计算完成 #

编写一个等待函数，循环检查目标单元格的显示值，直到其不再是"Loading..."（或其他加载状态文本），同时设置超时避免无限等待。

示例代码：

// 等待指定名称的单元格完成计算，返回最终值
function waitForCalculation(rangeName, timeoutSeconds = 30) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const range = ss.getRangeByName(rangeName);
  const startTime = Date.now();
  
  while (true) {
    const currentValue = range.getDisplayValue();
    if (currentValue !== "Loading...") {
      return currentValue;
    }
    // 超时判断，防止无限等待
    if (Date.now() - startTime > timeoutSeconds * 1000) {
      throw new Error(`等待单元格 ${rangeName} 计算超时`);
    }
    // 每次间隔1秒再检查
    Utilities.sleep(1000);
  }
}

// 修改原数据抓取逻辑
function yourOriginalFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  const new_row_data = [ [ 
    ss.getRangeByName('Foo').getDisplayValue(),
    ss.getRangeByName('barTotal').getDisplayValue(),
    waitForCalculation('CALC1'),
    ss.getRangeByName('bzzTotal').getDisplayValue(),
    waitForCalculation('CALC2'),
  ] ];
  
  // 后续写入history区域的逻辑...
}

方案2：脚本直接调用外部API替代单元格公式 #

如果单元格使用IMPORTDATA、GOOGLEFINANCE等函数获取数据，可直接在脚本中用UrlFetchApp调用对应API，完全掌控数据获取流程，避开单元格异步计算的问题。

示例（以股票价格为例）：

// 直接调用股票API获取价格
function getStockPrice(symbol) {
  // 替换为实际可用的股票API地址
  const apiUrl = `https://api.example.com/stock/price?symbol=${encodeURIComponent(symbol)}`;
  const response = UrlFetchApp.fetch(apiUrl);
  const data = JSON.parse(response.getContentText());
  return data.price.toString(); // 转为字符串匹配原getDisplayValue()的返回格式
}

// 修改原数据抓取逻辑
function yourOriginalFunction() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  
  const new_row_data = [ [ 
    ss.getRangeByName('Foo').getDisplayValue(),
    ss.getRangeByName('barTotal').getDisplayValue(),
    getStockPrice('YOUR_STOCK_SYMBOL'), // 替代CALC1的单元格公式
    ss.getRangeByName('bzzTotal').getDisplayValue(),
    getStockPrice('ANOTHER_SYMBOL'), // 替代CALC2的单元格公式
  ] ];
  
  // 后续写入history区域的逻辑...
}

注意：使用UrlFetchApp需确保API可公开访问，同时注意Google Apps Script的请求配额限制。

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