Google Apps Script：为依赖动态SKU的单元格批量设置数据验证

问题分析 #

你的核心痛点在于：

1. 原生onEdit触发器仅响应手动编辑操作，无法识别ARRAYFORMULA驱动的列更新
2. 原脚本未做批量优化，处理大量行时性能受限
3. 未精准定位有SKU的行，导致数据验证错误应用到空白行

解决方案 #

以下是优化后的完整脚本，配合安装型触发器实现需求：

function updateSupplierValidation(e) {
  // 仅响应Draft_Order表AA列的编辑操作
  if (!e || e.source.getSheetName() !== 'Draft_Order' || e.range.getColumn() !== 27) {
    return;
  }

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ordersSheet = ss.getSheetByName('Orders');
  const itemListSheet = ss.getSheetByName('Item_List');

  // 构建SKU与供应商的映射关系（一次性读取全量数据）
  const itemData = itemListSheet.getDataRange().getValues();
  const skuToSuppliers = {};
  
  for (let i = 1; i < itemData.length; i++) {
    const sku = itemData[i][1]; // Item_List表B列（SKU）
    const supplier = itemData[i][4]; // Item_List表E列（供应商）
    
    if (sku && supplier) {
      skuToSuppliers[sku] = skuToSuppliers[sku] || [];
      if (!skuToSuppliers[sku].includes(supplier)) {
        skuToSuppliers[sku].push(supplier);
      }
    }
  }

  // 获取Orders表D列的有效数据范围
  const ordersLastRow = ordersSheet.getLastRow();
  if (ordersLastRow < 2) return;
  
  const skuRange = ordersSheet.getRange(2, 4, ordersLastRow - 1, 1);
  const skuValues = skuRange.getValues();

  // 批量处理每一行的供应商验证
  for (let i = 0; i < skuValues.length; i++) {
    const rowNum = i + 2;
    const sku = skuValues[i][0];
    const eCell = ordersSheet.getRange(rowNum, 5); // Orders表E列对应行

    if (sku && skuToSuppliers[sku]) {
      // 创建下拉验证规则
      const validationRule = SpreadsheetApp.newDataValidation()
        .requireValueInList(skuToSuppliers[sku], false) // false表示仅允许选择列表内选项
        .setAllowInvalid(false)
        .build();
      eCell.setDataValidation(validationRule);
    } else {
      // 无SKU时清除验证规则
      eCell.clearDataValidations();
    }
  }
}

配置安装型触发器 #

1. 打开表格的脚本编辑器（工具 → 脚本编辑器）
2. 点击左侧「触发器」图标（时钟形状）
3. 点击「添加触发器」，配置如下：
   • 运行函数：updateSupplierValidation
   • 事件源：电子表格
   • 事件类型：编辑时
   • 可选限制：仅监听Draft_Order工作表
4. 保存并完成权限授权

效果说明 #

• 自动触发：只要在Draft_Order表AA列输入SKU，脚本会自动触发，同步处理Orders表对应行的供应商验证
• 批量支持：一次性读取全量SKU-供应商映射，遍历处理100+行无性能问题
• 精准范围：仅给Orders表D列有SKU的行设置验证，空白行自动清除验证规则

内容的提问来源于stack exchange，提问作者HOANG TRUNG LE