Google Sheets复制行公式、旧行转存数值的代码及触发方案求助

打开你的Google Sheets表格，点击顶部菜单栏「扩展程序」>「Apps Script」，删除编辑器里默认的空白代码，粘贴以下代码：

function updateHistoricalData() {
  // 配置参数，可根据实际表格调整
  const SHEET_NAME = 'Historical_Data';
  const DATA_COLUMNS = 'A:H';

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const dataSheet = ss.getSheetByName(SHEET_NAME);

  // 自动获取最后一行有数据的行号
  const lastRow = dataSheet.getLastRow();
  // 读取最后一行A-H列的公式
  const lastRowFormulas = dataSheet.getRange(`${DATA_COLUMNS}${lastRow}`).getFormulas();
  // 把公式复制到下一个空白行
  dataSheet.getRange(`${DATA_COLUMNS}${lastRow + 1}`).setFormulas(lastRowFormulas);
  // 将上一行的公式替换为计算结果，仅保留数值
  const lastRowValues = dataSheet.getRange(`${DATA_COLUMNS}${lastRow}`).getValues();
  dataSheet.getRange(`${DATA_COLUMNS}${lastRow}`).setValues(lastRowValues);

  // 强制刷新数据，避免GOOGLEFINANCE接口延迟
  SpreadsheetApp.flush();
}

代码使用说明 #

• 首次运行代码时会弹出授权提示，按照指引允许脚本访问你的表格数据即可，所有操作仅在你的Google账号范围内执行，无数据泄露风险。
• 代码会自动识别「Historical_Data」标签的最后一行数据，不需要手动修改行号参数。
• 复制后的公式会自动继承原公式的相对引用规则，比如原A列的WORKDAY公式引用上一行日期，复制到新行后会自动适配引用新的上一行日期，GOOGLEFINANCE函数的日期引用也会自动对应新行的A列日期。

按照以下步骤配置触发器，即可实现每个工作日美国东部时间9点前自动运行：

1. 在Apps Script编辑界面左侧菜单栏点击「触发器」（闹钟图标），点击右下角「添加触发器」。
2. 按以下参数配置触发器：
   • 选择要运行的函数：updateHistoricalData
   • 选择事件源：时间驱动
   • 选择触发器类型：工作日计时器
   • 时区：务必选择美国东部时间（纽约），避免时区偏差
   • 执行时间范围：选择上午8:00~9:00，确保在要求的时间点前完成执行
3. 保存配置即可。

可选优化 #

如果担心节假日误触发产生无效数据，可以在代码开头增加交易日判断逻辑，读取「NYSE Holidays」的假期列表，判断当日是否为交易日，非交易日直接终止脚本运行即可。

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