CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
FishWave
FishWave

Reputation: 308

What is the difference between onOpen and other functions?

I have to sync timezones between a script and a google sheet. First, i get the timezones of both. Now I have the exact same code in a function onOpen and myCode. onOpen is executed when the sheet opens. myCode I have to execute manually.

The script is it the sheet file, not standalone.

onOpen is not working. In the Log I get the following error:

ReferenceError: getActiveSpreadsheet is not defined
    at onOpen(Code:2:15)

here the code:

function onOpen() {
  let sheet = getActiveSpreadsheet();
  Logger.log(sheet);
  let sheetTimeZone = getActiveSpreadsheet().getSpreadsheetTimeZone();
  Logger.log(sheetTimeZone);
}

myCode is working and I get the timezone back.

function myCode() {
  let sheet = getActiveSpreadsheet();
  Logger.log(sheet);
  let sheetTimeZone = getActiveSpreadsheet().getSpreadsheetTimeZone();
  Logger.log(sheetTimeZone);
}

I guess, the code onOpen is executed too early. But I can't find any solution or documentation on this issue.

Upvotes: 0

Views: 98

Answers (1)

Marios
Marios

Reputation: 27380

Explanation:

Based on your comments I think you have confused the differences between classes and methods (functions).

getActiveSpreadsheet is indeed a built in function but it is a method of the SpreadsheetApp class.

What does that mean?

In order to execute a method of a class in JavaScript but also in many other programming languages too you need to call the name of the class and then the function (or method):

SpreadsheetApp.getActiveSpreadsheet();

Solution:

In your case, the final result would be:

function onOpen() {
  let spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  Logger.log(spreadsheet);
  let spreadsheetTimeZone = spreadsheet.getSpreadsheetTimeZone();
  Logger.log(spreadsheetTimeZone);
}

Bonus info:

  • getActiveSpreadsheet() does not return a sheet object but a spreadsheet object. This is important because each object has different methods that are available.

  • So it is better if you rename variable sheet to spreadsheet to make clear that distinction. Although the naming of the variables is up to you, it is important to understand the differences between spreadsheets and sheets.

  • onOpen trigger function runs automatically (is triggered) when a user opens a spreadsheet that they have permission to edit

Upvotes: 2

Related Questions