Can I get help to fix the Apps Script code for Dependant Dropdowns?

Question

I have dependent dropdowns in my Google Sheet per the link below:

The dropdowns are in the 'Events/Incidents' sheet:

Columns D and E are dependent on column C. The is an image of the dropdown data from the 'Dropdown Lists' sheet:

The dropdown in column C of the 'Events/Incidents' sheet is data validation from 'Dropdown Lists' sheet A2 to F2. The selection in the column C dropdown will dictate the criteria in the columns D & E dropdowns and the data will be the same for both columns. This will be sourced from the 'dropdown lists' sheet from either A4 down, B4 down, and so on e.g. if 'Tauranga' is selected all of the names under Tauranga will be in the in the D & E dropdowns.

This is my onEdit code:

 //Dependent Dropdowns for 'Event/Incidents' Sheet
   {
    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
    var tablists = 'Dropdown Lists';
    var tabValidation = 'Events/Incidents';
    var ss = spreadsheet.getActiveSheet();
    var datass = spreadsheet.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) {
        //Dynamic dropdown for 'Event Recorded By'
        var validationRange = datass.getRange(3, baseIndex, datass.getLastRow());
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 1).setDataValidation(validationRule);
         //Dynamic dropdown for 'Employee'
        var validationRange2 = datass.getRange(4, baseIndex, datass.getLastRow());
        var validationRule2 = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange2).build();
        activeCell.offset(0, 2).setDataValidation(validationRule2);
         }
    }
    if (ss.getSheetName() == tabValidation) {
      var lock = LockService.getScriptLock();
      if (lock.tryLock(0)) {
        autoid_(ss);
        lock.releaseLock();
      }
    }

I can't get this code to work and would appreciate some help.

Andres Duarte Andres Duarte · Accepted Answer · 2019-11-15T16:59:18

Here is an example that you can implement in your onEdit function, this will create both drop-downs for columns D and E in each row depending on the value selected in column C:

  var range = e.range;
  var editedRow = range.getRow();

  var spreadsheet = SpreadsheetApp.getActive();
  var dropdownSheet = spreadsheet.getSheetByName("Dropdown Lists");
  var eventsSheet = spreadsheet.getSheetByName("Events/Incidents");

  var baseSelected = eventsSheet.getRange('C' + editedRow).getValue();
  var column;

  switch (baseSelected) {
    case 'EBOP': column = 'A'; break;
    case 'Tauranga': column = 'B'; break;
    case 'Palmerston North': column = 'C'; break;
    case 'Kapiti': column = 'D'; 
  }
  var startCell = dropdownSheet.getRange( column +'4');
  var endCellNotation = startCell.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation();
  var ruleRange =  dropdownSheet.getRange(startCell.getA1Notation() + ':' + endCellNotation);

  var dropdown1 = eventsSheet.getRange('D' + editedRow);
  var dropdown2 = eventsSheet.getRange('E' + editedRow);

  var rule1 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();
  var rule2 = SpreadsheetApp.newDataValidation().requireValueInRange(ruleRange).build();

  dropdown1.setDataValidation(rule1);
  dropdown2.setDataValidation(rule2);

I used the DataValidation [1] and DataValidationBuilder [2] classes.

[1] https://developers.google.com/apps-script/reference/spreadsheet/data-validation

[2] https://developers.google.com/apps-script/reference/spreadsheet/data-validation-builder

Can I get help to fix the Apps Script code for Dependant Dropdowns?

1 Answers