In this post, I demonstrate how to Create a Dependent Dropdown list on Google Sheets using Google Apps Script.
How to Video:
Video Notes:
- Legacy Apps Script Editor Used in Video
- Apps Script (Script Editor) is now located under tab ‘Extensions’ instead of ‘Tools’ on Google Sheets
Code in Video:
function onEdit(e) {
var range = e.range;
var spreadSheet = e.source;
var sheetName = spreadSheet.getActiveSheet().getName();
var column = range.getColumn();
var row = range.getRow();
var colorValue = e.value;
var returnValues = [];
if(sheetName == 'MAIN' && column == 2 && row == 1)
{
var ss= SpreadsheetApp.getActiveSpreadsheet();
var dataSheet = ss.getSheetByName("DATA");
var mainSheet = ss.getSheetByName("MAIN");
//GET SQL STATEMENT
var lastRowData = dataSheet.getLastRow();
for(var i = 2; i <= lastRowData; i++)
{
if(colorValue == dataSheet.getRange(i, 1).getValue())
{
returnValues.push(dataSheet.getRange(i, 2).getValue());
}
}
mainSheet.getRange('B2').clear();
var dropdown = mainSheet.getRange('B2');
var rule = SpreadsheetApp.newDataValidation().requireValueInList(returnValues).build();
dropdown.setDataValidation(rule);
}
}
Related Posts



















