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
– GregL, Commented Oct 14, 2014 at 3: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
– neydroydrec, Commented Oct 14, 2014 at 3:57
possible duplicate of Accessing global variable in UI handler Google Spreadsheet Script — GregL
– GregL, Commented Oct 14, 2014 at 4:05
See this question and this question 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
– GregL, Commented Oct 14, 2014 at 4:08

Community · Accepted Answer · 2017-05-23 11:57:09Z

1

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.

edited May 23, 2017 at 11:57

CommunityBot

11 silver badge

answered Oct 14, 2014 at 4:09

Vivek Pradhan

4,8653 gold badges28 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Vivek Pradhan Over a year ago

@Benjamin, please accept a suitable answer so that others in a similar problem might also benefit out of it. Thanks

Zig Mandel Over a year ago

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.

neydroydrec · Accepted Answer · 2014-10-14 04:06:26Z

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, 2014 at 4:06

neydroydrec

7,38110 gold badges64 silver badges94 bronze badges

1 Comment

GregL Over a year ago

Using the PropertiesService 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.

Collectives™ on Stack Overflow

Global variable defined in function appears not defined?

2 Answers 2

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related