Google Sheets单元格非空触发宏及多行范围适配方案

场景与需求 #

• 已搭建关联数据库的下拉菜单，每个下拉选项对应唯一数字编码：下拉值保持选中的原始状态时，编码自动填充至同行O列校验单元格；下拉值被手动修改后，O列校验单元格返回空值
• 实现目标：编辑下拉单元格时，仅当同行O列存在有效非空编码，就将编码写入同行R列留存原始记录；O列编码为空时不执行写入，避免覆盖R列已经留存的原始选中值
• 涉及范围：
  • 目标工作表：Response Builder - Erin
  • 下拉单元格区间：H26:H65
  • 编码校验列区间：O26:O65
  • 原始值留存列区间：R26:R65

原有代码问题 #

最初编写的两版触发脚本均存在逻辑错误：判断条件中直接使用字符串'O26'与空值/0比较，没有实际读取O26单元格的存储值，因此无论O26是否为空都会执行复制操作，无法满足判断要求。
此前已验证可用的单行（仅适配H26单元格）脚本如下：

function onEdit(e) {
  e.source.toast("Entry")
  const sh = e.range.getSheet();
  if (sh.getName() == "Response Builder - Erin" && e.range.columnStart == 8 && e.range.rowStart == 26 && e.value) {
    e.source.toast("Flag1")
    let v = sh.getRange("O26").getValue();
    if (v !== "") {
      sh.getRange("R26").setValue(sh.getRange("O26").getValue());
    }
  }
}

多行适配可用代码 #

将单行逻辑扩展到H26:H65全区间的可运行代码如下：

function onEdit(e) {
  if (!e) throw new Error("请勿直接运行该函数，编辑单元格时会自动触发");
  const sh = e.range.getSheet();
  const row = e.range.rowStart;
  const col = e.range.columnStart;

  // 匹配触发条件：目标工作表、H列（第8列）、行号在26-65之间、编辑后单元格有值
  if (sh.getName() === "Response Builder - Erin" && col === 8 && row >=26 && row <=65 && e.value) {
    const oValue = sh.getRange(`O${row}`).getValue();
    // 仅当O列对应值非空时写入R列
    if (oValue !== "") {
      sh.getRange(`R${row}`).setValue(oValue);
    }
  }
}

代码说明 #

• 移除了调试用的弹窗提示，日常运行无多余干扰
• 自动读取当前编辑单元格的行号，动态匹配同行O列、R列的单元格，无需逐行写死地址，完整覆盖26-65行的所有下拉单元格
• 增加了直接运行的报错提示，避免手动执行函数时出现无意义报错
• 逻辑完全匹配需求：下拉值选中后未被修改时，O列生成有效编码，自动写入R列留存；后续手动修改下拉值导致O列编码为空时，不会执行写入操作，R列永久保留第一次选中时的原始编码

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