如何在Google Script中将AVGetCompanyOverview公式结果转为数组？

问题背景 #

在Google Sheet中使用Alphavantage插件获取股票数据，API限制为每分钟5次、每日500次，需处理6000只股票的月度数据更新。原脚本通过在单元格写入AVGetCompanyOverview公式再读取值的方式实现，希望优化为直接将公式结果转为数组，无需操作实际单元格。

解决方案 #

利用Google Apps Script的SpreadsheetApp.newFormula()和evaluate()方法，直接计算公式结果并转为数组，避免单元格读写操作，同时优化批量处理逻辑。

修改后的完整脚本 #

function MarketDataMonthly() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Data");
  const lastRow = sheet.getLastRow();
  // 将二维的股票代码数组转为一维，方便遍历
  const symbols = sheet.getRange(2, 2, lastRow - 1).getValues().flat();
  
  // 初始化输出数组：每行对应一只股票，每列对应一个指标（EBITDA到Beta共13列）
  const outputData = Array(symbols.length).fill().map(() => Array(13).fill(""));
  
  // 按API每日限制分批次处理，每批次500只
  const dailyBatchSize = 500;
  const totalBatches = Math.ceil(symbols.length / dailyBatchSize);
  
  for (let batch = 0; batch < totalBatches; batch++) {
    const startIndex = batch * dailyBatchSize;
    const currentBatch = symbols.slice(startIndex, startIndex + dailyBatchSize);
    
    for (let i = 0; i < currentBatch.length; i++) {
      const symbol = currentBatch[i];
      if (!symbol) continue; // 跳过空值股票代码
      
      try {
        // 构造公式并直接计算结果，无需写入单元格
        const formula = SpreadsheetApp.newFormula(`AVGetCompanyOverview("${symbol}")`).build();
        const overviewResult = SpreadsheetApp.getActiveSpreadsheet().evaluate(formula).getValues();
        
        // 将两列的结果数组转为键值对对象，方便按指标名取值
        const overviewObj = overviewResult.reduce((acc, [key, value]) => {
          acc[key] = value;
          return acc;
        }, {});
        
        // 按目标列顺序填充数据，不存在的指标留空
        outputData[startIndex + i] = [
          overviewObj.EBITDA || "",
          overviewObj["PE Ratio"] || "",
          overviewObj["Book Value"] || "",
          overviewObj["Dividend Per Share"] || "",
          overviewObj["Dividend Yield"] || "",
          overviewObj["EPS"] || "",
          overviewObj["Profit Margin"] || "",
          overviewObj["Return on Assets"] || "",
          overviewObj["Return on Equity"] || "",
          overviewObj["Price to Book Ratio"] || "",
          overviewObj["EV to Revenue"] || "",
          overviewObj["EV to EBITDA"] || "",
          overviewObj.Beta || ""
        ];
        
        // 遵守每分钟5次的限制，每次间隔12秒（60秒/5次）
        Utilities.sleep(12000);
      } catch (error) {
        console.error(`处理股票${symbol}时出错: ${error.message}`);
        // 出错时仍需等待，避免触发API限制
        Utilities.sleep(12000);
      }
    }
    
    // 除最后一批次外，每批处理完暂停24小时，符合每日500次限制
    if (batch < totalBatches - 1) {
      Utilities.sleep(86400000); // 24小时的毫秒数
    }
  }
  
  // 一次性将所有数据写入对应列（第9列到第21列），提升效率
  sheet.getRange(2, 9, outputData.length, 13).setValues(outputData);
}

关键优化点 #

• 无单元格操作：通过evaluate()直接执行公式，返回结果数组，完全绕开单元格读写，避免不必要的Sheet交互。
• 数据映射更可靠：将返回的两列数据转为键值对对象，按指标名取值，比原脚本按固定行号读取更稳定（避免插件返回的指标顺序变动）。
• 批量写入：所有数据整理完成后一次性写入Sheet，大幅减少脚本执行时间（原脚本逐行写入效率极低）。
• 严格遵守API限制：通过Utilities.sleep()控制调用间隔，分批次每日处理500只股票，避免触发限制导致失败。
• 异常处理：添加try-catch块捕获单只股票的处理异常，确保脚本不会因个别错误中断。

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