Dependent Dropdowns: 1 Independent, multiple dependent

user_18176624 0
Viewed 63

So, this goal seemed simple at first, but I can't seem to wrap my head around how to accomplish it.

I have these independent dropdowns, with between 3 to 4 dependent dropdowns associated with each independent. I would like to be able to apply the associated dependent dropdowns when the independent dropdown is selected.

Example: Dropdowns in Column A have 3-4 variables associated with them (https://docs.google.com/spreadsheets/d/1ErRBqfEeRqTNLIH0N11IjViP-_ej1LMdrOXNvDOcsx8/edit?usp=sharing)

In the following code, I can apply 1 independent, and 1 dependent, but I can't figure out how to scale it to my needs:

function onEdit(){
    var tabLists = "Lists";
    var spreadsheet = SpreadsheetApp;
    var activeSheet = spreadsheet.getActiveSpreadsheet().getActiveSheet();
    var data = spreadsheet.getActiveSpreadsheet().getSheetByName(tabLists);
    
    var activeCell = activeSheet.getActiveCell();
    
    if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && activeSheet.getSheetName().includes("Programming")){
      
      activeCell.offset(0, 1).clearDataValidations(); //Clears data validation 1 column over
      
      var exerciseCat = data.getRange(1, 1, 1, data.getLastColumn()).getValues();
      var catIndex = exerciseCat[0].indexOf(activeCell.getValue()) + 1;
      
      if(catIndex != 0){
      
          var validationRange = data.getRange(3, catIndex, data.getLastRow());
          var validationRule = spreadsheet.newDataValidation().requireValueInRange(validationRange).build();
          activeCell.offset(0, 1).setDataValidation(validationRule);
    
      }  
        
    }
    
  }

I thought that maybe my answer existed within making a 4-level dependent dropdown, but that didn't work either.

All help is greatly appreciated, as I am still quite new to any form of coding.

google-apps-script google-sheets
1 Answers

From your showing script, I guessed that you might have wanted to directly run the script with the script editor. So, in this modification, I didn't use the event object. By this, you can run the script with the OnEdit trigger and also directly run this modified script with the script editor.

And, in your script, only the data validation of column "B" of "Programming" sheet is set. In order to set other columns, I prepared an object for retrieving the values for setting the data validation rules.

So, how about the following modification?

Sample script:

function onEdit() {
  var tabLists = "Lists";
  var spreadsheet = SpreadsheetApp;
  var activeSheet = spreadsheet.getActiveSpreadsheet().getActiveSheet();
  var data = spreadsheet.getActiveSpreadsheet().getSheetByName(tabLists);
  var activeCell = activeSheet.getActiveCell();

  // I modified below script.
  var row = activeCell.getRow();
  if (activeCell.getColumn() == 1 && row > 1 && activeSheet.getSheetName().includes("Programming")) {
    var obj = { "Top Sets": [1, 2, 3], "Top Set/Percentage": [4, 5, 6, 7] };
    var v = activeCell.getValue();
    if (!obj[v]) return;
    activeCell.offset(0, 1, 1, activeSheet.getLastColumn() - 1).clearContent().clearDataValidations();
    var lastRow = data.getLastRow();
    var rules = obj[v].map(r => {
      var r = data.getRange(3, r, lastRow - 2, 1);
      return SpreadsheetApp.newDataValidation().requireValueInRange(r).build();
    });
    activeCell.offset(0, 1, 1, rules.length).clearContent().setDataValidations([rules]);
  }
}
  • When the column "A" of "Programming" sheet is edited, this script is run. And, by the value of column "A", the data validation rules of other columns are put.

Note:

  • This modified script can be tested using your provided Spreadsheet. So, when you change the Spreadsheet, this script might not be able to be used. Please be careful about this.
user_71086530
Related