Global variable defined in function appears not defined?

Question

I'm writing a script for Google Spreadsheets, I want to have my headers index available globally throughout the script.

According to the theory, I should be able to define global variables from within a function.

function testFunc() {
testVar = 1;       // `testVar` is a Global variable now
}

In my code it looks more or less like this:

function getHeaders() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var headers = data[0]

  headerIdIndex = headers.indexOf("ID")
  headerNameIndex = headers.indexOf("First-Last")
}

However, later on in my code, when I call up the variable headerNameIndex, it appears undefined:

function tellMeNames() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  for (var i = 1; i < data.length; i++) {
    Logger.log("Name: " + data[i][headerNameIndex])
  }
}

Reference Error

So what am I doing wrong? Thanks.

Are you sure that `getHeaders()` is being called before `tellMeNames()`? If not, then the variable would not be set correctly. — GregL, Oct 14 '14 at 03:52
Yest it is, as I'm running the function from the menu in the Google Script editor, running `getHeaders()` first and then `tellMeNames()`. In the end I want `getHeaders()` to be called by `onOpen()` and have the global variables available throughout the script. — neydroydrec, Oct 14 '14 at 03:57
possible duplicate of [Accessing global variable in UI handler Google Spreadsheet Script](http://stackoverflow.com/questions/21950911/accessing-global-variable-in-ui-handler-google-spreadsheet-script) — GregL, Oct 14 '14 at 04:05
See [this question](http://stackoverflow.com/questions/21950911/accessing-global-variable-in-ui-handler-google-spreadsheet-script) and [this question](http://stackoverflow.com/questions/17375360/global-variables-in-google-script-spreadsheet) for possible solutions. The key problem seems to be that every time you invoke a function from the menu in Google Script editor, a new global context is created. Hence you can't reference the global set in the first context from the second context. — GregL, Oct 14 '14 at 04:08

score 1 · Answer 1 · answered Oct 14 '14 at 04:06

1

I've found a solution I believe, but I'm not sure it is the "proper" way to do this.

I'm simply defining the global variable above in the script:

headerNameIndex = SpreadsheetApp.getActiveSheet().getDataRange().getValues()[0].indexOf("First-Last")

Which seems to work.

I had wrongly assumed it would only work if it was defined in the onOpen() function.

answered Oct 14 '14 at 04:06

neydroydrec

6,973
9
57
89

Using the [`PropertiesService`](https://developers.google.com/apps-script/reference/properties/properties-service) to store the values instead of global variables may be more of a "proper" way to do it. Or as Vivek mentioned, using Script-DB. – GregL Oct 14 '14 at 04:11

score 1 · Accepted Answer · edited May 23 '17 at 11:57

Well, I was going through some relevant SO posts on defining a global variable in Google Apps Script and from this answer it turns out you can not update global variables inside a handler function i.e. Global Variables in GAS are static.

Now, it depends on your specific use case but I am assuming since you have defined a function getHeaders(), you would want to make a call to it, more than once. If however that's not the case, then you can simply declare and initialize those variables outside the scope of all functions as global variables and read them in any other functions.

So you might want to try something like this in your case (haven't tested the following code):

 var sheet = SpreadsheetApp.getActiveSheet();
 var data = sheet.getDataRange().getValues();
 var headers = data[0]

 headerIdIndex = headers.indexOf("ID")
 headerNameIndex = headers.indexOf("First-Last")

 ....

 function tellMeNames() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
 for (var i = 1; i < data.length; i++) {
  Logger.log("Name: " + data[i][headerNameIndex])
 }
}

If however you want to store and update global variables in handler functions, you might want to give Script-DB a try. Hope this gets you started in the right direction.

@Benjamin, please accept a suitable answer so that others in a similar problem might also benefit out of it. Thanks — Vivek Pradhan, Oct 15 '14 at 06:49
it is not correct that "you cannot update global variables inside a hander". that's not the reason it fails. You *can* update them from anywhere. its just that the entire context gets reset on every time you reach an entry point to the script. thus later calls from another entry point will not see any globals not yet created at that point. use cacheservice together with propertiesService. — Zig Mandel, Nov 12 '14 at 22:08

Global variable defined in function appears not defined?

2 Answers2

Linked

Related