多工作表多级联动下拉列表（数据验证）脚本适配问题

完整修改后的脚本 #

// 定义需要支持三级联动的工作表名称数组
const TARGET_WORKSHEETS = ["Sheet1", "Sheet2", "Sheet3"];
// 定义三级下拉对应的列号（A列=1，B列=2，C列=3，可按需修改）
const FIRST_LEVEL_COL = 1;
const SECOND_LEVEL_COL = 2;
const THIRD_LEVEL_COL = 3;
// 参考数据工作表名称
const REF_WS_NAME = "ReferenceData";

function onEdit(e) {
  const activeSheet = e.source.getActiveSheet();
  const sheetName = activeSheet.getName();
  
  // 跳过非目标工作表的编辑操作
  if (!TARGET_WORKSHEETS.includes(sheetName)) return;
  
  const activeCell = e.range;
  const col = activeCell.getColumn();
  const row = activeCell.getRow();
  
  // 忽略表头行（假设第一行为表头，可根据实际调整）
  if (row === 1) return;
  
  const refWs = e.source.getSheetByName(REF_WS_NAME);
  if (!refWs) {
    SpreadsheetApp.getUi().alert("未找到参考数据工作表：" + REF_WS_NAME);
    return;
  }
  
  const refData = refWs.getDataRange().getValues();
  
  // 第一级选项变更时，更新第二级下拉
  if (col === FIRST_LEVEL_COL) {
    // 清空当前行的二、三级下拉内容
    activeSheet.getRange(row, SECOND_LEVEL_COL).clearContent();
    activeSheet.getRange(row, THIRD_LEVEL_COL).clearContent();
    
    const firstLevelValue = activeCell.getValue();
    if (!firstLevelValue) return;
    
    // 筛选对应第一级的第二级选项（去重）
    const secondLevelOptions = [...new Set(refData
      .filter(row => row[0] === firstLevelValue)
      .map(row => row[1])
      .filter(val => val !== ""))];
    
    // 设置第二级下拉验证规则
    const rule = secondLevelOptions.length > 0 
      ? SpreadsheetApp.newDataValidation()
          .requireValueInList(secondLevelOptions, true)
          .setAllowInvalid(false)
          .build()
      : null;
    activeSheet.getRange(row, SECOND_LEVEL_COL).setDataValidation(rule);
  }
  
  // 第二级选项变更时，更新第三级下拉
  if (col === SECOND_LEVEL_COL) {
    // 清空当前行的第三级下拉内容
    activeSheet.getRange(row, THIRD_LEVEL_COL).clearContent();
    
    const firstLevelValue = activeSheet.getRange(row, FIRST_LEVEL_COL).getValue();
    const secondLevelValue = activeCell.getValue();
    if (!firstLevelValue || !secondLevelValue) return;
    
    // 筛选对应一、二级的第三级选项（去重）
    const thirdLevelOptions = [...new Set(refData
      .filter(row => row[0] === firstLevelValue && row[1] === secondLevelValue)
      .map(row => row[2])
      .filter(val => val !== ""))];
    
    // 设置第三级下拉验证规则
    const rule = thirdLevelOptions.length > 0 
      ? SpreadsheetApp.newDataValidation()
          .requireValueInList(thirdLevelOptions, true)
          .setAllowInvalid(false)
          .build()
      : null;
    activeSheet.getRange(row, THIRD_LEVEL_COL).setDataValidation(rule);
  }
}

关键修改说明 #

• 目标工作表动态匹配：通过TARGET_WORKSHEETS数组指定所有需要支持联动的工作表，新增工作表时直接往数组中添加名称即可。
• 无差别适配多表：所有下拉操作（清空内容、设置验证）均基于当前编辑的activeSheet，不再绑定固定工作表。
• 保留核心联动逻辑：三级选项的筛选、去重规则沿用原脚本逻辑，保证功能一致性的同时扩展了适用范围。

使用注意事项 #

1. 确保ReferenceData工作表的结构为：第一列=第一级选项，第二列=第二级选项，第三列=第三级选项（每行对应一组完整的三级关联数据）。
2. 可根据实际布局修改FIRST_LEVEL_COL、SECOND_LEVEL_COL、THIRD_LEVEL_COL的列号值。
3. 若不需要严格限制下拉输入，可将setAllowInvalid(false)改为true。

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