Google Sheets多工作表多级依赖下拉列表脚本实现方案

云原生阿岚

2026-9-1

可扩展多工作表依赖下拉列表实现

原硬编码仅支持单工作表的脚本扩展性差，复制多份代码容易出现变量冲突、触发器重复触发问题，把所有规则抽成统一配置即可实现一次编码支持任意多工作表的同类需求。

重构后完整代码

// 统一配置项：新增工作表只需在这里加规则即可，无需修改其他业务代码
const DROPDOWN_CONFIG = [
  {
    targetSheetName: "SheetB", // 要生成下拉的目标工作表
    sourceSheetName: "Options", // 数据源工作表
    sourcePrimaryCol: 14, // 数据源一级下拉列号（N列=14）
    sourceSecondaryCol: 15, // 数据源二级下拉列号（O列=15）
    sourceDataStartRow: 7, // 数据源起始行
    sourceDataEndRow: 500, // 数据源结束行
    targetPrimaryCol: 3, // 目标表一级下拉放置列（C列=3）
    targetDropStartRow: 2, // 目标表下拉起始行
    targetDropEndRow: 500 // 目标表下拉结束行
  },
  {
    targetSheetName: "SheetC", // 第二组下拉对应SheetC
    sourceSheetName: "Options",
    sourcePrimaryCol: 17, // 按第二组数据源实际列号修改，示例为Q列=17
    sourceSecondaryCol: 18, // 示例为R列=18，按实际情况修改
    sourceDataStartRow: 7,
    sourceDataEndRow: 500,
    targetPrimaryCol: 3, // 按SheetC实际放置一级下拉的列号修改
    targetDropStartRow: 2,
    targetDropEndRow: 500
  }
  // 后续要加SheetD、SheetE，直接在数组里追加对应配置对象即可
];

// 批量为所有配置的工作表生成一级下拉
function createAllPrimaryDropdowns() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  DROPDOWN_CONFIG.forEach(config => {
    const sourceSheet = ss.getSheetByName(config.sourceSheetName);
    const targetSheet = ss.getSheetByName(config.targetSheetName);
    // 读取一级下拉数据源，过滤空值
    const primaryList = sourceSheet
      .getRange(config.sourceDataStartRow, config.sourcePrimaryCol, config.sourceDataEndRow - config.sourceDataStartRow + 1, 1)
      .getValues()
      .flat()
      .filter(item => item !== "");
    // 应用数据验证到目标列
    const targetRange = targetSheet.getRange(
      config.targetDropStartRow,
      config.targetPrimaryCol,
      config.targetDropEndRow - config.targetDropStartRow + 1,
      1
    );
    const rule = SpreadsheetApp.newDataValidation().requireValueInList(primaryList).build();
    targetRange.setDataValidation(rule);
  });
}

// 编辑时自动生成对应二级下拉
function onEdit(e) {
  if (!e) throw new Error("onEdit为表格自动触发函数，请勿手动运行");
  const ss = e.source;
  const activeCell = e.range;
  const activeSheet = activeCell.getSheet();
  const activeRow = activeCell.getRow();
  const activeCol = activeCell.getColumn();

  // 匹配当前编辑单元格对应的配置规则
  const matchConfig = DROPDOWN_CONFIG.find(config => {
    return config.targetSheetName === activeSheet.getName()
      && config.targetPrimaryCol === activeCol
      && activeRow >= config.targetDropStartRow
      && activeRow <= config.targetDropEndRow;
  });
  // 不在配置的下拉编辑范围内，直接退出不处理
  if (!matchConfig) return;

  // 读取对应配置的数据源
  const sourceSheet = ss.getSheetByName(matchConfig.sourceSheetName);
  const sourceData = sourceSheet
    .getRange(
      matchConfig.sourceDataStartRow,
      matchConfig.sourcePrimaryCol,
      matchConfig.sourceDataEndRow - matchConfig.sourceDataStartRow + 1,
      2
    )
    .getValues();
  // 过滤匹配当前一级选中值的二级选项
  const selectedPrimary = activeCell.getValue();
  const secondaryList = sourceData
    .filter(row => row[0] === selectedPrimary)
    .map(row => row[1])
    .filter(item => item !== "");
  // 给同行二级下拉单元格设置验证规则
  const secondaryCell = activeSheet.getRange(activeRow, matchConfig.targetPrimaryCol + 1);
  const rule = SpreadsheetApp.newDataValidation().requireValueInList(secondaryList).build();
  secondaryCell.setDataValidation(rule);
  // 切换一级选项时清空原有二级内容，避免无效值残留
  secondaryCell.clearContent();
}

使用说明

首次使用先修改DROPDOWN_CONFIG里的各项参数，对应表格中Options表的数据源列位置、每个目标工作表的下拉放置位置
参数修改完成后，手动运行一次createAllPrimaryDropdowns函数，授权后会自动给所有配置好的工作表批量生成一级下拉
后续新增SheetD、SheetE等工作表的下拉需求，不需要复制任何函数，只需要在DROPDOWN_CONFIG数组里追加一个新的配置对象，填好对应参数，再重新运行一次createAllPrimaryDropdowns即可
编辑一级下拉单元格时，脚本会自动识别当前所在工作表，匹配对应数据源生成二级下拉
如果二级下拉需要放在一级下拉的非相邻列，只需要在配置项里新增targetSecondaryCol字段，把onEdit里取二级单元格的列号改成对应配置值即可