Google表格-多个从属下拉列表 [英] Google Sheets - Multiple dependent drop-down lists

问题描述

我正在尝试创建一个描述并通过脚本回答的从属列表在这里.

我想要实现的是，如果从第1列的单元格中选择某个值(例如第一个")，则同一行中下一个单元格的下拉选项应在该列中提供一定范围的值在与第一个-左-单元格中的值具有相同标题的不同工作表中(即，第一工作表称为选择器"-其中存在下拉菜单，在第二工作表中称为"KAT")中有这些下拉菜单的选项).然后，应根据行中每个第一个单元格的值，对每行进行这种设置.

我试图使用和修改建议的脚本，并查看了本文中的示例文件，但显然我对脚本缺乏基本的了解，因此无法正确地修改和实现它.

有人可以帮助我使此动态下拉菜单正常工作吗?

只是为了阐明我的最终意图:我想让此脚本首先运行，以便能够在多个文件上使用它.不过，我的最终目标是制作自动填充的下拉列表和选择器，以便我可以简单地在选择器"表中填写数据，然后稍后可以在下面的单元格中选择这些相同的值(取决于行中第一个单元格的名称(值)=保存验证范围的列的第一个单元格).我希望能够通过使用数据透视表或"KAT"工作表中的任何其他公式来实现此目的，这些表格将汇总我从选择器"工作表中获得的数据并将其作为下拉选项进行反馈...).

谢谢您的帮助.

在此处查看示例表

