How to Create a Dependent Dropdown List in Google Sheets and apply to Multiple Rows

In a previous post, I showed you “How to Create a Dependent Dropdown List in Google Sheets“. However, you can only apply the dependent dropdown list into one cell using that method. As such, in this post, I will show you how you can create a dependent dropdown list in Google Sheets and apply to multiple rows.

You need to have a sound idea about Google Apps Scripts to create a dependent dropdown list in Google Sheets using this method. You can also learn Apps Scripts Basics and Working with Google Sheets from following tutorials on Google.

Apps Script Basics and working with Google Sheets

For this example, I am going to use the following data to populate primary and dependent dropdowns. The primary dropdown is populated by “Region” and Dependent dropdown list is populated by “Countries” where a Region has multiple countries.

I have named the Sheet with the list of data for populating the dropdown as “Lists” and the Sheet which the dropdown menu created as “Dropdown“. You are also advised to do the same since I am going to use these names in the code.

In this method also, I am using data validation to create a dropdown list. However, unlike in the previous post, here I apply the data validation to relevant cells using Google Apps Script. You can learn more about Class DataValidationBuilder from this Google Apps Script tutorial.

Creating primary dropdown list with Apps Script

The following script (function: createPrimaryDrpdwon) applies data validation to the range A2:A100 and create “region” dropdown list in each cell. The list is populated from the data in the “Lists” sheet as mentioned before.

If you are new to Google Apps Script; you can open the Apps Script editor by going to Tools  → <> Script Editor in the Google Sheet. Then copy and replace the default script with the following script.

//CREATE PRIMARY DROPDOWN LIST
function createPrimaryDrpdwon() {
  /* SET FOLLOWING VARIABLES */
  
  var dataSS           = "Lists";         //Name of the sheet that contain data for dropdown lists
  var dropSS           = "DropDown";      //Name of the sheet which dropdown list to be created
  var primaryDataRange = "A2:A213";       //Data range for primary dropdown
  var primaryDropRange = "A2:A100";       //Range which primary dropdown set

  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);
  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
  
  primaryDropRange.setDataValidation(validationRule);
}

Creating the dependent dropdown list with Apps Script

Here, I am using the onEdit() Apps Script trigger to apply a data validation rule to dependent cell (the RHS cell in the same row of the primary cell). Once you select a “Region” from the primary dropdown list, the dependent dropdown list is populated with the list of “Countries” in that region. Add the following script below the previous script to create the dependent dropdown list.

//CREATE SECONDARY DROPDOWN LIST
function onEdit(){
  /* SET FOLLOWING VARIABLES */
  var dataSS       = "Lists";         //Name of the sheet that contain data for dropdown lists
  var dropSS       = "DropDown";      //Name of the sheet which dropdown list to be created
  var allDataRange = "A2:B213";       //Data range for dropdown list (both primary and dependent)
  var primaryDDCol = 1;               //Column number of the primary drop down
  
  var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
  var dropDData    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(allDataRange).getValues();
  var activeCell   = dropSS_.getActiveCell();
  var activeColumn = activeCell.getColumn();
  var activeRow    = activeCell.getRow();
  
  if(activeColumn==primaryDDCol){
    var dep_Col         = primaryDDCol+1;
    var dep_Row         = activeRow;
    var depCell         = dropSS_.getRange(dep_Row, dep_Col);
    var primarySelected = activeCell.getValue();
    var validationRule  = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData,primarySelected)).build();
    
    depCell.setDataValidation(validationRule);
  }
}

function getDependentList(dropDData,primarySelected){
  var dependenList = [];
  var j = 0;
  if(dropDData != null){
    for(i=0; i<dropDData.length; i++){
      if(dropDData[i][0]==primarySelected){
        dependenList[j] = dropDData[i][1];
        j++;
      }
    }
  }
  return dependenList;
}

Once you combined the above two scripts, it looks like bellow. To apply the validation rules, you need to run the “createPrimaryDrpdwon()” function once.

Final Script

//CREATE PRIMARY DROPDOWN LIST
function createPrimaryDrpdwon() {
  /* SET FOLLOWING VARIABLES */
  
  var dataSS           = "Lists";         //Name of the sheet that contain data for dropdown lists
  var dropSS           = "DropDown";      //Name of the sheet which dropdown list to be created
  var primaryDataRange = "A2:A213";       //Data range for primary dropdown
  var primaryDropRange = "A2:A100";       //Range which primary dropdown set

  var primaryDropList  = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(primaryDataRange).getValues();
  var primaryDropRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS).getRange(primaryDropRange);
  var validationRule   = SpreadsheetApp.newDataValidation().requireValueInList(primaryDropList).build();
  
  primaryDropRange.setDataValidation(validationRule);
}


