谷歌表格股票追踪脚本逐行写入超时，求优化方案

问题背景 #

正在搭建自定义股票追踪工具，使用googlefinance()函数存在数据可靠性问题，且频繁调用影响性能，因此需要获取并存储历史股票数据。表格包含stockData（A列预填至2024-12-31的日期，其余列对应股票收盘价）、temp、log三个工作表。现有脚本逻辑可行，但逐行写入单元格的操作效率极低，频繁触发6分钟执行超时，需要优化数据写入方式。

核心优化方案 #

• 批量读写替代逐行操作：将所有价格数据先整理成二维数组，再一次性写入工作表，大幅减少SpreadsheetApp的API调用次数（这是性能提升的关键）
• 合并范围读取：一次性读取temp表的所有数据，避免多次调用getRange
• 简化日期格式化逻辑：统一日期格式处理，减少重复计算
• 减少不必要的单元格操作：仅在需要时写入表头，避免单独设置单个单元格值

优化后完整代码 #

function fetchHistoricalPrices(ticker) {
  const originalTicker = ticker;
  ticker = _ticker(ticker); // 格式化适配Google Finance

  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = spreadsheet.getSheetByName("stockData");
  const tempSheet = spreadsheet.getSheetByName("temp");
  const endDate = Utilities.formatDate(new Date(), "UTC", "yyyy-MM-dd");
  
  try {
    // 写入QUERY公式获取历史数据到temp表
    tempSheet.getRange("A1").setFormula(
      `=QUERY(googlefinance("${ticker}", "close", "2000-01-01", "${endDate}"), 
       "SELECT Col1, Col2 LABEL Col1 'Date', Col2 'Price' FORMAT Col1 'DD.MM.YYYY', Col2 '0.00'", 1)`
    );
    log(`数据检索并写入temp表完成：${originalTicker}，时间范围2000-01-01至${endDate}`);

    // 一次性读取temp表所有数据（跳过表头）
    const tempData = tempSheet.getRange(2, 1, tempSheet.getLastRow() - 1, 2).getValues();
    
    // 构建日期-价格映射字典
    const datePriceMap = {};
    tempData.forEach(row => {
      const formattedDate = Utilities.formatDate(new Date(row[0]), "UTC", "dd.MM.yyyy");
      datePriceMap[formattedDate] = row[1];
    });
    log(`日期-价格字典构建完成：${originalTicker}`);

    // 获取stockData表的表头（跳过A列）和所有日期数据
    const headerRange = sheet.getRange(1, 2, 1, sheet.getLastColumn() - 1);
    const headers = headerRange.getValues()[0] || [];
    const datesStockData = sheet.getRange(2, 1, sheet.getLastRow() - 1, 1).getValues();

    // 确定目标列索引
    let tickerColumnIndex = sheet.getLastColumn() + 1;
    const existingTickerIndex = headers.indexOf(originalTicker);
    if (existingTickerIndex !== -1) {
      tickerColumnIndex = existingTickerIndex + 2; // 转换为1-based索引，跳过A列
    } else {
      // 写入表头（如果是新列）
      sheet.getRange(1, tickerColumnIndex).setValue(originalTicker);
    }

    // 批量构建价格数组
    const priceArray = datesStockData.map(row => {
      const formattedDate = Utilities.formatDate(new Date(row[0]), "UTC", "dd.MM.yyyy");
      return [datePriceMap[formattedDate] || "NA"];
    });

    // 一次性写入所有价格数据
    sheet.getRange(2, tickerColumnIndex, priceArray.length, 1).setValues(priceArray);

    tempSheet.clear();
    log(`数据写入stockData表完成：${originalTicker}`);
  } catch (error) {
    log(`获取${originalTicker}数据出错：${error}`);
  }

  Utilities.sleep(5000);
}

优化说明 #

1. 批量写入：将原本逐行调用setValue的逻辑改为先构建priceArray二维数组，再通过一次setValues写入，直接将API调用次数从几百/几千次降到1次
2. 合并读取：将datesTemp和pricesTemp的两次getRange合并为一次读取tempData，减少API交互
3. 简化循环：使用forEach和map替代传统for循环，代码更简洁的同时保持性能
4. 减少冗余操作：仅在新增ticker时写入表头，避免不必要的单元格访问

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