Styjun

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:

1. Two sheets, one Activity and another $SheetData.


2. Column E of the Activity contains dropdowns, which when selected, should provide appropriate choice in the column G.


3. The data for Column E is sourced from sheet $SheetData (E1:K1);


4. The data for Column G is sourced from sheet $SheetData from each of the respective columns (E1 = SIRE: SIRE1, SIRE2 etc);


5. 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

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.