我使用的代码(如上所述):

 功能onEdit(){var ss = SpreadsheetApp.getActiveSpreadsheet()，工作表= ss.getActiveSheet()，名称= sheet.getName();如果(name！='Selector')返回;var range = sheet.getActiveRange()，col = range.getColumn();如果(col！= 1)返回;var val = range.getValue()，dv = ss.getSheetByName('KAT')，数据= dv.getDataRange().getValues()，catCol = data [0] .indexOf(val)，列表= [];Logger.log(catCol)for(var i = 1，len = 100; i< len; i ++)//问题在这里，列表中有太多项目！不能有更多500个项目需要验证list.push(data [i] [catCol]);var listRange = dv.getRange(2，catCol + 1，dv.getLastRow()-1，1)Logger.log(清单)var cell = sheet.getRange(range.getRow()，col-1)var rule = SpreadsheetApp.newDataValidation().requireValueInRange(listRange)//使用requireValueIn Range来解决问题.建造();cell.setDataValidation(rule);Logger.log(cell.getRow())} 

解决方案

此问题涉及动态下拉列表.关于StackOverflow的先前问题和解答( Google表格-依赖下拉列表)被引用，并且该答案中的代码未成功重新利用.

问题中的代码由于以下原因而无法正常工作:第20行

  var单元格= sheet.getRange(range.getRow()，col-1) 

在所引用的代码中，下拉列表从F列开始(col = 6).从属下拉列表的范围在左侧，因此从属列的定义为"col-1".在发问者的场景中，下拉列表从A列(col = 1)开始，从属下拉列表的范围从左到右.但是，未更改此代码行以考虑不同的布局.而不是"col-1"，它应该是"col + 1".

其他事项

• 除此之外，第16行和第17行执行循环以创建可用于从属下拉列表的数组.但是，该循环是多余的，因为下拉列表是通过在"KAT"工作表上创建和分配范围来实际定义的.

• KAT的单元格A2包含一个公式:

  = sort(唯一(选择器！$ A $ 2:$ A)，1，true)

  这似乎很有用，因为它会自动将在选择器"中输入的所有新下拉值添加到KAT的值列表中.实际上，这是没有用的，因为代码的依赖下拉列表构建在垂直而非水平上均有效.因此，添加到KAT的另一行本身并不会有助于建立从属下拉列表.

以下代码可用于构建从属下拉列表.我故意在代码中留下了许多记录器"条目，以帮助发问者理解代码的工作原理.

  function onEdit(){var ss = SpreadsheetApp.getActiveSpreadsheet();var sheet = ss.getActiveSheet();var name = sheet.getName();如果(name！='Selector')返回;var range = sheet.getActiveRange();var col = range.getColumn();var dropdownrow = range.getRow();//添加用于调试和信息如果(col！= 1)返回;var val = range.getValue();Logger.log(光标在单元格中的选择器"中=" + range.getA1Notation());//调试Logger.log(那是行" + dropdownrow +和列" + col +.选择的值=" + val);//调试var dv = ss.getSheetByName('KAT');var data = dv.getDataRange().getValues();var catCol = data [0] .indexOf(val);var list = [];var KAT_data = dv.getDataRange();var KAT_data_len = KAT_data.getLastRow();//添加以为"for"循环提供合理的范围Logger.log("KAT上的数据范围是" + KAT_data.getA1Notation()+，最后一行数据=" + KAT_data_len))；//调试Logger.log("KAT数据='" +数据+'");//调试Logger.log(在KAT中找到下拉项单元格值'" + val +'作为项#" + catCol)；//调试for(var i = 1，len = KAT_data_len; i< len; i ++){//不相关，因为数据验证范围是通过在KAT上定义一个范围来获得的//问题在这里，A2中的唯一命令会创建一个空白行//Logger.log("i =" + i +，data =" + data [i] [catCol]);//调试list.push(data [i] [catCol]);}var listRange = dv.getRange(2，catCol + 1，dv.getLastRow()-1，1);Logger.log("FWIW，这是循环后的列表=" + list);//调试Logger.log(新数据验证范围的内容(取自KAT)为" + listRange.getA1Notation())；//调试Logger.log(将新的验证范围添加到col =" +(col + 1)));//调试//var cell = sheet.getRange(range.getRow()，col-1);//控制下一个验证范围.验证示例从右到左起作用，但是此工作表从左到右起作用.因此必须加1，不能减1.var cell = sheet.getRange(range.getRow()，col + 1);Logger.log(要分配新验证范围的单元格将是" + cell.getA1Notation());//调试var rule = SpreadsheetApp.newDataValidation().requireValueInRange(listRange).build();//建立验证规则cell.setDataValidation(rule);//将验证范围分配给新单元格} 

---

此代码值得吗?

所编写和引用的代码仅限于创建一级依赖下拉列表.就此而言，它的价值非常有限.有一种创建依赖下拉列表的不同方法是合理的.

"如何动态/自2014年以来，StackOverflow上的Google Sheets中的依赖下拉列表?"就一直是讨论和更新动态依赖下拉列表技术的聚会场所.最新更新于2018年2月由here.

I would like to achieve that if selecting a certain value (e.g. "First") from a cell in column 1, then the drop-down options from the next cell in the same row should offer a range of values from the column in a different sheet with the same heading as the value in the first - left - cell (i.e. the first sheet is called "Selector" - in which there are dropdowns, in the second sheet called "KAT" I have the options for these dropdowns). This should then be possible for every row depending on the value of each first cell of the row.

I have tried to use and adapt the suggested script and have reviewed the sample files in the article but I apparently lack some basic understanding of the script to be able to adapt and implement it properly.

Could anybody kindly help me with making this dynamic dropdown work properly?

Just to clarify my final intention: I would like to have this script working first to be able to use it on multiple files. My final goal, though, is to make self-filling dropdown lists and selectors, so that I could simply fill in the data in the "Selector" sheet and would then be able to select these same values later in the cells below (depending on the name (value) of the first cell in the row = first cell of the column holding validation range). I hope to be able to achieve this by using either Pivot table or any other formula in the "KAT" sheet that would aggregate my data from "Selector" sheet and feed them back as drop-down options ...).

Thank you for your help.

See the example sheet here

Code I used (as above):

function onEdit() 
    {
      var ss = SpreadsheetApp.getActiveSpreadsheet(),
          sheet = ss.getActiveSheet(),
          name = sheet.getName();
      if (name != 'Selector') return;
      var range = sheet.getActiveRange(),
          col = range.getColumn();
      if (col != 1) return;
      var val = range.getValue(),
          dv = ss.getSheetByName('KAT'),
          data = dv.getDataRange().getValues(),
          catCol = data[0].indexOf(val),
          list = [];
      Logger.log(catCol)
      for (var i = 1, len = 100; i < len; i++) // Problem is here, you have too many items in list! Cannot have more 500 items for validation
          list.push(data[i][catCol]);
      var listRange = dv.getRange(2,catCol +1,dv.getLastRow() - 1, 1)
      Logger.log(list)
      var cell = sheet.getRange(range.getRow(), col-1)
      var rule = SpreadsheetApp.newDataValidation()
      .requireValueInRange(listRange)   // Use requireValueIn Range instead to fix the problem
      .build();
      cell.setDataValidation(rule);
      Logger.log(cell.getRow())
    }

解决方案

This question deals with dynamic dropdown lists. A previous question and answer on StackOverflow (Google Sheets - Dependent drop-down lists) were referenced, and code from that answer was being unsuccessfully re-purposed.

The code in the question was not working for one reason: Line 20

var cell = sheet.getRange(range.getRow(), col-1)

In the referenced code, the dropdown list begins in Column F (col=6). The dependant dropdowns ranged to the left so the definition of the dependant column was "col-1". In the questioner's scenario, the dropdown list begins in Column A (col=1) and the dependant dropdowns range from left to right. However, this line of code was not changed to take into account the different layout. Rather than "col-1", it should be "col+1".

Other matters

• In addition to this, lines 16 and 17 perform a loop to create an array that might be used for the dependant dropdown. However the loop is redundant because the dropdown is actual defined by creating and assigning a range on the "KAT" sheet.

• Cell A2 of KAT includes a formula:

  =sort(unique(Selector!$A$2:$A),1,true)

  This may appear to be useful because it automatically adds any new dropdown value entered in "Selector" to a list of values in KAT. In reality it is unproductive, because the dependant dropdown build by the code works vertically rather than horizontally. So an additional row added to KAT does not, of itself, contribute to building the dependant dropdown.

The following code works to build the dependant drop down list. I have deliberately left a number of "Logger" entries in the code to assist the questioner in understanding how the code works.

function onEdit() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();
    var name = sheet.getName();

    if (name != 'Selector') return;
    var range = sheet.getActiveRange();
    var col = range.getColumn();
    var dropdownrow = range.getRow(); // added for debugging and informationm

    if (col != 1) return;

    var val = range.getValue();
    Logger.log("the cursor is in 'Selector' in cell = " + range.getA1Notation()); //DEBUG
    Logger.log("That's row " + dropdownrow + ", and column " + col + ". The value selected = " + val); // DEBUG



    var dv = ss.getSheetByName('KAT');
    var data = dv.getDataRange().getValues();
    var catCol = data[0].indexOf(val);
    var list = [];
    var KAT_data = dv.getDataRange();
    var KAT_data_len = KAT_data.getLastRow(); // added to give 'for' loop a sensible range

    Logger.log("The data range on KAT is " + KAT_data.getA1Notation() + ", and the last row of data = " + KAT_data_len); //DEBUG
    Logger.log("KAT data = '" + data + "'"); // DEBUG
    Logger.log("Found the dropdown cell value of '" + val + "' in KAT as item #" + catCol); //DEBUG

    for (var i = 1, len = KAT_data_len; i < len; i++) { // Irrelevant because the data validation range is obtained by defining a range on KAT

        // Problem is here, the unique command in A2 creates a blank row
        // Logger.log("i="+i+", data = "+data[i][catCol]); // DEBUG
        list.push(data[i][catCol]);

    }

    var listRange = dv.getRange(2, catCol + 1, dv.getLastRow() - 1, 1);
    Logger.log("FWIW, this is the list after the loop= " + list); // DEBUG
    Logger.log("The contents for the new data validation range (taken from KAT) is " + listRange.getA1Notation()); // DEBUG
    Logger.log("The new validation range gets added to col = " + (col + 1)); // DEBUG

    //var cell = sheet.getRange(range.getRow(), col-1); // governs the next validation range. Example validation worked right to left, but this sheet works left to right. So must ADD 1, not subtract 1.
    var cell = sheet.getRange(range.getRow(), col + 1);
    Logger.log("The cell to be assigned the new validation range will be " + cell.getA1Notation()); // DEBUG
    var rule = SpreadsheetApp.newDataValidation().requireValueInRange(listRange).build(); // Build validation rule
    cell.setDataValidation(rule); // assign validation range to new cell

}

---

Is this code worthwhile?

The code, as written and referenced, is limited to creating only one level of dependant dropdowns. To this extent it has very limited value. A different approach to creating dependant dropdowns is justified.

"How do you do dynamic / dependent drop downs in Google Sheets?" on StackOverflow has been a meeting place for discussing and updating techniques for dynamic dependant dropdowns since 2014. The latest update was in February 2018 by Max Makhrov. Thye code described here may be useful for the questioner.

这篇关于Google表格-多个从属下拉列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！