Google 表格中的多个相关下拉列表

山泉

我发现这个代码片段从某些列中读取数据，然后自动从该数据中创建相关的下拉列表。

在我的示例中，它在 SheetB 上创建了相关的下拉列表，并且正在为此目的工作。

//CREATE PRIMARY DROPDOWN LIST
function createPrimaryDrpdwon() {
/* SET FOLLOWING VARIABLES */

var dataSS           = "Options";         //Name of the sheet that contain data for dropdown lists
var dropSS           = "SheetB";      //Name of the sheet which dropdown list to be created
var primaryDataRange = "N7:N500";       //Data range for primary dropdown
var primaryDropRange = "C2:C500";       //Range which primary dropdown set

var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);
var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();

primaryDropRange.setDataValidation(validationRule);
}


//CREATE SECONDARY DROPDOWN LIST
function onEdit(){
/* SET FOLLOWING VARIABLES */
var dataSS       = "Options";         //Name of the sheet that contain data for dropdown lists
var dropSS       = "SheetB";      //Name of the sheet which dropdown list to be created
var allDataRange = "N7:O500";       //Data range for dropdown list (both primary and dependent)
var primaryDDCol = 3;               //Column number of the primary drop down

var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
var dropDData    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(allDataRange).getValues();
var activeCell   = dropSS_.getActiveCell();
var activeColumn = activeCell.getColumn();
var activeRow    = activeCell.getRow();

if(activeColumn==primaryDDCol){
    var dep_Col         = primaryDDCol+1;
    var dep_Row         = activeRow;
    var depCell         = dropSS_.getRange(dep_Row, dep_Col);
    var primarySelected = activeCell.getValue();
    var validationRule  = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData,primarySelected)).build();
    
    depCell.setDataValidation(validationRule);
}
}

function getDependentList(dropDData,primarySelected){
var dependenList = [];
var j = 0;
if(dropDData != null){
    for(i=0; i<dropDData.length; i++){
    if(dropDData[i][0]==primarySelected){
        dependenList[j] = dropDData[i][1];
        j++;
    }
    }
}
return dependenList;
}

但是，如果我想要另一个下拉列表来读取选项表上不同列的数据并在 SheetC 上创建相关下拉列表，并且如果我想在未来拥有更多这样的下拉列表，该怎么办？

我尝试复制/粘贴和重命名所有变量并在第一个副本中更改输入和输出数据，但我无法使其工作。

编辑：用我想要实现的图像示例来澄清：

工作表选项 - 将显示在工作表 B 上的第一个相关下拉列表的数据范围

SheetB - 脚本生成的第一个依赖下拉列表

脚本当前如何在 SheetB 上运行的简短 Gif

工作表选项 - 将在 SheetC 上显示的第二个相关下拉列表的数据范围

SheetC - 脚本生成的第二个相关下拉列表

脚本目前在 SheetB 上生成下拉列表，但我在 SheetC 上需要同样的东西，可能是 SheetD、E 等:)

Tanaike

我相信你的目标如下。

运行函数时，您希望将下拉列表放入“SheetB”和“SheetC”中的“C”列createPrimaryDrpdwon()。
当更改“C”列的下拉列表时，您希望通过“C”列的值将下拉列表设置为“D”列。

在这种情况下，如何进行以下修改？

示例脚本：

function createPrimaryDrpdwon() {
  var dataSS = "Options";

  // Please set the sheet names and ranges.
  var dropSSs = [
    { dropSS: "SheetB", primaryDataRange: "N7:N500" },
    { dropSS: "SheetC", primaryDataRange: "Q7:Q500" },
  ];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  dropSSs.forEach(({ dropSS, primaryDataRange }) => {
    var primaryDropRange = "C2:C20";
    var primaryDropList = ss.getSheetByName(dataSS).getRange(primaryDataRange).getValues();
    var primaryDropRange = ss.getSheetByName(dropSS).getRange(primaryDropRange);
    var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
    primaryDropRange.setDataValidation(validationRule);
  });
}

function onEdit(e) {
  // Please set the sheet names and ranges.
  var dropSSs = { "SheetB": "N7:O500", "SheetC": "Q7:R500" };

  const ss = e.source;
  const range = e.range;
  const sheet = range.getSheet();
  const sheetName = sheet.getSheetName();
  if (!Object.keys(dropSSs).includes(sheetName) || range.columnStart != 3 || range.rowStart == 1) return;

  // I modified your onEdit function a little as follows.
  var dataSS = "Options";
  var primaryDDCol = 3;
  var dropSS_ = sheet;
  var dropDData = ss.getSheetByName(dataSS).getRange(dropSSs[sheetName]).getValues();
  var activeCell = range;
  var activeColumn = activeCell.getColumn();
  var activeRow = activeCell.getRow();
  if (activeColumn == primaryDDCol) {
    var dep_Col = primaryDDCol + 1;
    var dep_Row = activeRow;
    var depCell = dropSS_.getRange(dep_Row, dep_Col);
    var primarySelected = activeCell.getValue();
    var validationRule = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData, primarySelected)).build();
    depCell.setDataValidation(validationRule);
  }
}

function getDependentList(dropDData, primarySelected) {
  var dependenList = [];
  var j = 0;
  if (dropDData != null) {
    for (i = 0; i < dropDData.length; i++) {
      if (dropDData[i][0] == primarySelected) {
        dependenList[j] = dropDData[i][1];
        j++;
      }
    }
  }
  return dependenList;
}