Google Sheets多级下拉验证脚本适配多月份工作表咨询

核心修改思路 #

• 移除原代码中固定绑定「APRIL 21」工作表的全局ws变量，改为每次编辑时从触发事件中动态获取当前操作的工作表
• 新增月份工作表白名单数组，后续新增月份仅需在数组中添加对应表名即可，无需调整其他逻辑
• 删除原代码中重复定义的applysecondlevelvalidation函数，避免执行冲突
• 将原逻辑中所有对固定工作表的操作，替换为对当前编辑工作表的操作

修改后完整代码 #

// 所有需要生效的月份工作表名称，新增月份直接往该数组中添加对应表名即可
const ALLOWED_MONTH_SHEETS = ["APRIL 21", "MAY 21", "JUNE 21"];
// 固定配置项，无需修改
const OPTION_WS_NAME = "DataBase1";
const FIRST_LEVEL_COL = 4;
const SECOND_LEVEL_COL = 5;
const THIRD_LEVEL_COL = 6;

// 全局仅保留公共的数据库配置和选项数据
const wsDatabase = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(OPTION_WS_NAME);
const options = wsDatabase.getRange(2, 1, wsDatabase.getLastRow() - 1, 3).getValues();

function onEdit(e) {
  const activeCell = e.range;
  const currentWorksheet = activeCell.getSheet();
  const worksheetName = currentWorksheet.getName();

  // 非白名单内的工作表直接跳过逻辑
  if (!ALLOWED_MONTH_SHEETS.includes(worksheetName)) return;

  const editValue = activeCell.getValue();
  const editRow = activeCell.getRow();
  const editCol = activeCell.getColumn();

  if (editCol === FIRST_LEVEL_COL && editRow > 20) { 
    applyFirstLevelValidation(editValue, editRow, currentWorksheet);
  } else if (editCol === SECOND_LEVEL_COL && editRow > 20) {
    applySecondLevelValidation(editValue, editRow, currentWorksheet);
  }
}

function applyFirstLevelValidation(val, row, currentWorksheet) {
  if (val === "") {
    currentWorksheet.getRange(row, SECOND_LEVEL_COL).clearContent();
    currentWorksheet.getRange(row, SECOND_LEVEL_COL).clearDataValidations();
  } else {
    currentWorksheet.getRange(row, SECOND_LEVEL_COL).clearContent();
    const filteredOptions = options.filter(o => o[0] === val);
    const listToApply = filteredOptions.map(o => o[1]);
    const targetCell = currentWorksheet.getRange(row, SECOND_LEVEL_COL);
    applyValidationToCell(listToApply, targetCell);
  }
} 

function applySecondLevelValidation(val, row, currentWorksheet) {
  if (val === "") {
    currentWorksheet.getRange(row, THIRD_LEVEL_COL).clearContent();
    currentWorksheet.getRange(row, THIRD_LEVEL_COL).clearDataValidations();
  } else {
    currentWorksheet.getRange(row, THIRD_LEVEL_COL).clearContent();
    const firstLevelValue = currentWorksheet.getRange(row, FIRST_LEVEL_COL).getValue();
    const filteredOptions = options.filter(o => o[0] === firstLevelValue && o[1] === val);
    const listToApply = filteredOptions.map(o => o[2]);
    const targetCell = currentWorksheet.getRange(row, THIRD_LEVEL_COL);
    applyValidationToCell(listToApply, targetCell);
  }
} 

function applyValidationToCell(list, cell) {
  const rule = SpreadsheetApp
    .newDataValidation()
    .requireValueInList(list)
    .setAllowInvalid(false)
    .build();
  cell.setDataValidation(rule);
}

后续新增月份操作方式 #

新增对应月份的工作表后，仅需要把工作表的准确名称添加到代码最上方ALLOWED_MONTH_SHEETS数组的括号内即可，例如新增7月工作表就添加"JULY 21"，下拉验证逻辑会自动适配新工作表。

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