How do you create a second dynamic dependant dropdown in Google Sheets App Script?

Question

I have started a Google Sheet which has 4 dropdowns. Dropdowns 2, 3 and 4 all depend on the selection in dropdown 1. I have got dropdown 2 working but can't work out the code in App Script Editor for dropdowns 3 and 4.

This is my sheet titled 'Dropdown Lists':

Dropdown List

Row 1 is in dropdown 1 (Column C of the 'Event/Incidents' Sheet)

Rows 4 to 10 are in dropdown 2 (Column D of the 'Event/Incidents' Sheet)

Rows 15 to the end row are in dropdowns 3 (Column E of the 'Event/Incidents' Sheet) & also in dropdown 4 (Column F of the 'Event/Incidents' Sheet) This is the 'Events/Incidents' Sheet.

Events/Incidents

I have the following code which works for dropdown 2 (Column D of the 'Events/Incidents' Sheet) only:

App Script Code

Would really appreciate help with the rest of the code.

ziganotschka ziganotschka · Accepted Answer · 2019-10-18T11:46:11

In order to adapt your script and create additional dropdown menus, you need to

create additional validation rules
with adjusted validation ranges
adjust the offset according to the position where the dropdown menu shall be inserted.

Sample:

      var validationRange2=datass.getRange(15,baseIndex,datass.getLastRow());
      var validationRule2=SpreadsheetApp.newDataValidation().requireValueInRange(validationRange2).build()
      activeCell.offset(0,2).setDataValidation(validationRule2);

The full code would be:

function onEdit() {
 var tablists="Dropdown Lists";
  var tabValidation="Events/Incidents";
  var ss=SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass=SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tablists);
  var activeCell=ss.getActiveCell();
  if(activeCell.getColumn()==3&&activeCell.getRow()>1&&ss.getSheetName()==tabValidation){
    activeCell.offset(0,1).clearContent().clearDataValidations();
    var base=datass.getRange(2,1,1,5).getValues();
    var baseIndex=base[0].indexOf(activeCell.getValue())+1;
    Logger.log(baseIndex);
    if(baseIndex!=0){
      var validationRange=datass.getRange(3,baseIndex,10);
      var validationRule=SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build()
      activeCell.offset(0,1).setDataValidation(validationRule);

      var validationRange2=datass.getRange(15,baseIndex,datass.getLastRow());
      var validationRule2=SpreadsheetApp.newDataValidation().requireValueInRange(validationRange2).build()
      activeCell.offset(0,2).setDataValidation(validationRule2);
      activeCell.offset(0,3).setDataValidation(validationRule2);

    }
  }
}

To obtain a better understanding of the code, please refer to the Apps Script documentation and tutorials

How do you create a second dynamic dependant dropdown in Google Sheets App Script?

1 Answers