In this post, I demonstrate how to create a dependent drop-down list in a Google Web App using Google Apps Script on Google Sheets.
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
- For Further Details on Deploying a Web App Click Here
- Check out how you can do a Multi-Select in Dropdown. Click Here
Code in Video:
function doGet(e) {
var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
var colors = getColors();
htmlOutput.message = '';
htmlOutput.colors = colors;
return htmlOutput.evaluate();
}
function doPost(e) {
Logger.log(JSON.stringify(e));
var name = e.parameters.name.toString();
var color = e.parameters.color.toString();
var fruit = e.parameters.fruit.toString();
AddRecord(name, color, fruit);
var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
var colors = getColors();
htmlOutput.message = 'Record Added';
htmlOutput.colors = colors;
return htmlOutput.evaluate();
}
function getColors() {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var lovSheet = ss.getSheetByName("LOV");
var getLastRow = lovSheet.getLastRow();
var return_array = [];
for(var i = 2; i <= getLastRow; i++)
{
if(return_array.indexOf(lovSheet.getRange(i, 1).getValue()) === -1) {
return_array.push(lovSheet.getRange(i, 1).getValue());
}
}
return return_array;
}
function getFruits(color) {
var ss= SpreadsheetApp.getActiveSpreadsheet();
var lovSheet = ss.getSheetByName("LOV");
var getLastRow = lovSheet.getLastRow();
var return_array = [];
for(var i = 2; i <= getLastRow; i++)
{
if(lovSheet.getRange(i, 1).getValue() === color) {
return_array.push(lovSheet.getRange(i, 2).getValue());
}
}
return return_array;
}
function AddRecord(name, color, fruit) {
var url = ''; //URL OF GOOGLE SHEET;
var ss= SpreadsheetApp.openByUrl(url);
var dataSheet = ss.getSheetByName("DATA");
dataSheet.appendRow([name, color, fruit, new Date()]);
}
function getUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<script>
function GetFruit(color)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
fruit.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
fruit.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
fruit.appendChild(option);
});
}).getFruits(color);
};
</script>
</head>
<body>
<h1>Web App Dependent Drop Down</h1>
<?var url = getUrl();?>
<form method="post" action="<?= url ?>" >
<label style="font-size: 20px" >Name</label><br>
<input type="text" name="name" style="font-size: 20px" /><br><br>
<label style="font-size: 20px" >Colors</label><br>
<select name="color" style="font-size: 20px" onchange="GetFruit(this.value)" >
<option value="" ></option>
<? for(var i = 0; i < colors.length; i++) { ?>
<option value="<?= colors[i] ?>" ><?= colors[i] ?></option>
<? } ?>
</select><br><br>
<label style="font-size: 20px" >Fruit</label><br>
<select name="fruit" id="fruit" style="font-size: 20px" >
</select><br><br>
<input type="submit" name="submitButton" value="Submit" style="font-size: 20px" />
<span style="font-size: 20px" ><?= message ?></span>
</form>
</body>
</html>
Related Posts





