Search code examples
javascriptgoogle-sheetsgoogle-apps-scripttriggers

Cannot reassign a global variable within an onEdit function

So I'm trying to update a global variable from within an onEdit function yet it will not reassign. It seems to retain its original value.

The global variable is declared at the very top of the script so it shouldn't be hoisting issues as far as I know.

var strategy = "empty";

Within the onEdit there is:

test1.setValue(strategy);
strategy = "TEST";
test2.setValue(strategy);

The test 1&2 references are just two different cells (in a google sheet) used to test the before and after results of the onEdit code.

The result of this shows: test1 = "empty" & test2 = "TEST"

However, when onEdit is called again, the test results are exactly the same. Despite the very last action of the onEdit being to reassign the value to "TEST", it resets to the default value of "empty" at some point before onEdit is ran again.

Has anybody else seen something like this before.... What am I missing here ?

Solution

  • The state of the script does not persist

    Every time you run any function in Apps Script (including an onEdit), think of it like this: It will concatenate all the separate script files and run everything.

    So if you have

    var globalVar = 1;

function test() {
    globalVar = globalVar + 1
    console.log(globalVar)
}

    Every time you run the function test, the result will be 2. Because everything runs again.

    If you want to persist data across the different executions, then you need to store that in a Spreadsheet, for example.

    Properties Service

    You can also take advantage of the properties service. This is a small store of information that you can persist across executions. Data that you may only want to use as a developer, and for it not to be available to a user in a Spreadsheet for instance.

    It works with getters and setters though, so its not as straightforward as simple declaring a variable.

    To set a value to persist across different executions, you would need to run this example function once.

    function initializeProperties() {
    var scriptProperties = PropertiesService.getScriptProperties();
    scriptProperties.setProperty('globalVar', 1); // setting a property
}

    Then from another function, such as an onEdit, you could:

    var scriptProperties = PropertiesService.getScriptProperties();
var oldValue = scriptProperties.getValue('globalVar') // getting a property
scriptProperties.setProperty('globalVar',  oldValue + 1)
console.log(scriptProperties.getValue('globalVar'))

    Reference