google-apps-scriptweb-applicationsdropdownhtml-select
Google Web App Dynamic Dependent Dropdown
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>
Solution
You may use the following GAS and HTML:
Google Apps Script
I have added the onlyUnique function (from another SO post) to filter out similar entries. I also added other functions to cater the Class and Brand columns. I also changed the variable class to classParam since class is a reserved word.
function doGet(e) {
var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
var colors = getColors();
var fruits = getFruits();
var classParams = getClasses();
var brands = getBrands();
htmlOutput.message = '';
htmlOutput.colors = colors;
htmlOutput.fruits = fruits;
htmlOutput.classParams = classParams;
htmlOutput.brands = brands;
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 classParam = e.parameters.classParam.toString();
var brand = e.parameters.brand.toString();
AddRecord(name, color, fruit, classParam, brand);
var htmlOutput = HtmlService.createTemplateFromFile('DependentSelect');
var colors = getColors();
var fruits = getFruits();
var classParams = getClasses();
var brands = getBrands();
htmlOutput.message = 'Record Added';
htmlOutput.colors = colors;
htmlOutput.fruits = fruits;
htmlOutput.classParams = classParams;
htmlOutput.brands = brands;
return htmlOutput.evaluate();
}
function onlyUnique(value, index, self) {
return self.indexOf(value) === index;
}
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.filter(onlyUnique);
}
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.filter(onlyUnique);
}
function getClasses(color, 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, 1).getValue() === color) && (lovSheet.getRange(i, 2).getValue() === fruit)) {
return_array.push(lovSheet.getRange(i, 3).getValue());
}
}
return return_array.filter(onlyUnique);
}
function getBrands(color, fruit, classParam) {
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) && (lovSheet.getRange(i, 2).getValue() === fruit) && (lovSheet.getRange(i, 3).getValue() === classParam)) {
return_array.push(lovSheet.getRange(i, 4).getValue());
}
}
return return_array.filter(onlyUnique);
}
function AddRecord(name, color, fruit, classParam, brand) {
var url = ""; //INSERT SPREADSHEET URL HERE <---------
var ss = SpreadsheetApp.openByUrl(url);
var dataSheet = ss.getSheetByName("DATA");
dataSheet.appendRow([name, color, fruit, classParam, brand, new Date()]);
}
function getUrl() {
var url = ScriptApp.getService().getUrl();
return url;
}
HTML
<!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);
};
function GetClass(color, fruit)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
classParam.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
classParam.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
classParam.appendChild(option);
});
}).getClasses(color, fruit);
};
function GetBrand(color, fruit, classParam)
{
google.script.run.withSuccessHandler(function(ar)
{
console.log(ar);
brand.length = 0;
let option = document.createElement("option");
option.value = "";
option.text = "";
brand.appendChild(option);
ar.forEach(function(item, index)
{
let option = document.createElement("option");
option.value = item;
option.text = item;
brand.appendChild(option);
});
}).getBrands(color, fruit, classParam);
};
</script>
</head>
<body>
<h1>Web App Dependent Drop Down</h1>
<?var url = getUrl();?>
<form method="post" action="<?= url ?>" >
<!-- name -->
<label style="font-size: 20px" >Name</label><br>
<input type="text" name="name" style="font-size: 20px" /><br><br>
<!-- color -->
<label style="font-size: 20px" >Colors</label><br>
<select name="color" id="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>
<!-- fruit -->
<label style="font-size: 20px" >Fruits</label><br>
<select name="fruit" id="fruit" style="font-size: 20px" onchange="GetClass(color.value, this.value)" >
<option value="" ></option>
<? for(var i = 0; i < fruits.length; i++) { ?>
<option value="<?= fruits[i] ?>" ><?= fruits[i] ?></option>
<? } ?>
</select><br><br>
<!-- class -->
<label style="font-size: 20px" >Classes</label><br>
<select name="classParam" id="classParam" style="font-size: 20px" onchange="GetBrand(color.value, fruit.value, this.value)" >
<option value="" ></option>
<? for(var i = 0; i < classParams.length; i++) { ?>
<option value="<?= classParams[i] ?>" ><?= classParams[i] ?></option>
<? } ?>
</select><br><br>
<!-- brand -->
<label style="font-size: 20px" >Brand</label><br>
<select name="brand" id="brand" 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>
Sample Data
Web App
Output
- Gemini 1.0 Pro Vision has been deprecated on July 12, 2024. Consider switching to different model, for example gemini-1.5-flash
- How to use Apps script to move a lot of data rows at same time to different spreadsheet automatically base on time
- How can I see the full server response for this API error message in Google Scripts?
- Can I list all my scripts without any other types of documents in Google Drive?
- Custom Menus Google Apps Script
- Google Sheets Index Sidebar of all tabs - add search functionality
- HTML form visible or hidden based on checkbox condition
- How do I compare new IDs with a column of existing IDs and allowcate a timestamp if two IDs match on Google Sheets?
- Google script Display array with rows of different sizes
- Return index of cell that contains match on two strings
- How are list the permissions of shared drive in app-script
- Google Apps Script: Finding an element in a Column and returning the corresponding row
- How to run a web application in Android mobile Chrome browser
- Active VS Effective User for Google Sheets
- Resolving TypeError when trying to use findIndex
- I am not able to extract URL from these cell values using Google Apps Script
- How to type hint Google Types in Google Scripts?
- Getting started with google app script - can not retrieve form submission parameters
- How to execute a Google Apps Script ONLY on the edited row
- How to copy the last row in one table and append to another table in the same GSheet (the same tab)
- Google Apps Scripting variables and values
- Replace duplicate row with new data in Google Sheets using Apps Script
- Google App Script: DateTimePicker object shows time in UTC time zone
- Incorrect result of iferror formula via Apps Script
- How to Extract URLs from HYPERLINK in Google Sheets
- Trouble downloading files in Google Apps Script web app after Chrome update
- Multiple scripts combined to one script will not run
- Compare two cells and input timestamp in third if they do not match Google Apps Script
- Show next row as soon as a cell is filled, using a macro
- Comparing Dates between 2 Sheets, Not Evaluating as true when conditions are met