Global variables in Google Script (spreadsheet)

Question

I've been trying to get some functions in a Google apps script (inside a spreadsheet) to modify a global variable, but I can't seem to figure it out.

Basically I want to declare a variable (in this case "globalTestVar"), and every time one of the two functions (globalVarTestFunctionOne and two) launches this variable should increment by one.

The problem is that the variable is being declared again every time a button is pressed even though the if(typeof(globalTestVar) == 'undefined')-statement should take care of that.

I'm used to Objective C and Java where I can declare my variables in the beginning, and modify these variables anywhere in the code.

I'm sorry if this is a basic question but I've been googling for hours and I just can't get it to work.

Here is the code:

logstuff("outside");


function logstuff(logInput){
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
var lastRow = sheet.getLastRow() + 1;
sheet.getRange("A"+lastRow).setValue(logInput);
return;
}


if (typeof(globalTestVar) == 'undefined') {
logstuff('declaring global variable');
globalTestVar = 0;

} else {
logstuff('global variable has been declared'); 
}



function globalVarTestUIFunction() {
var app = UiApp.createApplication().setTitle('Test UI');
var doc = SpreadsheetApp.getActive();
var formPanel = app.createVerticalPanel();


var buttonF1 = app.createButton('F1');
var buttonbuttonF1ClickHandler = app.createServerClickHandler("globalVarTestFunctionOne");
buttonF1.addClickHandler(buttonbuttonF1ClickHandler);
buttonbuttonF1ClickHandler.addCallbackElement(formPanel);

var buttonF2 = app.createButton('F2');
var buttonbuttonF2ClickHandler = app.createServerClickHandler("globalVarTestFunctionTwo");
buttonF2.addClickHandler(buttonbuttonF2ClickHandler);
buttonbuttonF2ClickHandler.addCallbackElement(formPanel);


app.add(formPanel);

formPanel.add(buttonF1);
formPanel.add(buttonF2);


doc.show(app);

return app;
}



function globalVarTestFunctionOne() {
logstuff('globalVarTestFunctionOne');
globalTestVar++;
logstuff('Value of globalTestVar: ' + globalTestVar);
}

function globalVarTestFunctionTwo() {
logstuff('globalVarTestFunctionTwo');
globalTestVar++;
logstuff('Value of globalTestVar: ' + globalTestVar);
}

Output:

• outside3
• declaring global variable
• outside3
• declaring global variable
• globalVarTestFunctionOne
• Value of globalTestVar: 1
• outside3
• declaring global variable
• globalVarTestFunctionTwo
• Value of globalTestVar: 1

I've written my own function "logstuff" to print out messages because I don't like the built in Logger.log-function.

Thank you!

Pompey

Answer 1

You won't like this: global variables in GAS are static - you can't update them and expect them to retain their values. I, too, googled this for hours.

You can use CacheService or ScriptDB as possible storage for this sort of problem. CacheService is quick and easy to use, but limited because the cache will expire eventually. I haven't tried ScriptDB

Indeliberate

Well, that's disturbing. There has to be a way to use some sort of global variables. I thought about a work around and use a "Variables"-sheet to save/modify and get global variables. But that is not very elegant, nor is it efficient. – Pompey

I used CacheService to do what I needed, but that was short term storage for performance improvement. You might find ScriptDB is better, but I haven't tried that. – Indeliberate

Yes, cacheService works good for strings and numbers, but is it possible to store arrays with cacheService? – Pompey

No - you can't store arrays in the cache, and worse: JSON.stringify() doesn't seem to return a JSON string, and doesn't report an error either. You can store arrays in ScriptDB, though. Whether that's better than storing them as ranges in a Sheet will depend on what you're doing with them. – Indeliberate

Guys, this isn't quite what's happening. Globals aren't static, they are global, which refers to their scope. Within an execution context, a global variable is available to all blocks of code. When you are pressing a button in a client-side script (UiApp, HtmlService), a new server-side context is created to run the appropriate handler - and that new context has a unique set of "global" variables. Strictly speaking, your variable isn't being "redeclared", and the comparison to "undefined" only means it doesn't have a value assigned in this context. – Deeply

To see how to store arrays in ScriptDb, see this answer. – Deeply

