您如何在 Google 表格中进行动态/相关下拉列表? [英] How do you do dynamic / dependent drop downs in Google Sheets?

问题描述

如何根据在 google 表格中的主类别下拉列表中选择的值获取子类别列以填充下拉列表?

How do you get a sub-category column to populate a drop down based on the value selected in the main category drop down in google sheets?

我用谷歌搜索并找不到任何好的解决方案，因此我想分享我自己的解决方案.请看下面我的回答.

I googled around and couldn't find any good solutions, therefore I wanted to share my own. Please see my answer below.

推荐答案

您可以从设置了主页的 google sheet 开始，然后下拉源页面，如下所示.

You can start with a google sheet set up with a main page and drop down source page like shown below.

您可以通过正常的数据">验证"菜单提示设置第一列下拉菜单.

You can set up the first column drop down through the normal Data > Validations menu prompts.

主页

下拉源页面

之后，您需要设置一个名称为 onEdit 的脚本.(如果您不使用该名称，则 getActiveRange() 只会返回单元格 A1)

After that, you need to set up a script with the name onEdit. (If you don't use that name, the getActiveRange() will do nothing but return cell A1)

并使用此处提供的代码:

And use the code provided here:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = SpreadsheetApp.getActiveSheet();
  var myRange = SpreadsheetApp.getActiveRange();
  var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categories");
  var option = new Array();
  var startCol = 0;

  if(sheet.getName() == "Front Page" && myRange.getColumn() == 1 && myRange.getRow() > 1){
    if(myRange.getValue() == "Category 1"){
      startCol = 1;
    } else if(myRange.getValue() == "Category 2"){
      startCol = 2;
    } else if(myRange.getValue() == "Category 3"){
      startCol = 3;
    } else if(myRange.getValue() == "Category 4"){
      startCol = 4;
    } else {
      startCol = 10
    }

  if(startCol > 0 && startCol < 10){
    option = dvSheet.getSheetValues(3,startCol,10,1);
    var dv = SpreadsheetApp.newDataValidation();
    dv.setAllowInvalid(false);  
    //dv.setHelpText("Some help text here");
    dv.requireValueInList(option, true);
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
   }

  if(startCol == 10){
    sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
  } 
  }
}

之后，通过转到编辑">当前项目触发器"在脚本编辑器屏幕中设置触发器.这将打开一个窗口，让您选择各种下拉菜单，最终结果如下:

After that, set up a trigger in the script editor screen by going to Edit > Current Project Triggers. This will bring up a window to have you select various drop downs to eventually end up at this:

你应该去追求那个！

这篇关于您如何在 Google 表格中进行动态/相关下拉列表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！