Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps

In our blog, we have published several tutorials on creating web apps with data entry forms using Google Apps Script. Data entry forms significantly enhance the accuracy and speed of your data entry tasks. When creating these forms, you have several options for dropdown lists: hardcoding the items directly into your code or populating them from a range of data in your Google Spreadsheet. A particularly useful feature is the cascading dropdowns, which streamline user input by dynamically filtering available options based on previous selections, thereby improving efficiency and accuracy.

Google Apps Script Web Apps

Google Apps Script lets you build web apps right in your web browser, without needing to download any software. These apps can interact with your Gmail, Calendar, Drive, and Sheets, and you can access them from any device. They’re great for automating tasks, adding features to existing Google services, and creating custom interfaces to work with your data. You can publish them as standalone web pages or integrate them directly into Google Workspace apps like Sheets.

“If you’re new to Google Apps Script web apps, I recommend checking out the list of related articles published on our blog. You can find them here: https://www.bpwebs.com/tag/web-app/.

What are Cascading Dropdowns?

Cascading dropdowns, also known as a dependent dropdown list, is a type of input control where the selection made in one dropdown list determines the options available in another dropdown list. This is particularly useful when the items in the second list are logically dependent on the selection made in the first list.

Advantages of Cascading Dropdowns

Cascading dropdowns in data entry forms offer several advantages:

• Improved User Experience: Users can quickly find relevant options, as the dropdowns dynamically filter based on previous selections. This reduces the time and effort needed to complete the form.
• Reduced Errors: By limiting the options available in subsequent dropdowns based on prior selections, the likelihood of users making incorrect or incompatible choices is minimized. This leads to more accurate data entry.
• Enhanced Data Validation: Cascading dropdowns enforce logical relationships between data entries. For instance, if a user selects a specific category, only subcategories within that category are displayed, ensuring the consistency of the data collected.
• Streamlined Data Management: Structured and interdependent dropdowns make it easier to manage and analyze the collected data. For example, it’s simpler to sort and filter data by category, subcategory, and product type when the relationships are clearly defined.

Populating Cascading Dropdown list with Google Sheets Data

Hardcoding the options list within your code creates maintenance issues. Any future changes to the list require editing and redeploying the code. This becomes cumbersome, so it’s best to define the options list outside the code for easy updates.

You can achieve this by populating the dropdown list from the data stored in the Google Sheets. So, you can add, remove, or modify items in the dropdown list easily by modifying the Google Sheets.

Fetching All Data and Filtering on Client-Side

Populating cascading dropdowns from Google Sheets data typically involves fetching data each time a dropdown option changes. To reduce this delay, you can fetch all the data initially when the web app loads and then filter it on the client-side (user’s browser) as the user interacts with the dropdowns.

This approach offers a significant performance boost. However, there’s a trade-off: changes made to the data in Google Sheets won’t be reflected in the form until the user refreshes the page. This means users might see outdated options until they refresh.

Creating Multi-Level Cascading Dropdowns in Google Apps Script Web Apps

This tutorial dives into creating cascading dropdowns for your Google Apps Script web app. While I won’t be covering the overall setup of a web app here, I’ll provide the necessary code snippets to integrate cascading dropdowns into your existing project.

When building Google Apps Script web apps, you can connect them to Google Sheets using bound or unbound scripts. Bound scripts are attached to a specific sheet and accessed through its menu, making them ideal for tasks focused on that sheet. Unbound scripts are more flexible, not tied to any sheet initially, and can connect to any sheet using a unique ID, allowing your web app to work with various sheets.

While both the SpreadsheetApp method and the Google Sheets API can be used to build your Google Apps Script web app, they differ in how they retrieve data from Google Sheets. The explanation of cascading dropdown lists below applies to both methods. However, keep in mind that you’ll need to adapt the data retrieval process based on your chosen method and ensure the data is formatted correctly for use with cascading dropdowns on the client side.

Live demo of the web app with cascading dropdowns

You can access the live demo, the Google Apps Script code, and the Google Spreadsheet with the dropdown list data from the following link. You can continue reading for more details and code explanations.

Preparing the dataset

