如何修复requireValueInRange异常并精简Google Sheets重复脚本？

精简后的代码 #

function setDataValid_(range, sourceRange) {
  if (!sourceRange) return; // 避免命名范围不存在时引发错误
  const rule = SpreadsheetApp.newDataValidation()
    .requireValueInRange(sourceRange, true)
    .build();
  range.setDataValidation(rule);
}

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  const sheetName = sheet.getName();
  const cell = e.range;
  const col = cell.getColumn();

  // 定义需要处理的工作表和列规则
  const validSheets = ['Tháng 6', 'Tháng 7', 'Tháng 8', 'Tháng 9'];
  const validCols = [4, 5];

  // 快速过滤不符合条件的操作
  if (!validSheets.includes(sheetName) || !validCols.includes(col)) return;

  const targetRange = sheet.getRange(cell.getRow(), col + 1);
  const sourceRange = e.source.getRangeByName(cell.getValue());
  
  setDataValid_(targetRange, sourceRange);
}

原代码核心问题 #

• 冗余重复严重：每个工作表和列的判断逻辑完全复制，代码冗余度极高，维护时需修改多处，极易出现遗漏或错误。
• 无错误防护：当单元格值对应的命名范围不存在时，sourceRange为null，直接传入setDataValid_会触发脚本崩溃。
• 低效服务调用：重复调用SpreadsheetApp.getActiveSpreadsheet()和getRange，增加Google服务调用次数，容易触发执行超时限制。
• 简单触发局限性：onEdit作为简单触发无授权权限，若命名范围来自外部表格会因权限不足失效；同时30秒执行时间限制下，冗余代码更容易超时。

脚本失效原因排查方向 #

• 命名范围不匹配：其他表格中不存在对应单元格值的命名范围，或命名范围拼写、大小写存在差异。
• 工作表名称错误：其他表格中目标工作表的名称存在拼写错误（如空格、特殊字符、大小写不一致），导致判断条件不成立。
• 权限限制：若命名范围来自外部表格，简单触发的onEdit无跨表格访问权限，需改用可安装编辑触发（手动创建并授权）。
• 执行超时：原代码重复调用服务，频繁编辑时易超过30秒执行限制，导致脚本崩溃。

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