谷歌工作表相关下拉脚本查询

Question

我试图在我的工作表中创建一个依赖的下拉列表，在Col A中，它将允许您选择区域列表（例如。美洲、非洲、亚洲）等，然后在Col B中显示在Col A中选择的该地区国家的列表。

我尝试使用=间接和=索引/匹配公式，但它们对我超过1000行的工作表不起作用。

我的工作簿中有11个选项卡，但是我只想将数据从“国家”选项卡拉到“主呼叫表”选项卡进行验证。所有其他选项卡都是从主工作表中删除的。

在countries（国家）选项卡中，我列出了第2行A栏中的地区以及B栏中的相应国家，因此如下所示：

非洲加纳，亚洲。日本

等等。

在我的主调用选项卡中，我在顶部有切片器等，所以数据输入只从第9行开始。

我尝试了下面的脚本，但没有成功：

var mainWsName = "Master Calls Sheet";
var optionsWsName = "countries";
var firstLevelColumn = 1;
var secondLevelColumn = 2;

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("mainWsName");
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("optionsWsName");
var options = wsOptions.getRange(2, 1,wsOptions.getLastRow()-1,2).getValues();

function myFunction() {

  function onEdit(e){
    var activeCell = e.range;
    var val = activeCell.getValue();
    var r = activeCell.getRow();
    var c = activeCell.getColumn();
    var wsName = activeCell.getSheet().getName();
    if(wsName == "mainWsName" && c === firstLevelColumn && r > 8){
       var filteredOptions = options.filter(function(o){ return o[0] === val });
       var listToApply = filteredOptions.map(function(o){ return o[1] });
    var cell = ws.getRange(r, 2);
    applyValidationToCell(listToApply,cell)
  }

  }}

function applyValidationToCell(list,cell){

  var rule = SpreadsheetApp
  .newDataValidation()
  .requireValueInList(list)
  .setAllowInvalid(false)
  .build;

  cell.setDataValidation(rule)

}

有人能帮忙吗？！？

Answer 1

您可以使用以下由两个函数组成的脚本来实现所需的功能：createDropdown（）和onEditTrigger（e）。

• 全局变量：

var ss = SpreadsheetApp.getActiveSpreadsheet()
var master = ss.getSheetByName("Master Calls Sheet");
var countries = ss.getSheetByName("countries");
var options = countries.getRange(2, 1,countries.getLastRow()-1,2).getValues();
var continentsList = ['Americas','Africa','Asia']; 

如果要将其他大陆添加到函数中，则只需更改var colontslist变量。

• createDropdown（）函数：

function createDropdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var rangeRule = SpreadsheetApp.newDataValidation().requireValueInList(continentsList,true).build();
  ss.getRange('A9').setDataValidation(rangeRule);
}

上述功能用于创建包含所有大陆的下拉列表，并将在主调用表的A9单元格上创建该下拉列表。

• onEditTrigger（e）函数：

function onEditTrigger(e) {
  var countries = e.range.getSheet();
  var cellValue = e.range.getValue();

  var k =0;

  for (k=0; k<continentsList.length;k++) {

    if (cellValue == continentsList[k]) {
      countries.getRange("B9:B").clear();
      var j=0;
      for (var i=0; i<options.length; i++) 
        if (options[i][0]== continentsList[k]) {
          j++;
          countries.getRange(j+8,2).setValue(options[i][1]);
      }
    }
  }
}

上面的函数是一个可安装的触发器，这意味着每次下拉列表中的值被更改时，它都会触发。它的工作原理是循环遍历ContinentsList，并检查每次迭代的值是否与从下拉列表中选择的值相对应。如果值对应，则列出每个大陆的国家。

这些值将出现在B列上，从主调用表上的B9开始。

应使用以下属性设置触发器：

此外，我建议您查看以下链接，因为它们可能对您未来的发展有所帮助：

>

数据验证类。