//CREATE SECONDARY DROPDOWN LIST
function onEdit(){
  /* SET FOLLOWING VARIABLES */
  var dataSS       = "Lists";         //Name of the sheet that contain data for dropdown lists
  var dropSS       = "DropDown";      //Name of the sheet which dropdown list to be created
  var allDataRange = "A2:B213";       //Data range for dropdown list (both primary and dependent)
  var primaryDDCol = 1;               //Column number of the primary drop down
  
  var dropSS_      = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dropSS);
  var dropDData    = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSS).getRange(allDataRange).getValues();
  var activeCell   = dropSS_.getActiveCell();
  var activeColumn = activeCell.getColumn();
  var activeRow    = activeCell.getRow();
  
  if(activeColumn==primaryDDCol){
    var dep_Col         = primaryDDCol+1;
    var dep_Row         = activeRow;
    var depCell         = dropSS_.getRange(dep_Row, dep_Col);
    var primarySelected = activeCell.getValue();
    var validationRule  = SpreadsheetApp.newDataValidation().requireValueInList(getDependentList(dropDData,primarySelected)).build();
    
    depCell.setDataValidation(validationRule);
  }
}

function getDependentList(dropDData,primarySelected){
  var dependenList = [];
  var j = 0;
  if(dropDData != null){
    for(i=0; i<dropDData.length; i++){
      if(dropDData[i][0]==primarySelected){
        dependenList[j] = dropDData[i][1];
        j++;
      }
    }
  }
  return dependenList;
}

The following animation shows you how you can run this script.

You can make a copy of the Google Sheet with the above workings from this link.

20 thoughts on “How to Create a Dependent Dropdown List in Google Sheets and apply to Multiple Rows”

  1. Hey there, thanks so much for this code, SUPER helpful. If I want to duplicate this for another 2 columns, how would I do this? So it’s the same source data, but lets say I want column A to be the primary drop list, Column B to be the dependent dropdown, but I also want column C to be a second primary drop list (same data source), and column D to be another dependent (same source again). I tried copying the second half of the code….if I do that, columns C and D work, but A and B stop working. Any guidance is appreciated. Thanks!

    Reply
  2. Hi, I have an spread sheet where my primary drop down will be in the column H (on the drop down sheet) therefore the dependent dropdown will be in the column I. Using this script it does not work (just for the dependent drop down column) the primary drop down is working.Can you please help me guiding me with the changes I must do in the script so it can work?

    Reply
    • Hi,
      Assuming that you have named the two sheets as in the example and your data source is in the same range. You have to change line number 8 and 23 as follows
      Line 8: var primaryDropRange = “H2:H100”;
      Line 24: var primaryDDCol = 8;

      Reply
  3. I haven’t received a reply to my question and I don’t see it posted here so I am submitting it again. I found your tutorial very helpful, thank you! I would like to add a third column with dropdown lists that depend on what is chosen in the second column. Would you be able to provide guidance? I tried to edit the script but I am very much an amateur and it didn’t work. I would really appreciate any help. Thanks and happy new year!

    Reply
  4. I have a list bigger than 500 and got the following error “The data validation rule has more items than the limit of 500. Use the ‘List from a range’ criteria instead.”. Is there any solution?

    Reply
  5. Hi Admin,

    I have been looking for a dependent drop down for awhile and I finally found it. Thank you for the app script and it is easy to understand.

    But may I know how can I add the same script for three different sheets? The data is the same, just that I need to apply for three different sheets under the same workbook.

    Reply
  6. Hello.

    This was of tremendous help, thank you! Absolutely amazing.

    Was wondering what would be your approach if the list is over 500 items? I got the error “The data validation rule has more items than the limit of 500. Use the “List from a range” criteria instead” And I am not sure how to solve it – there seems to be no help online about this specific issue, Any help would be appreciated.

    Thanks.

    Reply
  7. I love this formula but when I put it into my sheet I getting an error for line 10: “TypeError: Cannot read property ‘getRange’ of null (line 10, file “Code”)”.
    I thought it might be one of the changes I made to customize the script for my sheet but when I copy the exact formula above it still has an issue. Any suggestions to fix this?
    Thanks in advance!

    Reply
  8. I left this on the wrong tutorial version – the basic one….

    This was a fantastic tutorial! I am trying to collate data with this but with a new month on each tab. I *can* rename the sheet I want the lists to be formed in and run it each month, but this seems a little clunky. Is there some way I can amend the script so it will set up the same drop lists in the same spots from the same initial data set? Thanks for your time.

    Reply
  9. Hello, could you please tell me how to edit this if I want this script on multiple sheets (not all). In my worksheet I need to apply this for each month which has its own sheet.

    Reply
  10. I’m creating multiple dropdown but I think there is an error in your code when there are choices bound to both the dropdown. As you can see the example of the website imgur.com/BV7neSH Gotham City shouldn’t be in the dropdown, cause it is part of Australia but not part of WprHI

    Reply
  11. Hi,

    I have a list bigger than 500 and got the following error “The data validation rule has more items than the limit of 500. Use the ‘List from a range’ criteria instead.”. Is there any solution? What would be your approach? My list is a little over 1,700 rows in the list column. Many of them are the exact same except one difference, this led to there being many rows but 50 or less unique items in each column. Please any help is appreciated.

    Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.