Where to store script configuration?-CodePudding

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
    }
  }

CodePudding user response：

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:

Tag info page for "Best practices"
Long processing time likely due to getValue and cell inserts

CodePudding user response：

If you have large data sets it's often worth your time to check out the difference of using Sheets API as opposed to SpreadsheetApp. It's not necessarily always true and SpreadsheetApp is much easier to use in my opinion.

Read Best Practices