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> https://codewithcurt.com/create-dependent-drop-down-on-google-web-app/
