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
Create Custom HTML Form for Google Sheets - In this post, I demonstrate the steps on creating a Custom HTML Form for Google Sheets using Google Apps Script.
How to Link Between Google Web App Pages - In this post, I demonstrate how to link Web App HTML pages together and pass parameters to the Web App HTML pages using Google Apps Script.
How to Call Google Apps Script Function from Web App - In this post, I demonstrate how to call Google Apps Script Functions from Web App. I also show how to pass parameters in and out of functions.
How to Create Web App Form on Google Sheets - In this post, I demonstrate how to create a simple Web App form that populates Google Sheets using Google Apps Script.
Create Multi-Select Dropdown in Web App on Google Sheets - In this post, I demonstrate how to use a multi-select dropdown in a Web App on Google Sheets. This will in turn store the multiple values in a cell on Google Sheets.
Create File Loader Google Web App to Google Drive - In this post, I demonstrate how to create a File Loader Google Web App to Google Drive on Google Sheets. It works with such files as PNG, JPG, DOC, XLS, PDF, and many more.