The following table illustrates how to structure your data in a Google Spreadsheet to populate cascading dropdown lists within your Google Apps Script web app. Each row represents a product, and the columns represent the hierarchical levels of your product categorization (e.g., Category, Subcategory, Product Type, Product Model).

Here’s a breakdown of the table:

• Category: The broadest product classification (e.g., Computers, Peripherals, Software).
• Subcategory: A more specific classification within a category (e.g., Desktops, Monitors, Operating Systems).
• Product Type: A further refinement of the subcategory (e.g., All-in-One, LCD, Windows).
• Product Model: The most granular level, representing a specific product variant (e.g., Model A, Model O, Version 1).

The code you’ll implement will process this data and only display unique entries within each dropdown list. This ensures users are presented with relevant options based on their selections in previous dropdown menus.

The Code Snippet

This code snippet is designed to demonstrate creating a dropdown list as a standalone example. You can easily integrate the relevant code sections into your existing project to build the dropdown functionality.

This code snippet utilizes the Google Sheets API to retrieve data from your Google Sheets. While the data retrieval method only impacts the getDropdownListData function within the Code.gs file, the rest of the code should remain compatible with other data retrieval methods (like the SpreadsheetApp method).

Explaining the code

The file structure in the above code follows a pattern common to many Apps Script web apps I’ve explained in this blog. It consists of files like Code.gs, Index.html, Form.html, Css.html, and JavaScript.html. Essentially, Css.html, Form.html, and JavaScript.html contain code sections that should be included in the main Index.html file. To enhance code readability and maintainability, these sections are separated into individual files and then included back into Index.html using the include() function within Code.gs.

• The getDropdownListData() function in Code.gs retrieves the entire dropdown data table from the Google Sheet. The constant DATA_RANGE doesn’t explicitly define the last row of the data range (e.g., Helper!A2:D). Leaving the last row open-ended allows you to add more items to the cascading dropdown list later without modifying the code itself.
• The Form.html file defines the HTML structure for the cascading dropdown fields. Notice that we haven’t included the option lists within these dropdowns yet. The JavaScript code will dynamically populate these options later. You can incorporate these code sections into your Apps Script web app project as needed.
• The getDropdownListData function in JavaScript.html is called by the initialize function when the page loads. It uses google.script.run to call the server-side function with the same name (getDropdownListData) in the Code.gs file. Upon successful execution, it retrieves the dropdown data.
• The createCategoryDropdown function populates the category dropdown with unique categories extracted from the retrieved data. It adds a default “Select Category” option and attaches an event listener that triggers subsequent dropdown population based on the selected category.
• The createSubCategoryDropdown, createProductTypeDropdown, createProductModelDropdown functions follow a similar pattern to createCategoryDropdown. They populate the respective sub-category, product type, and product model dropdowns based on the selection from the previous dropdown and filter the data accordingly
• The populateDropdown is a helper function used by all the dropdown creation functions. It clears existing options in a dropdown, adds a default option, and then appends each option from the provided list.
• The resetDropdowns function is used to clear the content of all subsequent dropdowns when a selection is made in any dropdown. This ensures that only relevant options are displayed based on the user’s selections.

Modification of the code to work with the SpreadsheetApp method.

As mentioned earlier, you’ll only need to modify the getDropdownListData function (in Code.gs file) to use your cascading dropdown list with the web app if you plan to use the SpreadsheetApp method for connecting to Google Sheets. Here’s the modified version of the function that works with SpreadsheetApp:

function getDropdownListData(){
  try{
    let sheet = SpreadsheetApp.openById(SPREADSHEET_ID);
    let data = sheet.getDataRange().getValues();
    let dropdownData = data.slice(1);
    return dropdownData;
  } catch(err){
    console.log('Failed with error %s', err.message);
  }
}

Wrapping Up

This blog post tackled creating cascading dropdown lists for Google Apps Script web apps. These enhance user experience by filtering options based on previous selections, leading to faster and more accurate data entry.

The guide provided a step-by-step approach, including how to populate the dropdowns from a Google Sheet and filter data on the client-side for optimal performance. The included code snippet serves as a foundation you can integrate into your project. It also explains modifications for using the SpreadsheetApp method to connect to Google Sheets. With this knowledge, you can now implement cascading dropdowns in your web apps to streamline user interaction and data collection.