Using Configuration Data in Google App Scripts

• Introduction
• Configuration Types
• A CONFIG Tab
• Configuration Code

Combining user and general configuration to write more flexible App Scripts.

Introduction

It’s tempting to hard code configurable data into your apps script. It’s easy and quick, but also makes maintaining that script a lot harder in the future. A configurable script is more robust¹ and therefore more reliable. It adapts to change better, and will require less of your time in the future to keep it working!

Configuration Types

Typically there are two types of configurable values you might use in a script.

System Config

The first is set by the developer and stored within the source code/script itself. The most important aspects of these values is that they are declared only once and in one commonly known place (e.g. a single file or object). Following these rules ensures it is trivial for a developer to find and update a single value, instead of having to search through source-code to find and replace all instances of that configuration value.

User Config

The second type of configuration data is that which comes from the users themselves. On a complex script project, you would like want to build a custom dialog UI to prompt for (and store) configuration data. This could include a picker to select content from Google Drive, and validation logic to ensure the configuration data is acceptable (using HTML5 inputs or custom code). For a smaller project, an easier way to achieve similar functionality is to use the spreadsheet UI itself, by created a configuration tab.

A CONFIG Tab

The table above is a good example of how to create a config tab in a spreadsheet. Even a standalone script can use this technique, provided you supply the id of the sheet holding your configuration. If you are using a sheet to host your apps script, it makes sense to keep the configuration in the same place as the user interacts with your system (to save future confusion!).

The type column helps the configuration parser to understand the syntax of the value it is given. This means a string, delineated with semi-columns, can be parsed into an array as well as booleans being correctly handled as native boolean values (rather than being coerced). The description column helps your users understand the usage and impact of the configurable value, added essential documentation to your system.

Configuration Code

System Config

var config = {
  prompts : {
    to : {
      title : "Send Example / Test Email",
      desc : "Send Email to ..."
    },
    confirm : {
      title : "Please confirm",
      desc : "Do you want to send emails to %s addresses, in %s batch/es?"
    },
  },
  alerts : {
    missing_value : "Please ensure you have filled in a '%s' cell for the email",
    missing_emails : "Can't find a sheet entitled %s",
    sent : "Sent emails to %s address/es, in %s batch/es"
  }
}

A simple javascript object can act as the container for all the system configuration values. Logical organisation of values around a hierarchy makes for easier usage (e.g. config.alerts.sent). If this object is stored in a dedicated script file within your project it makes it easy to locate and update (e.g. config.gs or .config.gs if you would like at alphabetically sorted to the top of your script file list).

User Config

function loadConfig(id) {
  
  var _configSheet = id ? SpreadsheetApp.openById(id).getSheetByName("CONFIG") : SpreadsheetApp.getActive().getSheetByName("CONFIG");
  
  if (_configSheet) {
     var _data = _configSheet.getDataRange().getValues();
     for (var i = 1; i < _data.length; i++) {
       if (_data[i][0] && _data[i][1]) {
         if (_data[i][2] && _data[i][2] == "Array") {
           if (!_data[i][1] || _data[i][1] == "") {
             config[_data[i][0]] = false;
           } else {
             config[_data[i][0]] = _data[i][1].split(";");
           }
           
         } else if (_data[i][2] && _data[i][2] == "Boolean") {
           config[_data[i][0]] = (_data[i][1] === true || (_data[i][1] && _data[i][1].toLowerCase() == "true"));
         } else {
           config[_data[i][0]] = _data[i][1];
         }
       }
     }
  }
   
}

The function above will dynamically load the configuration values from your sheet into your config object. If your configuration resides in a sheet to which the code is not container-bound (e.g. a standalone script), then you will need to supply the ID of that sheet so the code can find it! The function above means you can access the values in your code as if you had declared then in the config object itself making a single, and simple, source for all the configuration across your script.

function doSomething() {
  
  loadConfig();
  if (config.LIVE) {
    // Proceed to send emails
  }

}

Remember that object properties/keys in javascript are case-sensitive, so stick with a casing scheme in your configuration sheet (ALL CAPS is a sensible option).