In a previous post, I explained how to create a simple dependent dropdown list in Google Sheets. However, using that method you cannot copy the dependent dropdown list into multiple rows. As such, in this tutorial, I will show you how you can create a dependent dropdown list in Google Sheets and copy it into multiple rows.
For this method, you need to have a sound idea about Google Apps Script. You can learn Apps Scripts Basics for Google Sheets from the following tutorials on Google Developers tutorial.
Apps Script Basics and working with Google Sheets
Table of Contents
Preparing the Google Sheets and sample data
For this example, I am going to use the following data stored in another sheet of the same spreadsheet to populate primary and dependent dropdowns. The primary dropdown is populated by “Region” and the 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 in 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 in Google Sheets with Apps Script
The following script (function: createPrimaryDrpdwon) applies data validation to the range A2:A100 and creates a “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 in Google Sheets with Apps Script
Here, I am using the onEdit() Apps Script trigger to apply a data validation rule to the 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 below. 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.
Make a copy of the Google Sheet with the above script
Go to Tools > Script editor to access the code
Wrapping Up
In this post, you learned to create a multi-row dependent dropdown list in Google Sheets. To use this method, you need to have a basic knowledge of Google Apps Scripts for Google Sheets. You can learn Google Apps Scripts from the link provided above. Here we created the dropdown list for one column. However, you can expand this to multiple columns too.
Hi how can I apply this for multiple range in same sheets?
Dear Sir,
I need some help here please.
Sheet1 is named ‘Ticket Reviews’
Dropdown 1 starts in G4 and goes to G1087
Dropdown 2 should start in H4 and go down to H1087
Sheet3 is named ‘Sheet3’ and has the source data.
Primary data range is Sheet3 F1:F4
Secondary data range for secondary dropdown is Sheet3 A2:D16
Primary dropdown works fine IF I use F1:F4 as the source data otherwise the dropdown in column G looks like: Fruit,Vegetables,Drinks,Other
The secondary dropdown – the arrow to chose the option is there but no data is being pulled in.
I have Primary DDCol = 7 to reach column G. My code looks like this:
var dataSS = “Sheet3”; //Name of the sheet that contain data for dropdown lists
var dropSS = “Ticket Reviews”; //Name of the sheet which dropdown list to be created
var primaryDataRange = “F1:F4”; //Data range for primary dropdown
var primaryDropRange = “G4:G1087”; //Range which primary dropdown set
var dataSS = “Sheet3”; //Name of the sheet that contain data for dropdown lists
var dropSS = “Ticket Reviews”; //Name of the sheet which dropdown list to be created
var allDataRange = “A2:D19”; //Data range for dropdown list (both primary and dependent)
var primaryDDCol = 7; //Column number of the primary drop down
am I doing something wrong?
This works like a charm! Fantastic! You saved me a big headache. I spent three days trying to do this!! Big THANK YOU!!
Thanks, this is helpful!
How can this (easily) be applied to existing cells? / How can the dependencies be easily updated throughout the spreadsheet?
Right now, I have to manually “touch” (change, change back) each primary cell to make the dependent selections show up/update.
Thank you, sir.
@Admin How can we spread this across multiple sheets in the same workbook?
Try replacing lines 6 and 22 of the code with the following.
There are multiple comments on the same topic, no answers though. Is there a way to make this work in multiple tabs of the same spread sheet? Thanks