Apps Script调用getDisplayValues返回#REF!，无法获取Google Finance公式值

我明白你遇到的问题了——在浏览器中单元格能正常显示股票YTD最高价，但用Apps Script的getDisplayValues()获取时却返回#REF!。这确实和公式里的INDEX()以及GoogleFinance的异步特性有关，下面我来详细分析并给出解决方案：

问题原因 #

GoogleFinance是异步加载的特殊函数，它需要从外部获取数据后才能完成计算。当你的脚本调用getDisplayValues()时，可能存在两种情况导致返回#REF!：

• 脚本执行速度快于GoogleFinance的数据加载，此时单元格还没完成计算，返回错误值；
• 在Apps Script的执行上下文里，嵌套的INDEX()无法正确解析GoogleFinance返回的二维数组，导致公式计算失败。

而在浏览器中，Google Sheets会自动等待GoogleFinance加载完成后再执行后续的INDEX()和MAX()计算，所以能正常显示结果。

解决方案 #

方案1：简化公式写法，避免嵌套解析问题 #

你原来的公式可以简化，不需要用INDEX()来提取第二列——因为MAX()可以直接作用于GoogleFinance返回的二维数组（它会自动忽略第一列的日期，只计算数值列）。把B2的公式改成：

=MAX(GOOGLEFINANCE(A2,"high","1/2/2018",TODAY()))

这样不仅更简洁，还能避免INDEX()带来的解析问题，脚本获取值时也会更稳定。

如果一定要保留INDEX()的写法，可以调整为更明确的数组引用：

=MAX(INDEX(GOOGLEFINANCE(A2,"high","1/2/2018",TODAY()),,2))

（去掉0参数，省略行参数默认就是所有行）

方案2：用Apps Script直接计算YTD最高价，不依赖单元格公式 #

如果不想依赖单元格的公式，可以直接在脚本里调用FinanceApp服务来获取数据并计算最大值，这样能彻底避免公式解析的问题：

function getStockYTDHigh() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var symbol = sheet.getRange("A2").getValue(); // 获取股票代码
  var startDate = new Date("2018-01-02");
  var endDate = new Date();
  
  // 获取历史数据（每日最高价）
  var historicalData = FinanceApp.getHistoricalData(symbol, startDate, endDate, FinanceApp.TimeUnit.DAYS);
  
  // 计算最高价
  var ytdHigh = 0;
  historicalData.forEach(function(dayData) {
    var dailyHigh = dayData.getHigh();
    if (dailyHigh > ytdHigh) {
      ytdHigh = dailyHigh;
    }
  });
  
  // 可以把结果写入B2，或者直接打印日志
  sheet.getRange("B2").setValue(ytdHigh);
  Logger.log("YTD High for " + symbol + ": " + ytdHigh);
}

注意：第一次运行这个脚本需要授权，允许访问FinanceApp服务。

方案3：等待公式计算完成后再获取值 #

如果一定要保留原来的公式，可以在脚本中加入等待逻辑，给GoogleFinance足够的时间加载数据：

function getStock(location) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeRange = sheet.getDataRange();
  
  // 强制刷新表格计算
  SpreadsheetApp.flush();
  
  // 等待2秒（可根据网络情况调整）
  Utilities.sleep(2000);
  
  var values = activeRange.getDisplayValues();
  var formula = activeRange.getFormulas();
  
  Logger.log(values[0]);
  Logger.log(values[1]);
  Logger.log(formula[1]);
}

不过这种方法不太可靠，因为网络加载时间不确定，可能还是会偶尔返回错误。

验证测试 #

修改公式后，重新运行你的原脚本，应该就能获取到正确的计算值了。如果用方案2的脚本，直接运行就能得到YTD最高价，不需要依赖单元格公式。

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