Dyanamic Dropdown List Validation (third level) using app script

Question

I have a script written to apply three levels of validation. The first level of validation is in column A on the "master" sheet and the second and third are in the app script.

I am able to select the first level of validation in the "master"sheet and the list is filtered to only show the relevant answers in a dropdown list in Column B. After selecting my answer in Column B, the dropdown arrow appears in Column C but there are no fields to select. I can manually enter a value that is on my list and it is accepted and any thing that is not in the list is rejected. I have narrowed my issue to:

var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });

When I place Logger.log(filteredOptions); in function applySecondLevelValadation(val, r){, it returns with an empty array [].

When I place

Logger.log(filteredOptions);` in `function applyFirstLevelValadation(val, r){

, it returns with the array:

[[Intermediate, 9:00 AM, Upper Dumont], [Intermediate, 12:00 PM, Outer Limts], [Intermediate, 3:30 PM, Satelite], [Intermediate, 9:00 AM, Box], [Intermediate, 12:00 PM, Hidden Valley], [Intermediate, 3:30 PM, Kermits]]

Screenshot of "options" sheet

Screenshot of "master" with all possible options for column A & B and no options for selection in column C

Any help figuring out why var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val }); is returning an empty array would be great!

Code being used:

var mainWsName = "master";
var optionsWsName = "options";
var firstLevelColumn = 1;
var secondLevelColumn = 2;
var thirdLevelColumn = 3;
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(mainWsName);
var wsOptions = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(optionsWsName);
var options = wsOptions.getRange(2,1,wsOptions.getLastRow()-1,3).getValues();

function onEdit(e) {
  var activeCell = e.range;  
  var val = activeCell.getValue();  
  var r = activeCell.getRow();  
  var c = activeCell.getColumn();  
  var wsName = activeCell.getSheet().getName();  
  if(wsName === mainWsName && c === firstLevelColumn && r > 1) {    
    applyFirstLevelValadation(val, r);    
  } else if(wsName === mainWsName && c === secondLevelColumn && r > 1) {    
    applySecondLevelValadation(val, r);    
  }  
}

function applyFirstLevelValadation(val, r) {
  if(val === "") {    
      ws.getRange(r, secondLevelColumn).clearContent();    
      ws.getRange(r, secondLevelColumn).clearDataValidations();    
      ws.getRange(r, thirdLevelColumn).clearContent();    
      ws.getRange(r, thirdLevelColumn).clearDataValidations();    
  } else {      
      ws.getRange(r, secondLevelColumn).clearContent();      
      ws.getRange(r, secondLevelColumn).clearDataValidations();      
      ws.getRange(r, thirdLevelColumn).clearContent();      
      ws.getRange(r, thirdLevelColumn).clearDataValidations();      
      var filteredOptions = options.filter(function(o){ return o[0] === val });      
      var listToApply = filteredOptions.map(function(o){ return o[1] });      
      var cell = ws.getRange(r, secondLevelColumn);      
      Logger.log(filteredOptions)      
      applyValidationToCell(listToApply,cell);
  }  
}

function applySecondLevelValadation(val, r) {
  if(val === "") {    
      ws.getRange(r, thirdLevelColumn).clearContent();    
      ws.getRange(r, thirdLevelColumn).clearDataValidations();    
  } else {      
      ws.getRange(r, thirdLevelColumn).clearContent();      
      var firstLevelColValue = ws.getRange(r, firstLevelColumn).getValue();      
      var filteredOptions = options.filter(function(o){ return o[0] === firstLevelColValue && o[1] === val });      
      var listToApply = filteredOptions.map(function(o){ return o[2] });      
      var cell = ws.getRange(r, thirdLevelColumn);      
      Logger.log(filteredOptions);      
      applyValidationToCell(listToApply,cell);
  }   
}

function applyValidationToCell(list, cell) {   
  var rule = SpreadsheetApp  
  .newDataValidation()  
  .requireValueInList(list)  
  .setAllowInvalid(false)  
  .build();  
  cell.setDataValidation(rule);
}

Answer 1

It looks like you are having an issue with the time column. Probably because it's comparing a string to a date or what have you. We would have more information if you shared a copy of the spreadsheet.

In any case, to avoid this, you can format data in both columns B (in 'master' as well as in 'options') as plain text (for that, go to Format > Number > Plain text in your spreadsheet). Then, you can write the trip times in 'options' as plain text.

I hope this is of any help to you.