如何修改Google Script使其适用于除指定工作表外的所有表

修改说明 #

核心调整逻辑如下：

• 新增排除工作表配置数组，所有填入该数组的工作表都不会触发多级验证规则
• 移除原脚本中固定绑定Template工作表的全局声明，改为动态获取当前正在编辑的工作表
• 调整两级验证函数的入参，支持传入当前操作的工作表实例，避免跨表误操作

完整修改后代码 #

var MenuWSname = "Master Menu"
var wsMenu = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(MenuWSname);
var Menu = wsMenu.getRange(2,1,wsMenu.getLastRow()-1,3).getValues();
var firstColumn = 8;
var secondColumn = 9;
var thirdColumn = 10;
// 在这里填入不需要生效的工作表名，多个用英文逗号分隔
var excludes = ["Sheet1", "Sheet2", "Master Menu"];

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 (excludes.indexOf(wsName) != -1) return;

  if(c === firstColumn && r > 6){ 
    applyFirstLevelValidation(val,r,currentWs)
  } else if(c === secondColumn && r > 6){
    applySecondLevelValidation(val,r,currentWs);
  }
}

function applyFirstLevelValidation(val,r,currentWs){
    if(val === ""){
      currentWs.getRange(r,secondColumn).clearContent();
      currentWs.getRange(r,secondColumn).clearDataValidations();
      currentWs.getRange(r,thirdColumn).clearContent();
      currentWs.getRange(r,thirdColumn).clearDataValidations();
    }  else {
      currentWs.getRange(r,secondColumn).clearContent();
      currentWs.getRange(r,secondColumn).clearDataValidations();
      currentWs.getRange(r,thirdColumn).clearContent();
      currentWs.getRange(r,thirdColumn).clearDataValidations();
      var filteredOptions = Menu.filter(function(o){return o[0] === val });
      var filteredpiata = filteredOptions.map(function(o){return o[1] } );
      var cell = currentWs.getRange(r,secondColumn);
      applyValidationToCell(filteredpiata,cell);
    }
}

function applySecondLevelValidation(val,r,currentWs){
    if(val === ""){
      currentWs.getRange(r,thirdColumn).clearContent();
      currentWs.getRange(r,thirdColumn).clearDataValidations();
    }  else {
      currentWs.getRange(r,thirdColumn).clearContent();
      var firstLevelColValue = currentWs.getRange(r, firstColumn).getValue();
      var filteredOptions = Menu.filter(function(o){return o[0] === firstLevelColValue && o[1] === val });
      var filteredpiata = filteredOptions.map(function(o){return o[2] } );
      var cell = currentWs.getRange(r,thirdColumn);
      applyValidationToCell(filteredpiata,cell);
    }
}

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

后续调整说明 #

如果后续需要新增排除的工作表，直接往excludes数组中添加对应工作表的全称即可，注意名称需要和工作表标签上的名称完全一致，区分大小写。

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