I've been trying to make dependent drop down lists using App script. When I select my independent drop down option, executions shows "Completed", yet it won't add in the dependent drop down in the column beside it. I can't seem to figure out what's going wrong here:

function onEdit(){

  var ss  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var datass  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Exercise Index");

  var activeCell = ss.getActiveCell();

  if(activeCell.getColumn() == 1 && activeCell.getRow() > 3 && ss.getSheetName()=="Weekly Template"){
  
    activeCell.offset(0,1).clearContent().clearDataValidations();
  

    var categories = datass.getRange(1,1,1,datass.getLastColumn()).getValues();

    var catIndex = categories[0].indexOf(activeCell.getValue()) 1;
  
    if(catIndex != 0){

      var validationRange = datass.getRange(2,catIndex,datass.getLastRow());
      var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
      activeCell.offset(0,1).setDataValidation(validationRule);

    }
  }
}

Any help would be greatly appreciated as this is my first go at App Script.

CodePudding user response：

I did manage to get it functioning as intended. Here's the script in case anyone comes across this in the future:

function onEdit(){
    var tabLists = "Exercise Index";
    var spreadsheet = SpreadsheetApp;
    var activeSheet = spreadsheet.getActiveSpreadsheet().getActiveSheet();
    var data = spreadsheet.getActiveSpreadsheet().getSheetByName(tabLists);
    
    var activeCell = activeSheet.getActiveCell();
    
    if(activeCell.getColumn() == 1 && activeCell.getRow() > 3 && activeSheet.getSheetName().includes("Week")){
      
      activeCell.offset(0, 1).clearDataValidations();
      
      var makes = data.getRange(1, 1, 1, data.getLastColumn()).getValues();
      
      var makeIndex = makes[0].indexOf(activeCell.getValue())   1;
      
      if(makeIndex != 0){
      
          var validationRange = data.getRange(2, makeIndex, data.getLastRow());
          var validationRule = spreadsheet.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
    
      }  
        
    }
    
  }