如何让Google Sheets联动下拉菜单脚本适配多工作表？

原脚本仅针对Twitter工作表生效，要扩展到Facebook、Instagram、LinkedIn，需要做以下几处关键修改：

• 定义目标工作表列表，替代硬编码的单个表名
• 移除全局固定的工作表对象，改为动态获取当前编辑的工作表
• 调整验证函数，接收当前工作表作为参数，避免依赖全局变量

修改后的完整代码如下：

const TARGET_SHEETS = ["Twitter", "Facebook", "Instagram", "LinkedIn"];
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Tags");
var options = wsOptions.getRange(2, 1, wsOptions.getLastRow()-1,4).getValues();
var firstLevelColumn = 5;
var secondLevelColumn = 6;
var thirdLevelColumn = 7;
var fourthLevelColumn = 8;

function onEdit(e){
  var activeCell = e.range;
  var val = activeCell.getValue();
  var r = activeCell.getRow();
  var c = activeCell.getColumn();
  var wsName = activeCell.getSheet().getName();
  var currentWs = activeCell.getSheet();

  if(TARGET_SHEETS.includes(wsName) && c === firstLevelColumn && r > 1){
    applyfirstLevelValidation(val, r, currentWs);
  } else if (TARGET_SHEETS.includes(wsName) && c === secondLevelColumn && r > 1){
    applySecondLevelValidation(val, r, currentWs);
  } else if (TARGET_SHEETS.includes(wsName) && c === thirdLevelColumn && r > 1){
    applyThirdLevelValidation(val, r, currentWs);
  }
} //end onEdit

function applyfirstLevelValidation(val, r, ws){
    if(val === ""){
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations();
      ws.getRange(r, fourthLevelColumn).clearContent();
      ws.getRange(r, fourthLevelColumn).clearDataValidations();
    } else {
      ws.getRange(r, secondLevelColumn).clearContent();
      ws.getRange(r, secondLevelColumn).clearDataValidations();
      ws.getRange(r, thirdLevelColumn).clearContent();
      ws.getRange(r, thirdLevelColumn).clearDataValidations();
      ws.getRange(r, fourthLevelColumn).clearContent();
      ws.getRange(r, fourthLevelColumn).clearDataValidations();
      var filteredOptions = options.filter(function(t){ return t[0] === val });
      var listToApply = filteredOptions.map(function(t){ return t[1] });
      var cell = ws.getRange(r, secondLevelColumn);
      applyValidationToCell(listToApply,cell);
    } 
}

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

function applyThirdLevelValidation(val, r, ws){
    if(val === ""){
      ws.getRange(r, fourthLevelColumn).clearContent();
      ws.getRange(r, fourthLevelColumn).clearDataValidations();
    } else {
      ws.getRange(r, fourthLevelColumn).clearContent();
      var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();
      var secondLevelColValue = ws.getRange(r, secondLevelColumn).getValue();
      var filteredOptions = options.filter(function(t){ return t[0] === firstLevelColValue && t[1] === secondLevelColValue && t[2] === val });
      var listToApply = filteredOptions.map(function(t){ return t[3] });
      var cell = ws.getRange(r, fourthLevelColumn);
      applyValidationToCell(listToApply,cell);
    } 
}

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

  cell.setDataValidation(rule);
}

修改说明： #

1. 目标工作表列表：用TARGET_SHEETS数组统一管理需要生效的工作表名称，后续新增或修改只需调整这个数组即可。
2. 动态获取工作表：在onEdit函数中通过activeCell.getSheet()拿到当前编辑的工作表对象，传给各个验证函数。
3. 验证函数适配：每个验证函数新增ws参数，替换原来的全局固定工作表对象，确保操作的是当前编辑的工作表。

内容的提问来源于stack exchange，提问作者Chow A.