Google Apps script html drop down list

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 Scripts. 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

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 Script editor to access the code