The Problem
In my experience with Google Sheets Apps Script projects, a common challenge has been the need to incorporate external script parameters. Think about parameters like DEFAULT_DISCOUNT_RATE, EARLY_BIRD_END_DATE, FEEDBACK_EMAIL, or NEXT_EVENT_DATE. These parameters aren’t just placeholders; they are crucial settings that can influence how a script behaves or presents data to the end user.
However, managing these parameters has often been a challenge:
Configuration Chaos: Hard-coded configurations, such as DEFAULT_DISCOUNT_RATE = “10%”, can lead to maintenance issues. Every change requires diving into the code, which isn’t ideal for non-tech-savvy people.
Lack of Transparency: For teams or projects where non-developers need insight, it’s challenging to have a clear view of active configurations. Imagine a marketing manager trying to understand why the early bird discount rate is 10% or why the feedback emails are being sent to a specific address.
Inflexibility: Changing a simple setting, like updating the NEXT_EVENT_DATE, can become tedious if buried deep within the code.
A barrier to Non-Technical Users: We certainly don’t want non-technical team members, like sales reps or event managers, to visit the script editor and risk breaking the code to change a parameter. It’s not user-friendly and poses a risk to the script’s integrity.
This need for a more intuitive parameter management system wasn’t an isolated instance but a recurring theme in many of my projects. Since I’ve always valued clean, reusable code, emphasizing not just the functionality but also the elegance, maintainability, and scalability of solutions.
The goal was clear:
Create a reusable settings page in Google Sheets, using Apps Script, where configurations are easily accessible, even to those without a coding background.
Something like this:
If you prefer watching than reading, you can watch the entire tutorial here:
A Google Sheets Apps Script Solution for User Settings
Within the realm of Google Sheets Apps Script, the Settings class emerges as a solution to address the challenges of managing script parameters. This class connects the Google Sheet with the Apps Script, enabling easy script parameter management without diving into the code.
Key Features of the Code
Dynamic Sheet Reference with Default:
The constructor method initializes the class with a reference to the active spreadsheet and a specified sheet name, defaulting to “Settings”. This provides flexibility in naming the settings sheet.
Self-Initializing Sheet:
The init() method checks if the settings sheet exists. If not, it creates one, complete with headers. This ensures that the settings sheet is always available, reducing setup steps for the user.
In-Memory Settings Map:
The initSettingsMap method creates a map of settings for faster lookups. This reduces the need for constant sheet reads, enhancing performance.
Enhanced Setting Management:
The setSetting method has been optimized to update the in-memory map and the sheet simultaneously, ensuring consistency. It also allows for finding and updating a setting by its name, making changes straightforward.
Boolean Settings Handling:
The getBooleanSetting method interprets ‘Yes’ or ‘No’ values as boolean true or false, providing a user-friendly way to handle boolean settings.
External Storage Option:
Methods setSettingInScriptProperties and getSettingFromScriptProperties offer an option to store settings in Google Apps Script’s ScriptProperties, providing an alternative storage mechanism if needed.
A User Settings Google Sheets Apps Script Example
In a typical Google Sheets Apps Script project, imagine giving the end user the ability to decide if a modal message should be displayed when they press a menu button named “Show Message”.
Additionally, they should be able to customize the message shown in the modal. Here’s how you can achieve this using the Settings class:
Initialization & Sheet Creation
First, create an instance of the Settings class and initialize the settings sheet.
Once you save your script and refresh the Google Sheet, a “Settings” sheet will be created in your Google Sheet if it doesn’t already exist.
Adding Parameters
Add a boolean parameter to decide if the modal should be shown and a string parameter for the modal message:
- “ShowModal” set to “Yes”
- “ModalMessage” set to “Welcome to our Google Sheet”
Adding a Menu Item and using the Parameters in the Script
Now, when the “Show Message” menu button is pressed, you can check the settings and display the modal accordingly.
