Script based Dependent Drop-Down List not working (Sheets)

Question

I have set up this script to give me a drop list on cell Y4, depending on the value chosen on cell S4. The data where both values come from are based on another sheet named DW, starting on row 2, because it has headers.

function onEdit(){
  var tabLists = "DW";
  var tabValidation = "EditItem";
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(tabLists);
  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 19 && activeCell.getRow() == 4 && ss.getSheetName() == tabValidation){

    activeCell.offset(0, 6).clearContent().clearDataValidations();

    var makes = datass.getRange("A2:C").getValues();

    var makeIndex = makes[0].indexOf(activeCell.getValue()); //This gives me row2 to check below, but I need the column data, instead. How to change this one then?

    if(makeIndex != 0){

      var validationRange = datass.getRange("C2:C");
        var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
        activeCell.offset(0, 6).setDataValidation(validationRule);

     }  

  }

}

I get the list from DW("C2:C") listed on Y4, but it doesn't come "filtered", according to the item name chosen on S4.

Any light as to what I'm missing?

Thank you!

Answer 1

If I understood your issue correctly - the problem is in your statement if(makeIndex != 0)

Keep in mind that the method indexOf returns -1 and not 0 if an entry has not been found in an array.

So, you should query for if(makeIndex != -1) if you want to implement your data validation only in case activeCell.getValue() is contained in "C2:C".

UPDATE

• You want to know which row in column B contains the value in S4
• You want to the value in column C of this row to be used for data validation in Y4

In this case you need to

• convert the entries in column B into a searchable array - this can be done with map()
• find the corresponding value in column C - this can be done by manually build the A1 notation of the cell with the retrieved index

Sample:

    var makes = datass.getRange("A2:C").getValues();    
    var searchArray=makes.map(function(e){return e[1];});                            
    var makeIndex = searchArray.indexOf(activeCell.getValue());    
    if(makeIndex != -1){
      var validationRange = datass.getRange("C" + (2+makeIndex));
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      activeCell.offset(0, 6).setDataValidation(validationRule);

    } 

Notes:

• It seems that you are not using column A, so there is no need to include it in range makes
  • Notaions like A2:C are not very efficient becasue they include many empty rows. Instead it is better to use as a margin the last row with contents