Google Apps Script粘贴多行时onEdit逐行触发实现方法

问题原因 #

原代码仅读取触发编辑范围左上角的单个单元格值生成校验规则，没有对编辑范围内的每一行做单独遍历处理，因此批量粘贴多行数据时，会把第一行匹配到的规则错误应用到所有行。

修正后可直接使用的代码 #

function onEdit(event) {
  const ss = event.source.getActiveSheet();
  // 非Main表的编辑直接终止执行
  if (ss.getName() !== "Main") return;

  const editRange = event.range;
  const firstRow = editRange.getRow();
  const lastRow = editRange.getLastRow();
  const firstCol = editRange.getColumn();
  const lastCol = editRange.getLastColumn();

  // 编辑范围不涉及C列（第3列）直接终止执行
  if (lastCol < 3 || firstCol > 3) return;

  const datass = event.source.getSheetByName("Lists");
  // 一次性读取Lists表的一级分类表头，避免循环内重复调用接口
  const typeHeaders = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues()[0];
  // 缓存校验规则，相同分类的规则只生成一次，提升执行效率
  const validationRuleCache = {};

  // 逐行遍历编辑范围
  for (let row = firstRow; row <= lastRow; row++) {
    // 跳过表头行
    if (row <= 1) continue;
    const currentCell = ss.getRange(row, 3);
    const cellValue = currentCell.getValue().toString().trim();
    const targetCell = currentCell.offset(0, 1);
    // 先清空目标单元格原有内容和校验规则
    targetCell.clearContent().clearDataValidations();

    // 匹配当前值对应的分类列
    const typeIndex = typeHeaders.indexOf(cellValue);
    if (typeIndex === -1) continue;

    // 缓存无对应规则时先生成规则
    if (!validationRuleCache[cellValue]) {
      const validationRange = datass.getRange(2, typeIndex + 1, datass.getLastRow() - 1);
      validationRuleCache[cellValue] = SpreadsheetApp.newDataValidation()
        .requireValueInRange(validationRange)
        .setAllowInvalid(false) // 不需要禁止输入下拉外的值可删除此行
        .build();
    }
    // 给当前行目标单元格挂载对应下拉规则
    targetCell.setDataValidation(validationRuleCache[cellValue]);
  }
}

核心改动说明 #

• 不再对整个编辑范围套用统一规则，逐行遍历编辑范围内C列的单元格，单独匹配对应分类的二级下拉规则
• 增加前置拦截判断：非目标工作表、非目标列的编辑直接终止脚本，减少无意义的接口调用
• 做了性能优化：一次性读取分类表头、增加规则缓存，避免批量粘贴时重复调用Spreadsheet服务导致脚本超时
• 全场景兼容：支持单个单元格选值、跨多行粘贴、选中多单元格批量输入等所有编辑操作

使用注意 #

• 请确保Lists表第一行的表头值和Main表C列的一级下拉选项完全一致，每列表头下方存放对应分类的二级下拉选项
• 若粘贴的C列值在Lists表头中无匹配项，脚本会自动清空对应行D列的内容和原有校验规则，不会生成无效下拉
• 若需要调整触发列、下拉生成列的位置，直接修改代码中对应的列号参数即可

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