实现两个不同Google Sheets间的双向数据同步方法咨询

需求明确 #

实现源表与目标表的项目状态双向实时同步：

• 修改源表状态，目标表自动更新；修改目标表状态，源表自动同步
• 仅向承包商开放目标表操作权限，承包商更新状态后触发内部审核通知，审核不通过可从源表将状态退回

分步实现 #

1. 权限配置 #

• 给承包商分配目标表的编辑权限，隐藏目标表中无关工作表，仅保留他们需要操作的项目状态表
• 源表仅对内部团队开放编辑/查看权限，避免敏感数据泄露

2. 双向同步脚本（Google Apps Script） #

以「项目编号」作为唯一匹配键（需确保两个表格的项目编号完全一致），通过Google Apps Script实现跨表同步

源表脚本部署

打开源表 → 点击「扩展程序」→「Apps脚本」，替换工作表名称和表格ID后粘贴以下代码：

function onEdit(e) {
  const sourceSheet = e.source.getSheetByName("项目状态表"); // 替换为源表中存放状态的工作表名
  const targetSpreadsheetId = "1qjx1oiKLBnjs4RcDn6MJ2E99F11zDomYd7972qmLUJs"; // 替换为目标表ID（从URL提取）
  const targetSheet = SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName("承包商操作表"); // 替换为目标表对应工作表名
  
  const statusCol = 3; // 假设状态在第3列，根据实际调整
  if (e.range.getColumn() !== statusCol) return;
  
  const projectId = sourceSheet.getRange(e.range.getRow(), 1).getValue(); // 第1列为项目编号
  const newStatus = e.value;
  
  // 匹配目标表项目并更新状态
  const targetData = targetSheet.getDataRange().getValues();
  for (let i = 1; i < targetData.length; i++) {
    if (targetData[i][0] === projectId) {
      targetSheet.getRange(i+1, statusCol).setValue(newStatus);
      // 可选：给承包商发状态更新通知
      MailApp.sendEmail("contractor@example.com", "项目状态更新", `项目${projectId}已被退回修改，请重新提交`);
      break;
    }
  }
}

目标表脚本部署

打开目标表的Apps脚本，替换相关信息后粘贴以下代码：

function onEdit(e) {
  const sourceSheet = e.source.getSheetByName("承包商操作表"); // 替换为目标表中承包商操作的工作表名
  const targetSpreadsheetId = "1wIA5tz7U2D6Ba1j_uLKu3alMhe1BrfT2r_ATY4FHp5c"; // 替换为源表ID
  const targetSheet = SpreadsheetApp.openById(targetSpreadsheetId).getSheetByName("项目状态表"); // 替换为源表对应工作表名
  
  const statusCol = 3; // 对应状态列索引
  if (e.range.getColumn() !== statusCol) return;
  
  const projectId = sourceSheet.getRange(e.range.getRow(), 1).getValue();
  const newStatus = e.value;
  
  // 匹配源表项目并更新状态
  const targetData = targetSheet.getDataRange().getValues();
  for (let i = 1; i < targetData.length; i++) {
    if (targetData[i][0] === projectId) {
      targetSheet.getRange(i+1, statusCol).setValue(newStatus);
      // 给内部审核人员发待审核通知
      MailApp.sendEmail("reviewer@example.com", "项目待审核", `项目${projectId}状态更新为${newStatus}，请及时审核`);
      break;
    }
  }
}

3. 脚本授权与触发 #

• 保存脚本后点击「运行」，首次运行需完成权限授权（允许脚本访问表格和发送邮件）
• onEdit函数会自动绑定编辑触发事件，无需额外配置触发器

4. 优化建议 #

• 给状态列设置数据验证下拉选项（如「待处理」「审核中」「已完成」「退回修改」），避免无效输入
• 添加「更新时间」列，用公式=IF(ISBLANK(C2),"",NOW())（假设状态在C列）自动记录修改时间
• 若项目数量较多，可改用filter或findIndex优化匹配逻辑，提升脚本运行效率

注意事项 #

• 必须保证两个表格的「项目编号」完全一致，否则同步会失败
• 同步操作存在1-2秒延迟，建议避免短时间内重复编辑同一状态
• 邮件通知功能需确保Google账号允许发送外部邮件（企业账号可能有域限制）

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