Google Sheets如何通过脚本每日自动填充公式值到空行解决#N/A报错

改后可用完整代码 #

// 给菜单添加运行按钮
function onOpen() { 
  const ui = SpreadsheetApp.getUi();
  ui.createMenu("自动触发") 
    .addItem("运行","runAuto") 
    .addToUi(); 
}

// 菜单按钮绑定的运行函数
function runAuto() { 
  recordValue();
}

// 创建定时触发器，每个工作日上午9点运行
function createTimeDrivenTrigger() {
  ScriptApp.newTrigger('recordValue')
      .timeBased()
      .onWeekDay(ScriptApp.WeekDay)
      .atHour(9)
      .create();
}

// 核心记录数据函数
function recordValue() { 
  if (!isNotHoliday()) return;
  
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const historySheet = ss.getSheetByName("Historical_Data");
  const holidayRangeStr = "'NYSE Holidays'!$A$2:$A$27";
  const lastRow = historySheet.getLastRow();
  const newRow = lastRow + 1;

  // 按列顺序定义所有36列的公式，列A到AJ对应索引0到35
  const formulas = [
    `WORKDAY(A${lastRow},1,${holidayRangeStr})`, // A列 日期
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VIX9D","close",$A${newRow}),2,2)`, // B列 VIX9D
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VIX","close",$A${newRow}),2,2)`, // C列 VIX1D
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VIX3M","close",$A${newRow}),2,2)`, // D列 VIX3M
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VIX6M","close",$A${newRow}),2,2)`, // E列 VIX6M
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VIX1Y","close",$A${newRow}),2,2)`, // F列 VIX1Y
    `INDEX(GOOGLEFINANCE("INDEXCBOE:VVIX","close",$A${newRow}),2,2)`, // G列 VVIX
    `INDEX(GOOGLEFINANCE("INDEXSP:.INX","close",$A${newRow}),2,2)`, // H列 SPX
    `B${newRow}/C${newRow}`, // I列 VIX9D:VIX
    `C${newRow}/D${newRow}`, // J列 VIX:VIX3M
    `C${newRow}/E${newRow}`, // K列 VIX:VIX6M
    `C${newRow}/F${newRow}`, // L列 VIX:VIX1Y
    `CORREL(H${newRow-4}:H${newRow},G${newRow-4}:G${newRow})`, // M列 5日SPX&VVIX相关性
    `CORREL(H${newRow-9}:H${newRow},C${newRow-9}:C${newRow})`, // N列 10日SPX&VIX相关性
    `""`, // O列 暂无公式
    `""`, // P列 暂无公式
    `LN(H${newRow}/H${lastRow})`, // Q列 对数收益率
    `STDEV(Q${newRow-9}:Q${newRow})`, // R列 V10
    `STDEV(Q${newRow-19}:Q${newRow})`, // S列 V20
    `SQRT(252)*R${newRow}`, // T列 HV10
    `SQRT(252)*S${newRow}`, // U列 HV20
    `C${newRow}-(T${newRow}*100)`, // V列 VIX-HV10
    `C${newRow}-(U${newRow}*100)`, // W列 VIX-HV20
    `PERCENTRANK(B$2:B${lastRow},B${newRow})`, // X列 VIX9D百分位
    `PERCENTRANK(C$2:C${lastRow},C${newRow})`, // Y列 VIX1D百分位
    `PERCENTRANK(D$2:D${lastRow},D${newRow})`, // Z列 VIX3M百分位
    `PERCENTRANK(E$2:E${lastRow},E${newRow})`, // AA列 VIX6M百分位
    `PERCENTRANK(F$2:F${lastRow},F${newRow})`, // AB列 VIX1Y百分位
    `PERCENTRANK(G$2:G${lastRow},G${newRow})`, // AC列 VVIX百分位
    `MEDIAN(B$2:B${lastRow})`, // AD列 VIX9D中位数
    `MEDIAN(C$2:C${lastRow})`, // AE列 VIX1D中位数
    `MEDIAN(D$2:D${lastRow})`, // AF列 VIX3M中位数
    `MEDIAN(E$2:E${lastRow})`, // AG列 VIX6M中位数
    `MEDIAN(F$2:F${lastRow})`, // AH列 VIX1Y中位数
    `MEDIAN(G$2:G${lastRow})`, // AI列 VVIX中位数
    `C${newRow}-B${newRow}` // AJ列 VDelta
  ];

  // 写入公式后等待计算完成，再转存为数值
  const targetRange = historySheet.getRange(newRow, 1, 1, 36);
  targetRange.setFormulas([formulas]);
  SpreadsheetApp.flush();
  Utilities.sleep(1500); // 预留1.5秒等待GoogleFinance数据拉取完成，可根据实际情况调整时长
  targetRange.setValues(targetRange.getValues());
} 

// 判断是否为NYSE交易日
function isNotHoliday(){ 
  const yesterday = new Date(new Date().getFullYear(), new Date().getMonth(), new Date().getDate()-1); 
  const formattedDate = Utilities.formatDate(yesterday, Session.getScriptTimeZone(), "M/d/yy");
  const holidays = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('NYSE Holidays').getRange('A2:A').getDisplayValues().flat();
  return !holidays.includes(formattedDate);
}

改动说明 #

• 所有公式直接在代码中定义，不需要在工作表中提前预置公式，完全避免复制公式时的#N/A报错问题
• 公式写入新行后会等待计算完成，立刻覆盖为纯数值，工作表中不会残留任何公式
• 完全适配你提供的所有公式逻辑，包括动态引用上一行、历史区间的需求
• 优化了节假日判断的代码逻辑，运行稳定性更高

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