Trouble getting onOpen to trigger in Google Apps Script

Question

I’m somewhat new to scripting so would be extremely grateful to anybody who could provide an insight to my problem. For some reason I’m unable to get the Google script below to trigger onOpen for my Google Apps spreadsheet.

The sheets in my spreadsheet range from January to December from left to right. The script is meant to identify the current month number, then use that number to identify and set the active sheet for that month when the spreadsheet is opened. I’ve taken it verbatim from another thread: Google Apps Spreadsheet open specific sheet based on current date (Month)

I’m simply creating a new spreadsheet, selecting Script Editor from the Tools drop-down, then pasting in the below script and saving it.

/**
 * Selects a monthly sheet
 */
function onOpen() {
  var month = (new Date()).getMonth();

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[month];
  ss.setActiveSheet(sheet);
};

Have tried both naming it onOpen, and adding an onOpen trigger to the function after renaming it. I’m able to get other onOpen events to trigger when the spreadsheet is opened, but sadly this one only works if I trigger it manually from the script editor.

Cheers.

This seems a bug of the new version of Sheets. I tried the old version of Sheets and it works, but when you open the spreadsheet have generated two sheets instead of one. To make it work properly with the old version I created a Installable Trigger. I opened an issue Apps Script team: The setActiveSheet method does not work in onOpen() — wchiquito
I second the onOpen working fine in the old version, I am using it currently. Must be a new issue. — CameronC
Ok so I'm not going crazy then. Thanks for the updates. I'll be sure to track your issue @wchiquito to see if anything comes out of it, or post a work-around if I can find anything. — SoccerFan

backy0175 backy0175 · Accepted Answer · 2014-06-05T11:01:33

Unfortunately specifications was changed. On new spreadsheet, methods that related to activate no longer have any effect if they are called from an onOpen or onEdit trigger.

You can get more details about change below.

Google Apps Script

Issue 3928: April 22, 2014, New Spreadsheet: activate() doesn't change active sheet into onOpen service function event https://code.google.com/p/google-apps-script-issues/issues/detail?id=3928

Release Notes April 17, 2014, The Document method setSelection the Sheet method activate, and the Spreadsheet methods setActiveRange and setActiveSelection no longer have any effect if they are called from an onOpen or onEdit trigger. https://developers.google.com/apps-script/releases/#april_2014

bakcy0175

Trouble getting onOpen to trigger in Google Apps Script

1 Answers