1

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);
}
Kos
  • 4,890
  • 9
  • 38
  • 42
mds93
  • 93
  • 1
  • 2
  • 9

1 Answers1

6

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.

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • 3
    Excellent explanation. Google should really emphasize this on the getting started page. They direct JS newcomers to the CodeAcademy JavaScript tutorial that is directed towards general web/client-side applications and therefore misleading on the issue. It's a stumbling block for many newcomers to GAS. I would suggest to the original questioner that instead of using the Properties Service, which can be finicky, try to use the spreadsheet itself to store the "global" value, if at all possible. Properties and the Cache are also constrained in size and Cache expires. – ballenf Jul 28 '16 at 03:53
  • Hi @BenFletcher , I have a question about the opposite case (ensuring variables don't get changed during simultaneous executions): https://stackoverflow.com/questions/62685394/google-apps-script-multiple-users-hitting-script-for-doget-and-dopost-can-vari/. Are you able to help? Thanks. – policenauts Jul 01 '20 at 21:46