@Deeply Nice explanation on the globals issue. Unfortunately the effect is the same :( I looked at ScriptDB this week for a different project, but Google Apps Script has so many holes in it I've elected to go the Javascript/PHP/MySQL route. – Indeliberate

thanks @Deeply for your explanation on globals server/client side, very helpful as usual. – Jardiniere

Answer 2

PropertiesService -> Properties

Currently (2015), I think the way to go is using the class PropertiesService and the return type Properties.

About the global scope

As I understand it, each new invokation of a script function (time triggers, user clicked a menu item, pressed a button, etc), will lead to a new full parse of the script without memory of earlier executions unless they were persisted somehow (in a spreadsheet range or using Properties for example).

Rivet

Answer 3

Despite CacheService would work, it has 6 hours maximum life time. This can be solved by using PropertiesService instead, as @consideRatio mentioned.

Example wrapper might be(injects variables into global context)

/* Wrap variable inside global context */
const Globals = {
  global:this,
  items:{},
  /* Can be 'User', 'Script', or 'Document'
  ** Script - same values for all executions of this script
  ** User - same values for all executions by same user
  ** Document - same values for any user for same document
  ** Check reference for details.
  ** https://developers.google.com/apps-script/guides/properties
  **
  */
  context:'Script', 
  /* Get property service based on requested context */
  get service() {
    return PropertiesService['get' + this.context + 'Properties']()
  },
  /* Assign implementation */
  set(name, value = null) {
    this.service.setProperty(name, JSON.stringify(value));
    return value;
  },
  /* Read implementation */
  get(name) {
    var value = this.service.getProperty(name);
    return value !== null? JSON.parse(value) : null;
  },
  /* Shortcut for setter of complex objects */
  save(name) {
    this.set(name, this.items[name]);
  },
  /* Save all */
  flush(name) {
    Object.keys(this.items).map(name => this.save(name));
  },
  /* Delete implementation */
  reset(name) {
    this.service.deleteProperty(name);
    delete this.items[name];
  },
  /* Add to global scope */
  init(name, default_value = null) {
    if(! this.items.hasOwnProperty(name)) {
      if(this.service.getProperty(name) === null)
        this.set(name, default_value);
      this.items[name] = this.get(name);
      Object.defineProperty(this.global, name, {
        get: () => {return this.items[name]},
        set: (value) => {return this.items[name] = this.set(name, value)},
      })
    }
    return this.items[name];
  }
}

After registering with Globals.init, variables can be used just like ordinary vars. This works with primitives, however, since watchers aren't supported to complex objects, they have to be flushed on script end or explicitly.

/* In case you need to start over */
function restart_simulations() {
  Globals.reset('counter');
  Globals.reset('state');
  
  test_run();
}

function test_run() {
  /* After running init once, you can use global var as simple variable */
  Globals.init('counter', 1); // Required to use "counter" var directly, as simple variable
  
  /* Complex objects are also accepted */
  Globals.init('state', { logined: false, items: [] }); 
  
  /* Using primitives is simple */
  Logger.log('Counter was ' + counter);
  counter = counter + 1;
  Logger.log('Counter is now ' + counter);

  /* Let's modify complex object */
  Logger.log('State was ' + JSON.stringify(state));
  
  state.items.push(state.logined ? 'foo' : 'bar');
  state.logined = ! state.logined;
  
  Logger.log('State is now ' + JSON.stringify(state));
  
  /* Unfortunately, watchers aren't supported. Non-primitives have to be flushed */
  /* Either explicitly */
  //Globals.save('state');  
  
  /* Or all-at-once, e.g. on script end */
  Globals.flush();  
}

Here's what is preserved among different 3 runs

First run:

[20-10-29 06:13:17:463 EET] Counter was 1
[20-10-29 06:13:17:518 EET] Counter is now 2
[20-10-29 06:13:17:520 EET] State was {"logined":false,"items":[]}
[20-10-29 06:13:17:523 EET] State is now {"logined":true,"items":["bar"]}

Second run:

[20-10-29 06:13:43:162 EET] Counter was 2
[20-10-29 06:13:43:215 EET] Counter is now 3
[20-10-29 06:13:43:217 EET] State was {"logined":true,"items":["bar"]}
[20-10-29 06:13:43:218 EET] State is now {"logined":false,"items":["bar","foo"]}

Third run:

[20-10-29 06:14:22:817 EET] Counter was 3
[20-10-29 06:14:22:951 EET] Counter is now 4
[20-10-29 06:14:22:953 EET] State was {"logined":false,"items":["bar","foo"]}
[20-10-29 06:14:22:956 EET] State is now {"logined":true,"items":["bar","foo","bar"]}

You can check working example here.

Demo script

Mousetail