如何用Google Form新响应值更新主Google Sheet指定单元格

需求概述 #

• 主表格以ID为唯一主键，每行对应一个ID，列值填充无固定规律，支持手动编辑
• 独立Google Form的响应数据存储在同一工作簿的单独标签页
• 需触发式脚本：当表单响应页指定列设为「completed」时，按主键将新数据同步到主表格，仅更新有非空值的单元格
• 保留主表格无需更新的列，禁止替换整行
• 不增删主表格行，支持自定义同步的目标列号
• 此前使用Hyde's Move Rows脚本处理整行复制，无法适配此场景

解决方案脚本 #

function updateMasterSheet() {
  // 配置参数 - 根据你的表格实际情况修改
  const SPREADSHEET_ID = "你的工作簿ID"; // 从表格URL中提取ID
  const FORM_RESPONSE_SHEET = "表单响应"; // 表单响应标签页名称
  const MASTER_SHEET = "主表格"; // 主表格标签页名称
  const STATUS_COLUMN = 6; // 状态列的列号（从1开始计数）
  const PRIMARY_KEY_COLUMN_FORM = 1; // 表单响应中ID主键的列号
  const PRIMARY_KEY_COLUMN_MASTER = 1; // 主表格中ID主键的列号
  // 列映射：表单响应列号 => 主表格目标列号，按需添加/修改
  const COLUMN_MAPPING = {
    2: 2,
    3: 3,
    5: 5,
    // 例如：7: 4 表示表单第7列同步到主表格第4列
  };

  const ss = SpreadsheetApp.openById(SPREADSHEET_ID);
  const formSheet = ss.getSheetByName(FORM_RESPONSE_SHEET);
  const masterSheet = ss.getSheetByName(MASTER_SHEET);

  // 获取表单响应数据（跳过表头）
  const formData = formSheet.getDataRange().getValues().slice(1);
  // 提取主表格的主键列表，用于快速定位行
  const masterKeys = masterSheet.getRange(2, PRIMARY_KEY_COLUMN_MASTER, masterSheet.getLastRow()-1, 1).getValues().flat();

  formData.forEach((row, index) => {
    const status = row[STATUS_COLUMN - 1];
    const primaryKey = row[PRIMARY_KEY_COLUMN_FORM - 1];

    // 仅处理状态为completed的记录
    if (status !== "completed") return;

    // 查找主表格中对应主键的行号
    const masterRowNum = masterKeys.indexOf(primaryKey) + 2; // +2是因为表头占1行，索引转实际行号

    if (masterRowNum === 1) {
      console.log(`未找到主键【${primaryKey}】的记录，跳过`);
      return;
    }

    // 遍历配置的列，仅同步非空值
    Object.entries(COLUMN_MAPPING).forEach(([formCol, masterCol]) => {
      const formValue = row[formCol - 1];
      if (formValue && formValue !== "") {
        masterSheet.getRange(masterRowNum, masterCol).setValue(formValue);
      }
    });

    // 可选：标记已处理的行，避免重复执行
    formSheet.getRange(index + 2, STATUS_COLUMN).setValue("processed");
  });
}

配置与使用步骤 #

1. 打开目标Google Sheets，点击顶部「扩展程序」>「Apps脚本」
2. 替换代码中的配置参数，确保与你的表格结构匹配
3. 保存脚本，命名为UpdateMasterSheet
4. 设置触发器：
   • 点击左侧「触发器」图标 >「添加触发器」
   • 选择函数：updateMasterSheet
   • 事件源：「从电子表格」
   • 事件类型：「编辑时」（当编辑表单响应页时触发，或选「定时驱动」按周期执行）
5. 测试：在表单响应页的状态列选择「completed」，检查主表格对应行的指定列是否更新

注意事项 #

• 首次运行脚本需完成授权，确保脚本有访问表格数据的权限
• 列号从1开始计数，需准确对应表单响应页和主表格的列位置
• 可通过修改COLUMN_MAPPING自由控制哪些列需要同步
• 状态标记为「processed」的逻辑可按需移除，用于避免重复处理同一行记录

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