Google Sheets编写onEdit函数实现复选框触发跨表复制数据

以下脚本可直接实现需要的交互逻辑，基于简单触发器onEdit实现，无需额外部署。

部署步骤 #

• 打开目标Google表格，点击顶部菜单栏「扩展程序」→「Apps Script」进入脚本编辑器
• 清空编辑器内默认生成的空函数代码，粘贴下方提供的完整脚本
• 点击顶部保存按钮，为项目输入任意名称后确认
• 首次触发时（即第一次点击J16复选框）会弹出授权提示，按页面指引完成Google账号授权即可正常使用（个人脚本的安全提示为正常现象，脚本仅在你自己的表格内运行，不会上传任何数据）

完整代码 #

function onEdit(e) {
  // 拦截无事件对象的手动运行场景
  if (!e) throw new Error('请勿手动运行此函数，编辑单元格时将自动触发');
  const editedRange = e.range;
  const sourceSheet = editedRange.getSheet();
  
  // 仅当编辑位置为DATA PULL表的J16单元格时执行后续逻辑
  if (sourceSheet.getName() !== 'DATA PULL' || editedRange.getA1Notation() !== 'J16') return;
  const targetSheet = e.source.getSheetByName('REPORTS');
  if (!targetSheet) throw new Error('请确认目标表名为REPORTS，注意大小写与空格完全匹配');

  // 复选框被勾选时执行复制粘贴
  if (e.value === 'TRUE') {
    // 定义源数据区域，若实际范围有偏差可直接修改引号内的A1标记
    // 原需求中I17:E20为反向选区，实际有效范围等价于E17:I20
    const sourceRange1 = sourceSheet.getRange('F15:F20').getValues();
    const sourceRange2 = sourceSheet.getRange('E17:I20').getValues();

    // 自动定位REPORTS表中第一个空行作为写入起始行，天然避免覆盖历史数据
    const targetRow = targetSheet.getLastRow() + 1;

    // 写入第一块数据：默认从目标行A列开始写入，如需对齐源表F列，将第二个参数改为6即可
    targetSheet.getRange(targetRow, 1, sourceRange1.length, sourceRange1[0].length).setValues(sourceRange1);
    // 写入第二块数据：默认接在第一块数据后写入，如需对齐源表E列，将第二个参数改为5即可
    targetSheet.getRange(targetRow, 1 + sourceRange1[0].length, sourceRange2.length, sourceRange2[0].length).setValues(sourceRange2);

    // 可选：写入完成后自动取消复选框勾选，不需要此功能可直接注释掉下一行
    editedRange.setValue(false);
  }

  // 复选框取消勾选时无需额外操作，下次勾选会自动识别新的空行写入，完全匹配需求中的行定位逻辑
}

调整说明 #

• 脚本默认仅复制单元格纯值，不会携带格式、公式、条件格式、数据验证规则，如果需要同步复制格式，可将getValues()+setValues()的逻辑替换为copyTo()方法，指定复制类型即可
• 如果需要让粘贴位置和源表列完全对应，只需修改两处写入逻辑中getRange的第二个参数（列序号，A=1、B=2……F=6、E=5，以此类推）
• 如果后续需要调整源数据的抓取范围，直接修改代码中getRange()内的A1区域字符串即可
• 脚本自动识别目标表最后一行的逻辑不受手动插入/删除行的影响，稳定性高于手动记录行号的实现方式

内容的提问来源于stack exchange，提问作者Natasha Marie Canubida