Google Sheets多工作表共用级联下拉菜单Appscript适配问题

你的代码目前仅绑定了SheetOne作为唯一操作工作表，所有编辑判断、单元格操作都只作用在SheetOne上，自然不会响应SheetTwo的编辑事件。

// 这里配置所有需要实现联动效果的工作表，后续新增直接往数组里加即可
var allowedSheets = ["SheetOne", "SheetTwo"];
var dropWsList = "MainSheet";
var date = 1;
var clientName  = 2;
var projectID = 3;
var taskName = 4;
var taskDescription = 5;
var deliveryDate = 6;
var duration = 7;

// 数据源固定取MainSheet，这部分不用改
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropWsList);
var options = wsOptions.getRange(3, 1, wsOptions.getLastRow()-1,4).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(allowedSheets.includes(wsName) && r > 2){
    if(c === clientName){
      firstLevelValidation(val, r, currentWs);
    } else if(c === projectID){
      secondLevelValidation(val, r, currentWs);             
    } else if(c === taskName){
      thridLevelValidation(val, r, currentWs);
    }
  }
}

function firstLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, projectID).clearContent();
    currentWs.getRange(r, projectID).clearDataValidations();
    currentWs.getRange(r, taskName).clearContent();
    currentWs.getRange(r, taskName).clearDataValidations();  
    currentWs.getRange(r, taskDescription).clearContent();
    currentWs.getRange(r, taskDescription).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
    currentWs.getRange(r, date).clearContent();
  } else {
    currentWs.getRange(r, projectID).clearContent();
    currentWs.getRange(r, projectID).clearDataValidations();
    currentWs.getRange(r, taskName).clearContent();
    currentWs.getRange(r, taskName).clearDataValidations();  
    currentWs.getRange(r, taskDescription).clearContent();
    currentWs.getRange(r, taskDescription).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
    currentWs.getRange(r, date).clearContent();

    var filteredOptions = options.filter(function(o){ return o[0] == val });
    var listToApply = filteredOptions.map(function(o){ return o[1] });
    var cell = currentWs.getRange(r, projectID); 
    applyValidationToCell(listToApply,cell);
  }
}

function secondLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, taskName).clearContent();
    currentWs.getRange(r, taskName).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
  } else {
    currentWs.getRange(r, taskName).clearContent();
    currentWs.getRange(r, taskName).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
    var thirdlevelColValue = currentWs.getRange(r, clientName).getValue();
    var filteredOptions = options.filter(function(o){ return o[0] == thirdlevelColValue && o[1] === val });
    var listToApply = filteredOptions.map(function(o){ return o[2] });
    var cell = currentWs.getRange(r, taskName); 
    applyValidationToCell(listToApply,cell);
  } 
}

function thridLevelValidation(val, r, currentWs){
  if(val === ""){
    currentWs.getRange(r, taskDescription).clearContent();
    currentWs.getRange(r, taskDescription).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
  } else {
    currentWs.getRange(r, taskDescription).clearContent();
    currentWs.getRange(r, taskDescription).clearDataValidations();
    currentWs.getRange(r, duration).clearContent();
    var fourthlevelColValue = currentWs.getRange(r, clientName).getValue();
    var filteredOptions = options.filter(function(o){ return o[0] == fourthlevelColValue && o[2] === val });
    var listToApply = filteredOptions.map(function(o){ return o[3] });
    // 原代码漏了给任务描述列加验证，这里按逻辑补上，不需要可以删掉以下两行
    var cell = currentWs.getRange(r, taskDescription); 
    applyValidationToCell(listToApply,cell);
  } 
}

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

• 将原来固定的单个工作表配置改成了可配置的工作表数组，后续需要新增其他工作表直接往allowedSheets数组里加工作表名称即可
• 取消了全局绑定单个工作表的逻辑，每次编辑时动态获取当前操作的工作表实例，所有单元格操作都基于当前工作表执行，避免跨表操作错误
• 优化了编辑事件的判断逻辑，统一判断是否为允许的工作表、是否为有效行，再判断列触发对应层级的联动

请确保SheetTwo的列布局和SheetOne完全一致，即客户名列在第2列、项目ID在第3列，以此类推。如果列布局不同，修改代码开头的列号变量即可。

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