CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Davey
Davey

Reputation: 119

Is it viable to have all variables inside a settings Google Sheet for multiple sheets scripts?

I have multiple app scripts/sheets which use the same values and am wanting to create one place to store all the variables as a global settings page (sheet names/ranges/ font settings/columns to sort, etc) in a single sheet and have app script read from them for each page.

If I were to do this would I have a severe performance decrease? Every edit of the sheet would be triggering a function. I am unsure if it would be trying to fetch all the values every single time a function was run (not sure if it would keep pages that are not changed in cache on their servers)

More info on this:

I will be having many people setup their own copies of all the pages and want to make it as easy as possible by only giving them 1 sheet to look at and edit for the whole system.

Example:

  var sheetName1 = "Purchases";
  var sheetRange1 = "A3:N";
  var font= "Roboto";
  var font color= "#434343";
  var font size= 12;

would change to something like

  var sheetName1 = SettingsPageCell1;
  var sheetRange1 = SettingsPageCell2;
  var font= SettingsPageCell3;
  var font color= SettingsPageCell4";
  var font size= SettingsPageCell5;

Example of my script getting a cell. I am not sure if this will start causing large delay's eventually.

  var ssa = SpreadsheetApp.openByUrl('spreadsheet_url');
  var targetSheet = ssa.getSheetByName("Stylesheet");
  var test = targetSheet.getRange("B4").getValue();

Settings Google Sheet page

|--------------------------------|
|           Settings Page        |
|--------------------------------|
| Page 1 sheet name: | Purchases |
| Page 1 range       | A3:N      |
| Page font          | Roboto    |
| Font color         | #434343   |
| font size          | 12        |

Thanks

Upvotes: 1

Views: 236

Answers (1)

Alan Wells
Alan Wells

Reputation: 31300

It depends upon how often those centralized settings need to be accessed. If it's once per user session, then I doubt it would be perceptible. You can use Cache Service to store values between function runs. A global variable looses it's value and the end of every stack completion. You probably want to retrieve the values once per user session, and have those values persist and be accessible for a certain amount of time. You can do that with Cache Service. Cache Service "times out". When the time runs out, the settings and values cease to exist.

The script in each spreadsheet could get the values from the central sheet, then put the values into that spreadsheet's Cache. Then you really don't need to worry about performance issues with that situation. Your code should check for the value in Cache, and if it's not there, retrieve it from the central spreadsheet again. Set the cache time for however long you think a typical user session will last.

Upvotes: 1

Related Questions