如何修改Google Apps Script使多级联动下拉菜单适配多工作表？

你需要对代码做以下4处关键修改，就能让联动下拉在三个目标工作表同时工作：

1. 把单个目标表名改成数组 #

原来只指定了一个工作表，现在改成包含三个目标表的数组：

// 替换原来的单个字符串定义
var targetSheets = ["Transactions 6080", "Transactions 6586", "Transactions 1002"];

2. 删除全局固定的ws变量 #

删掉这行固定指向单个工作表的代码：

// 移除这行代码
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(transactionsWsName);

3. 修改onEdit的判断逻辑，传递当前工作表对象 #

更新判断条件，检查当前编辑的表是否在目标数组中，同时调用验证函数时传入当前工作表：

function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var currentWs = activeCell.getSheet();
  var wsName = currentWs.getName();
  
  // 检查当前表是否在目标数组中
  if(targetSheets.includes(wsName) && c === firstLevelColumn && r > 5){
    // 传入当前工作表对象
    applyFirstLevelValidation(val, r, currentWs);   
  } else if(targetSheets.includes(wsName) && c === secondLevelColumn && r > 5){
    // 传入当前工作表对象
    applySecondLevelValidation(val, r, currentWs);    
  }
}

4. 更新验证函数，使用传入的当前工作表 #

修改两个验证函数的参数，接收当前工作表对象，并替换所有原来的ws引用：

修改applyFirstLevelValidation #

function applyFirstLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, secondLevelColumn).clearContent();
    currentWs.getRange(r, secondLevelColumn).clearDataValidations();
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    currentWs.getRange(r, secondLevelColumn).clearContent();
    currentWs.getRange(r, secondLevelColumn).clearDataValidations();
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
    var filteredCategories = categories.filter(function(o){ return o[0] === val });
    var listToApply = filteredCategories.map(function(o){ return o[1] });
    var cell = currentWs.getRange(r, secondLevelColumn);
    applyValidationToCell(listToApply,cell);
  }   
}

修改applySecondLevelValidation #

function applySecondLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    // 用当前工作表获取第一级的值
    var firstLevelColValue = currentWs.getRange(r, firstLevelColumn).getValue();
    var filteredCategories = categories.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
    var listToApply = filteredCategories.map(function(o){ return o[2] });
    var cell = currentWs.getRange(r, thirdLevelColumn);
    applyValidationToCell(listToApply,cell);
  }   
}

修改后的完整代码 #

var targetSheets = ["Transactions 6080", "Transactions 6586", "Transactions 1002"];
var categoriesWsName = "Categories";
var firstLevelColumn = 6;
var secondLevelColumn = 7;
var thirdLevelColumn = 8;

var wsCategories = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(categoriesWsName);
var categories = wsCategories.getRange(6, 6,wsCategories.getLastRow()-5, 3).getValues();

function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var currentWs = activeCell.getSheet();
  var wsName = currentWs.getName();
  
  if(targetSheets.includes(wsName) && c === firstLevelColumn && r > 5){
    applyFirstLevelValidation(val, r, currentWs);   
  } else if(targetSheets.includes(wsName) && c === secondLevelColumn && r > 5){
    applySecondLevelValidation(val, r, currentWs);    
  }
}

function applyFirstLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, secondLevelColumn).clearContent();
    currentWs.getRange(r, secondLevelColumn).clearDataValidations();
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    currentWs.getRange(r, secondLevelColumn).clearContent();
    currentWs.getRange(r, secondLevelColumn).clearDataValidations();
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
    var filteredCategories = categories.filter(function(o){ return o[0] === val });
    var listToApply = filteredCategories.map(function(o){ return o[1] });
    var cell = currentWs.getRange(r, secondLevelColumn);
    applyValidationToCell(listToApply,cell);
  }   
}

function applySecondLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    currentWs.getRange(r, thirdLevelColumn).clearDataValidations();
  } else {
    currentWs.getRange(r, thirdLevelColumn).clearContent();
    var firstLevelColValue = currentWs.getRange(r, firstLevelColumn).getValue();
    var filteredCategories = categories.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });
    var listToApply = filteredCategories.map(function(o){ return o[2] });
    var cell = currentWs.getRange(r, thirdLevelColumn);
    applyValidationToCell(listToApply,cell);
  }   
}

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

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