如何实现Google Sheets源表与目标表的数据变更同步（基于copyFormResponses）

要解决源表数据修改后目标表同步更新的问题，核心是建立源表与目标表行的关联关系，再结合可安装触发器实现实时同步。以下是具体步骤：

一、完善初始表单提交复制逻辑（保留关联标识） #

原代码仅复制表单提交的内容，但我们需要把源表中唯一的提交时间戳（表单响应表默认第一列，每个提交唯一）同步到目标表，作为后续匹配更新的标识。修改后的提交复制代码：

function copyFormResponses(e) {
  const targetSpreadsheetUrl = "替换为你的目标表格URL";
  const targetSheetName = "替换为目标工作表名称";
  const targetSheet = SpreadsheetApp.openByUrl(targetSpreadsheetUrl).getSheetByName(targetSheetName);
  
  // 获取源表当前提交的完整行数据（包含第一列的时间戳）
  const sourceRow = e.range.getSheet().getRange(e.range.rowStart, 1, 1, e.range.getSheet().getLastColumn()).getValues()[0];
  const lastRow = targetSheet.getLastRow();
  
  // 写入整行数据，保留时间戳作为关联标识
  targetSheet.getRange(lastRow + 1, 1, 1, sourceRow.length).setValues([sourceRow]);
}

二、编写编辑同步函数 #

当源表的行被修改时，通过时间戳定位目标表中对应的行并更新数据。注意：必须使用可安装的onEdit触发器（而非简单触发），因为简单触发无法跨表格访问数据。

function syncSourceToTarget(e) {
  const sourceSheet = e.range.getSheet();
  // 限定只监听表单响应表（替换为你的源表名称）
  if (sourceSheet.getName() !== "表单响应表") return;
  
  const targetSpreadsheetUrl = "替换为你的目标表格URL";
  const targetSheetName = "替换为目标工作表名称";
  const targetSheet = SpreadsheetApp.openByUrl(targetSpreadsheetUrl).getSheetByName(targetSheetName);
  
  // 获取当前编辑行的时间戳和完整数据
  const editedRow = sourceSheet.getRange(e.range.rowStart, 1, 1, sourceSheet.getLastColumn()).getValues()[0];
  const timestamp = editedRow[0];
  
  // 在目标表中查找匹配时间戳的行
  const targetData = targetSheet.getDataRange().getValues();
  let targetRowIndex = -1;
  for (let i = 0; i < targetData.length; i++) {
    if (targetData[i][0] === timestamp) {
      targetRowIndex = i + 1; // 转换为表格行号（从1开始计数）
      break;
    }
  }
  
  // 找到对应行后更新数据
  if (targetRowIndex !== -1) {
    targetSheet.getRange(targetRowIndex, 1, 1, editedRow.length).setValues([editedRow]);
  }
}

三、配置可安装触发器 #

1. 打开脚本编辑器，点击左侧「触发器」图标（闹钟样式）
2. 点击「添加触发器」，按以下配置：
   • 选择运行函数：syncSourceToTarget
   • 部署类型：Head
   • 事件源：从电子表格
   • 事件类型：编辑时
3. 点击保存，完成授权流程

关键注意事项 #

• 确保源表和目标表的第一列都是表单提交的时间戳，若表单未开启该功能，需在表单设置中勾选「收集提交者的时间戳」
• 若源表和目标表为同一表格，可使用简单触发onEdit，但跨表格必须用可安装触发器
• 测试时直接编辑源表某一行，验证目标表对应行是否同步更新

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