Creating a Settings Section in Google Sheets Apps Script Projects

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:

Watch the Video Tutorial

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.

/**
 * Constants for boolean settings values.
 */
const YES = 'Yes';
const NO = 'No';

/**
 * The Settings class provides a way to manage script parameters/settings 
 * directly within a Google Sheet, making it accessible for non-technical users.
 */
class Settings {
    
    /**
     * Constructor initializes the settings sheet and map.
     * @param {string} [sheetName="Settings"] - Name of the sheet where settings are stored.
     */
    constructor(sheetName = "Settings") {
        this.sheetName = sheetName;
        this.spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
        this.settingsSheet = this.spreadsheet.getSheetByName(sheetName);
        this.settingsMap = this.initSettingsMap();
    }

    /**
     * Initializes the settings sheet if it doesn't exist.
     */
    init() {
        if (!this.settingsSheet) {
            this.settingsSheet = this.spreadsheet.insertSheet(this.sheetName);
            this.settingsSheet.appendRow(['Setting', 'Value']);
            this.settingsSheet.getRange('1:1').setFontWeight('bold');
        }
    }

    /**
     * Initializes the settings map from the sheet data.
     * @returns {Map} - A map of settings.
     */
    initSettingsMap() {
        if (!this.settingsSheet) return new Map();

        const data = this.settingsSheet.getDataRange().getValues();
        const map = new Map();

        for (const [key, value] of data) {
            map.set(key, value);
        }

        return map;
    }

    /**
     * Sets or updates a setting in the sheet.
     * @param {string} settingName - Name of the setting.
     * @param {string} settingValue - Value of the setting.
     */
    setSetting(settingName, settingValue) {
        const rowIndex = [...this.settingsMap.keys()].indexOf(settingName) + 1;

        if (rowIndex > 0) {
            this.settingsSheet.getRange(rowIndex, 2).setValue(settingValue);
        } else {
            this.settingsSheet.appendRow([settingName, settingValue]);
        }

        this.settingsMap.set(settingName, settingValue);
    }

    /**
     * Retrieves a setting's value from the map.
     * @param {string} settingName - Name of the setting.
     * @returns {string|null} - Value of the setting or null if not found.
     */
    getSetting(settingName) {
        return this.settingsMap.get(settingName) || null;
    }

    /**
     * Retrieves a boolean setting's value.
     * @param {string} settingName - Name of the setting.
     * @returns {boolean|null} - True if 'Yes', False if 'No', or null if neither.
     */
    getBooleanSetting(settingName) {
        const settingValue = this.getSetting(settingName);

        if (settingValue === YES) return true;
        if (settingValue === NO) return false;

        Logger.log(`Setting value is not ${YES} or ${NO}: ${settingName}`);
        return null;
    }

    /**
     * Sets a setting in the script properties.
     * @param {string} settingName - Name of the setting.
     * @param {string} settingValue - Value of the setting.
     */
    setSettingInScriptProperties(settingName, settingValue) {
        PropertiesService.getScriptProperties().setProperty(settingName, settingValue);
    }

    /**
     * Retrieves a setting from the script properties.
     * @param {string} settingName - Name of the setting.
     * @returns {string} - Value of the setting.
     */
    getSettingFromScriptProperties(settingName) {
        return PropertiesService.getScriptProperties().getProperty(settingName);
    }
}

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.

Key Features

Initialization & Sheet Creation

First, create an instance of the Settings class and initialize the settings sheet.

const settings = new Settings();
settings.init();

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

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Settings')
      .addItem('Show Message', 'showMessage')
      .addToUi();
}


function showMessage() {
  if (settings.getBooleanSetting("ShowModal")) {
      const message = settings.getSetting("ModalMessage");
      SpreadsheetApp.getUi().alert(message);
  }
}

Now, when the “Show Message” menu button is pressed, you can check the settings and display the modal accordingly.