Skip to content

Instantly share code, notes, and snippets.

@bpwebs

bpwebs/#Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps

Last active August 31, 2024 16:20
Show Gist options
  • Save bpwebs/bf05c2adb2e61fd60931a2d9f1cd7c5c to your computer and use it in GitHub Desktop.
Save bpwebs/bf05c2adb2e61fd60931a2d9f1cd7c5c to your computer and use it in GitHub Desktop.
Download ZIP
Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps
Raw
#Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps
Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps
Raw
Code.gs
/**
* Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps
* bpwebs.com
*/
//CONSTANTS
const SPREADSHEET_ID = "1YjWPUh0aVMivLVPlNeqwGtWLEZXCwCfSUS_bfH4xfPo";
const DATA_RANGE = "Helper!A2:D";
/**
* Serves cascading dropdown example page.
* @return {HtmlOutput} Rendered HTML.
*/
function doGet() {
let template = HtmlService.createTemplateFromFile("Index");
let html = template.evaluate().setTitle("Cascading Dropdown Example");
html.setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
html.addMetaTag('viewport', 'width=device-width, initial-scale=1');
return html;
}
/**
* Gets dropdown list data from a Google Sheet range.
*
* @return {Array} List data from sheet range.
*/
function getDropdownListData() {
try {
let result = Sheets.Spreadsheets.Values.get(SPREADSHEET_ID, DATA_RANGE);
let dropdownData = result.values;
return dropdownData;
} catch (err) {
console.log('Failed with error %s', err.message);
}
}
/**
* Includes the content of an external HTML file.
*
* @param {string} fileName The name of the HTML file to include.
* @returns {string} The HTML content of the file.
*/
function include(filename) {
return HtmlService.createHtmlOutputFromFile(filename).getContent();
}
Raw
Css.html
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
Raw
Form.html
<h2 class="mt-3">Cascading Dropdown Example</h2>
</br>
<form id="myForm">
<div class="col-md-6">
<label for="category" class="col-sm-6 col-form-label">Category:</label>
<select id="category" name="category" class="form-select form-select" required>
<!-- List of options -->
</select>
</div>
<div class="col-md-6">
<label for="subCategory" class="col-sm-6 col-form-label">Sub Category:</label>
<select id="subCategory" name="subCategory" class="form-select form-select" required>
<!-- List of options -->
</select>
</div>
<div class="col-md-6">
<label for="productType" class="col-sm-6 col-form-label">Product Type:</label>
<select id="productType" name="productType" class="form-select form-select" required>
<!-- List of options -->
</select>
</div>
<div class="col-md-6">
<label for="productModel" class="col-sm-6 col-form-label">Product Model:</label>
<select id="productModel" name="productModel" class="form-select form-select" required>
<!-- List of options -->
</select>
</div>
</form>
Raw
Index.html
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<?!= include('Css'); ?>
<title>Cascading Dropdown</title>
</head>
<body class="bg-secondary text-light mt-3 mb-3">
<div class="container">
<div class="row">
<?!= include('Form'); ?>
</div>
</div>
<?!= include('JavaScript'); ?>
</body>
</html>
Raw
JavaScript.html
<script>
/**
* Attaches an event listener to the window's "load" event to call the `initialize` function
* when the page finishes loading.
*
* @param {Event} event The "load" event object. (Optional argument passed by the browser)
*/
window.addEventListener("load", initialize, true);
/**
* Initializes the cascading dropdown functionality by fetching the initial dropdown list data.
*/
function initialize() {
getDropdownListData();
}
/**
* Retrieves dropdown list data from the server-side.
* Upon successful retrieval, calls the `createCategoryDropdown` function to populate the category dropdown.
*/
function getDropdownListData() {
google.script.run.withSuccessHandler(createCategoryDropdown).getDropdownListData();
}
/**
* Populates the category dropdown element with unique categories extracted from the provided data.
* Adds a default "Select Category" option and attaches an event listener for selection changes.
*
* @param {Array} data The retrieved data containing dropdown options (array of arrays).
*/
function createCategoryDropdown(data) {
const categoryDropdown = document.getElementById("category");
const categoryList = new Set(data.map(entry => entry[0]));
populateDropdown(categoryDropdown, categoryList, "Select Category");
categoryDropdown.addEventListener("change", () => {
resetDropdowns(["subCategory", "productType", "productModel"]);
createSubCategoryDropdown(data, categoryDropdown.value);
});
}
/**
* Populates the sub-category dropdown element based on the selected category from the category dropdown.
* Filters the data to find sub-categories belonging to the chosen category, then adds them along with a default option.
* Attaches an event listener for selection changes.
*
* @param {Array} data The retrieved data containing dropdown options.
* @param {string} selectedCategory The currently selected category value from the category dropdown.
*/
function createSubCategoryDropdown(data, selectedCategory) {
const subCategoryDropdown = document.getElementById("subCategory");
const subCategoryList = new Set(data.filter(entry => entry[0] === selectedCategory).map(entry => entry[1]));
populateDropdown(subCategoryDropdown, subCategoryList, "Select Sub Category");
subCategoryDropdown.addEventListener("change", () => {
resetDropdowns(["productType", "productModel"]);
createProductTypeDropdown(data, subCategoryDropdown.value);
});
}
/**
* Populates the product type dropdown element based on the selected sub-category from the sub-category dropdown.
* Filters the data to find product types belonging to the chosen sub-category, then adds them along with a default option.
* Attaches an event listener for selection changes.
*
* @param {Array} data The retrieved data containing dropdown options.
* @param {string} selectedSubCategory The currently selected sub-category value from the sub-category dropdown.
*/
function createProductTypeDropdown(data, selectedSubCategory) {
const productTypeDropdown = document.getElementById("productType");
const productTypeList = new Set(data.filter(entry => entry[1] === selectedSubCategory).map(entry => entry[2]));
populateDropdown(productTypeDropdown, productTypeList, "Select Product Type");
productTypeDropdown.addEventListener("change", () => {
resetDropdowns(["productModel"]);
createProductModelDropdown(data, productTypeDropdown.value);
});
}
/**
* Populates the product model dropdown element based on the selected product type from the product type dropdown.
* Filters the data to find product models belonging to the chosen product type, then adds them along with a default option.
*
* @param {Array} data The retrieved data containing dropdown options.
* @param {string} selectedProductType The currently selected product type value from the product type dropdown.
*/
function createProductModelDropdown(data, selectedProductType) {
const productModelDropdown = document.getElementById("productModel");
const productModelList = new Set(data.filter(entry => entry[2] === selectedProductType).map(entry => entry[3]));
populateDropdown(productModelDropdown, productModelList, "Select Product Model");
}
/**
* Populates a dropdown element with the provided options and a default option.
* Clears existing options, adds the default option, and then appends each option from the list.
*
* @param {HTMLElement} dropdown The dropdown element to populate.
* @param {Set} options A set containing unique option values.
* @param {string} defaultText The text to display for the default option.
*/
function populateDropdown(dropdown, options, defaultText) {
dropdown.innerHTML = "";
const defaultOption = document.createElement("option");
defaultOption.value = "";
defaultOption.text = defaultText;
dropdown.appendChild(defaultOption);
for (const option of options) {
const optionElement = document.createElement("option");
optionElement.value = option;
optionElement.text = option;
dropdown.appendChild(optionElement);
}
}
/**
* Clears the content of all dropdowns specified by their IDs in the provided array.
*
* @param {Array} dropdownIds An array containing dropdown element IDs as strings.
*/
function resetDropdowns(dropdownIds){
for(const dropdownId of dropdownIds){
const dropdown = document.getElementById(dropdownId);
dropdown.innerHTML = "";
}
}
</script>
@bpwebs
Copy link
Author

bpwebs commented Aug 31, 2024 •
edited
Loading

Visit the blog post at https://www.bpwebs.com/cascading-dropdowns-in-google-apps-script-web-apps/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment