Where to store script configuration?

Question

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

Note that questions starting with "Best way"( what is best? Fast? Short lines of code?) or "In your experience" are likely to be closed as "opinion based". If you can reword your question to focus on a specific aspect and remove "opinion" based words, this question may remain. — TheMaster, Oct 17 '22 at 14:29

TheMaster · Accepted Answer · 2022-10-17T14:53:45.663

3

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

edited Oct 17 '22 at 14:53

answered Oct 17 '22 at 14:30

TheMaster

45,448
6
62
85

thanks for reply @TheMaster, I updated the question to be less opinion based and not less blur – Puygrenier Solann Oct 17 '22 at 14:52

score 0 · Answer 2 · answered Oct 17 '22 at 15:07

0

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

answered Oct 17 '22 at 15:07

Cooper

59,616
6
23
54

Where to store script configuration?

2 Answers2

References: