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.

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.

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

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.

Leave a Reply

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

%d bloggers like this: