How do you do dynamic / dependent drop downs in Google Sheets?

by

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!

More Related Contents:

  1. Why do we use SpreadsheetApp.flush();?
  2. Is it possible to ‘prefill’ a google form using data from a google spreadsheet?
  3. How to let validation depend on the pressed button?
  4. Is there a way to evaluate a formula that is stored in a cell?
  5. Faster way to find the first empty row in a Google Sheet column
  6. How to perform validation in JSF, how to create a custom validator in JSF
  7. Detect user inserting row or column in a google spreadsheet and reacting in a script
  8. Is there a custom function or script that returns gid of a specific sheet in Google Sheets?
  9. How to convert a Google Docs-File to an Excel-File (XLSX)
  10. jQuery disable rule validation on a single field
  11. Found an unexpected Mach-O header code: 0x72613c21 in Xcode 7
  12. How to get onFormSubmit to trigger automatically?
  13. Copying a spreadsheet copies all linked files as well
  14. What characters should be restricted from a Unix file name?
  15. “SpreadsheetApp.getUi() cannot be called from this context”
  16. Using built-in spreadsheet functions in a script
  17. Scraping data to google sheets from a website that uses JavaScript
  18. How to allow access for importrange function via apps script?
  19. Can no longer produce PDF from Google Sheets spreadsheet for some of the users
  20. How do you add UI inside cells in a google spreadsheet using app script?
  21. Disable validation of HTML form elements
  22. Dynamically creating spreadsheet menu items
  23. Creating and Sending sheets in the same function
  24. Best Practices for Multiple OnEdit Functions
  25. How to customize JSF validation error message
  26. How to ignore empty cell values for getRange().getValues()
  27. How do I replace text in a spreadsheet with Google Apps Script?
  28. Is it possible to create macro buttons to navigate in Google Sheets?
  29. Publish an add-on privately
  30. Reset checkboxes to false

Leave a Comment