Dependable dropdown and getValues() 2D array

Question

I am building dependable (filtered) dropdown in google sheet, where value in the one dropdown list depends on the choice of another. Although being a releatively simple arrangement, I came across the problem with getValues() which returns 2D array.

There is the following set up:

Two sheets, one Activity and another $SheetData.
Column E of the Activity contains dropdowns, which when selected, should provide appropriate choice in the column G.
The data for Column E is sourced from sheet $SheetData (E1:K1);
The data for Column G is sourced from sheet $SheetData from each of the respective columns (E1 = SIRE: SIRE1, SIRE2 etc);
When SIRE is selected in Col E of Activity, I need the data in column G to be from E2:E of the $SheetData Here is my code:

function dependableDropdown() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activity = ss.getSheetByName("Activity");
  var sheetData = ss.getSheetByName("$SheetData");
  var activeCell = activity.getActiveCell();

  if(activeCell.getColumn() == 5 && activeCell.getRow() > 1){
    activeCell.offset(0, 2).clearContent().clearDataValidations();

    var inspectionTypes = sheetData.getRange(1, 5, 1, 7).getValues();    

    var selectedValue = activeCell.getValue();
    var inspectionTypesIndex = inspectionTypes[0].indexOf(selectedValue) + 1;

    var validationRange = sheetData.getRange(2, inspectionTypesIndex, sheetData.getLastRow());

    var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
    activeCell.offset(0, 2).setDataValidation(validationRule);
  };
};

Value returned by inspectionTypesIndex seems to be correct (SIRE = 1, OVID = 2 etc), but when I select data in Col E, I get the wrong data fed into the column G.

Apprecaite your help.
Thank you,

Corresponding screens:

Activity sheet

$SheetData

How do you trigger the above function dependableDropdown()? Or do you run it manually as preliminary test? — Александр Ермолин
@АлександрЕрмолин there is onEdit() down below where dependableDropdown() is called. — AlexShevyakov

Александр Ермолин Александр Ермолин · Accepted Answer · 2019-03-21T18:13:23

If you get inspectionTypesIndex as written (SIRE = 1, OVID = 2 etc), and you have options columns: E, F, G, ... , then you should reference validationRange in the following way:

var validationRange = sheetData.getRange(2, inspectionTypesIndex + 4, sheetData.getLastRow());

because E column is 5-th, but "SIRE" index is equal to 1.

Dependable dropdown and getValues() 2D array

1 Answers