Global Variable value not usable in multiple functions

Question

Using Google Apps Script I'm trying to create a global variable (e.g. an array) that can be used in multiple functions, but I can't seem to find the answer anywhere and I need it for my Google Spreadsheet to work.

Code:

var infoSheetArray = null;

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Fetch Info', 'grabInfo')
      .addItem('Run Program', 'itemSetup')
      .addToUi();
}

function grabInfo() {
  var infoSheet = SpreadsheetApp.openByUrl('....');
  infoSheetArray = infoSheet.getSheetValues(1, 1, infoSheet.getLastRow(), 10);
}    

Answer 1

In your code, infoSheetArray is a variable with global scope, meaning that it is accessible by all code in your program. What isn't so obvious is that your program is running multiple times & in different "machines", and that's why it may appear that infoSheetArray is getting reset.

Each separate execution of a script is done in a new execution instance, and each of those have their own set of "globals". In your example snippet, for example, onOpen() is an automatic trigger function invoked independently when the spreadsheet is opened in the web editor. You have two other functions, grabInfo() and itemSetup() (not shown) that get invoked independently via user menu extensions.

When any of these three functions are invoked independently, an execution instance is created for it, your program is loaded, and then execution begins. All global statements (e.g. var infoSheetArray = null;) are evaluated first, followed by the specifically triggered function. Any changes made to global variables will persist only within the execution instance the change happened in, and only for as long as that instance is required. Once the function completes, the instance is blown away.

If you need to persist values between execution instances, you need to use some storage method such as the Properties Service or an External Database.