validationgoogle-apps-scriptgoogle-sheets

How can I do dynamic / dependent drop downs in Google Sheets?


How do you get a sub-category column to populate a drop down based on the value selected in the main category drop down in Google Sheets?


Solution

  • You can start with a Google Sheet set up with a main page and drop down source page like shown below.

    You can set up the first column drop down through the normal Data > Validations menu prompts.

    Main Page

    Main Page with the drop down for the first column already populated.

    Drop Down Source Page

    Source page for all of the sub-categories needed

    After that, you need to set up a script with the name onEdit. (If you don't use that name, the getActiveRange() will do nothing but return cell A1)

    And use the code provided here:

    function onEdit() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sheet = SpreadsheetApp.getActiveSheet();
      var myRange = SpreadsheetApp.getActiveRange();
      var dvSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Categories");
      var option = new Array();
      var startCol = 0;
    
      if(sheet.getName() == "Front Page" && myRange.getColumn() == 1 && myRange.getRow() > 1){
        if(myRange.getValue() == "Category 1"){
          startCol = 1;
        } else if(myRange.getValue() == "Category 2"){
          startCol = 2;
        } else if(myRange.getValue() == "Category 3"){
          startCol = 3;
        } else if(myRange.getValue() == "Category 4"){
          startCol = 4;
        } else {
          startCol = 10
        }
        
      if(startCol > 0 && startCol < 10){
        option = dvSheet.getSheetValues(3,startCol,10,1);
        var dv = SpreadsheetApp.newDataValidation();
        dv.setAllowInvalid(false);  
        //dv.setHelpText("Some help text here");
        dv.requireValueInList(option, true);
        sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).setDataValidation(dv.build());
       }
       
      if(startCol == 10){
        sheet.getRange(myRange.getRow(),myRange.getColumn() + 1).clearDataValidations();
      } 
      }
    }
    

    After that, set up a trigger in the script editor screen by going to Edit > Current Project Triggers. This will bring up a window to have you select various drop downs to eventually end up at this:

    Trigger set up

    You should be good to go after that!