Search code examples
javascriptgoogle-apps-scriptgoogle-sheetsarchitecture

Where to store script configuration?

I'm looking for a way to organise the configuration of my code (how the code is articluated: the architecture) in the best way possible based on:

  • speed
  • visibility
  • lines of codes
  • low coupling
  • maintainance effort
  • ...

Could you provide good patterns (several) for meeting one or several axes ?

For example, we have all created a code like bellow :

var spread = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spread.getSheetByName("Sheet1");

However if the name of the sheet change, we have to update it in every place in the code.

I have no doubt that we can greatly improve how script are articulate together. As the best personal method I found so far, It's to create a sheet call "Configuration" and set all value inside as Named Range. Then script have an init part to pipe the correct named range to variable.

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var ranges = sheet.getNamedRanges();
  if (ranges === null || ranges === "") throw ErrorMessage(`Named Range allowing to initialise project are not set for the sheet ${sheet.getName()}`)

  var ROOT_DRIVE_ID;
  var SHEET_NAME

  for (var i = 0; i < ranges.length; i++) {
    switch (ranges[i].getName()) {
      case "ROOT_DRIVE_ID": ROOT_DRIVE_ID = ranges[i].getRange().getValue(); break; 
      case "SHEET_NAME" : SHEET_NAME = ranges[i].getRange().getValue(); break
    }
  }
Solution

  • Touching sheet again and again, like what the script is doing, will slow your script considerably. You can store configuration in a

    • Global object:
    const CONFIG={
  ROOT_DRIVE_ID : "",
  SHEET_NAME : ""
}

    This is faster on both read and writes, easy to configure manually but not from a sheet.

    • Cache service:

    This is also faster, but not easy to configure manually either. And cache has a expiration time.

    • Properties service:

    This is also faster. It's also easy to configure manually

    References: