Google Sheets - Dependent Drop Down Script Query

Question

I am trying to create a dependent drop down in my sheet that in Col A will allow you to select a list of regions (e.g. Americas, Africa, Asia) etc and then in Col B will show a list of the countries in the region selected in Col A.

I have tried using an =indirect and =index/match formula but they do not work for the totality of my sheet, which is over 1000 rows.

There are 11 tabs in my workbook, however I only want to pull data from the 'countries' tab in to the 'Master Calls Sheet' tab for validation. All the other tabs are =filter cuts from the main sheet.

In the countries tab, I have listed the regions in Col A from Row 2, with the corresponding countries in Col B, so it reads like:

Africa . Ghana Asia . Japan

and so on.

In my Master Calls tab, I have slicers etc at the top, so the data entry only starts from row 9.

I have attempted the script below, to no avail:

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)

}

Can anyone help?!?

Answer 1

What you want can be achieved by using the following script which is composed of two functions: createDropdown() and the onEditTrigger(e).

• The global variables:

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']; 

If you want to add other continents to the function, you should only change the var continentsList variable.

• The createDropdown() function:

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

The above function is used to create the dropdown with all the continents and it will be created on the A9 cell on the Master Calls Sheet.

• The onEditTrigger(e) function:

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]);
      }
    }
  }
}

The above function is an installable trigger which means that every time the value from the dropdown is changed, it will fire. It works by looping through the continentsList and it checks if the value from each iteration corresponds to the one selected from the dropdown. If the values correspond, then the countries from each continent are listed.

The values will appear on the B column starting from B9 on the Master Calls Sheet.

The trigger should be set with the following properties:

Moreover, I suggest you check the following links since they might be of help in your future development:

• Installable Triggers;

• DataValidation Class.