Using Configuration Data in Google App Scripts

12 Oct 2017
coding 5 minute read

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


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 robust1 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.


FROMjohn.doe@example.comStringThis is the name that will appear in the ‘from’ part of the email (before your email address)
BATCH_SIZE20IntegerIf you leave this blank, or use a value of 1 or lower, an email will be sent to each address in the EMAILS tab. If you set this to 2 or higher, emails will be sent in BCC batches of that number.
SOURCEEmailsStringThe name of the sheet to take the email list from.
DATA_ROW4IntegerThe row at which data starts in your source sheet
LIVETRUEBooleanWhether the script is ‘live’ and sending emails, or in a testing / debug mode.; archive@example.comArrayList of addresses to CC every email to (for archive / information).

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. or 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() {
  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).

  1. Fragility or robustness (in this case) refers to the ability of the code to tolerate change, or be susceptible to bugs caused by inconsistent/repetitive statements or configuration values. 

Tagged Google, Sheet, Javascript

Related Google Calendars on the Web (01 Sep 2018), Protecting Data with Google Drive (14 Aug 2018), Dealing with confidential information in schools (24 Jul 2018)