I've been trying to add 3rd and 4th level dependent dropdown using the code from Code with Curt(https://codewithcurt.com/create-dependent-drop-down-on-google-web-app/), but I'm running into some issues. In this code below, I'm trying to add a 3rd level, but it doesn't seem to work. This is the output I'm trying to achieve. I'm not sure if there's a fastest way to load the dropdown from Google sheets, as this codes loads in about 3 seconds, or a better way to fetch it from Sheets.
Here's the code:
Google Apps Script:
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();
var class = e.parameters.class.toString(); //class is a reserved word
AddRecord(name, color, fruit, class);
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 getClass(fruit) {
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, 2).getValue() === fruit) {
return_array.push(lovSheet.getRange(i, 3).getValue());
}
}
return return_array.sort();
}
function AddRecord(name, color, fruit, class) {
var url = ''; //URL OF GOOGLE SHEET;
var ss = SpreadsheetApp.openByUrl(url);
var dataSheet = ss.getSheetByName("DATA");
dataSheet.appendRow([name, color, fruit, class, new Date()]);
}
function getUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}
HTML:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<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);
};
function getClass(queue)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
class.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
class.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
class.appendChild(option);
});
}).getClass(queue);
};
</script>
<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>
<label style="font-size: 20px" >Class</label><br>
<select name="location" id="location" style="font-size: 20px" >
<option value="" selected disabled>Select Class</option>
</select><br><br>
<label style="font-size: 20px" >Brand</label><br>
<select name="location" id="location" style="font-size: 20px" >
<option value="" selected disabled>Select Brand</option>
</select><br><br>
<input type="submit" name="submitButton" value="Submit" style="font-size: 20px" />
<span style="font-size: 20px" ><?= message ?></span>
</form>
</body>
</html>
