如何在Google Sheets Apps Script中触发IMPORTRANGE更新的onChange事件

一、解决PropertiesService的报错问题 #

你之前的报错是误用了setProperties()方法（复数形式），该方法要求传入对象参数，而你实际需要调用的是setProperty()（单数形式），用于设置单个键值对。修正后的代码如下：

function saveLastRow() {
  const ws2 = SpreadsheetApp.getActive().getSheetByName("Sheet 2");
  // 存储Sheet2的最后一行行数，更适合判断新增行逻辑
  PropertiesService.getScriptProperties().setProperty("lastSheet2Row", ws2.getLastRow());
}

二、实现Sheet2新增数据时自动同步到Sheet1 #

核心逻辑 #

1. 用onChange触发器监听电子表格变更（IMPORTRANGE的更新会触发OTHER类型的变更事件）；
2. 通过PropertiesService存储Sheet2的历史行数，每次触发时对比当前行数，仅当行数增加时执行同步；
3. 通过行数变化+工作表判断，排除手动编辑、删除行等无关操作。

完整代码 #

// 全局变量：替换为你的Destination表格URL
const DESTINATION_URL = "你的Destination表格URL";

// 初始化：首次运行存储Sheet2的初始行数
function init() {
  const ss = SpreadsheetApp.openByUrl(DESTINATION_URL);
  const ws2 = ss.getSheetByName("Sheet 2");
  PropertiesService.getScriptProperties().setProperty("lastSheet2Row", ws2.getLastRow());
}

// onChange事件触发函数
function onSheetChange(e) {
  const ss = SpreadsheetApp.openByUrl(DESTINATION_URL);
  const ws2 = ss.getSheetByName("Sheet 2");
  const ws1 = ss.getSheetByName("Sheet 1");
  
  // 获取历史行数与当前行数
  const props = PropertiesService.getScriptProperties();
  const lastRow = parseInt(props.getProperty("lastSheet2Row"));
  const currentRow = ws2.getLastRow();
  
  // 仅处理Sheet2行数增加的情况，排除删除行、手动编辑等操作
  if (currentRow > lastRow && e.source.getSheetName() === "Sheet 2") {
    // 一次性获取Sheet2新增的最后一行数据（第1至8列）
    const newRowData = ws2.getRange(currentRow, 1, 1, 8).getValues()[0];
    const [entryNo, company, firstName, surName, address, position, degree, dateHired] = newRowData;
    
    // 生成下一个companyNo
    const lastCompanyNo = ws1.getRange(ws1.getLastRow(), 2).getValue();
    const nextCompanyNo = lastCompanyNo + 1;
    
    // 构造要追加到Sheet1的数据（修正原代码中Address大小写错误）
    const dataToAppend = [
      entryNo,
      nextCompanyNo,
      company,
      firstName,
      surName,
      address,
      "", // department
      position,
      "", // schedule
      degree,
      dateHired
    ];
    
    // 追加到Sheet1
    ws1.appendRow(dataToAppend);
    
    // 更新历史行数
    props.setProperty("lastSheet2Row", currentRow);
  }
}

配置触发器步骤 #

1. 打开Destination表格的脚本编辑器；
2. 点击左侧「触发器」图标（闹钟形状）；
3. 点击「添加触发器」：
   • 选择运行函数：onSheetChange
   • 部署类型：「Head」
   • 事件来源：「从电子表格」
   • 事件类型：「更改」
   • 点击「保存」并完成权限授权。

三、原代码的优化点 #

• 原代码多次调用getRange()获取单个单元格，效率低下，改为一次性获取整行数据；
• 修正了dataArray中Address的大小写错误（与变量名address保持一致）；
• 用全局变量统一管理表格URL，避免重复编写。

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