SpreadsheetApp.getActive() fails in onOpen trigger in Google Sheets addon

0
votes

I would like to read the Active spreadsheet and add menu items based on that. Below is the code in OnOpen(e) trigger:

function onOpen(e) {
 var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
 //add menus based on values of sheet
}

I am getting below exception:

[Exception: You do not have permission to perform that action.]

I have gone through the documentation and found that the AuthMode is None in OnOpen trigger which does not allow access to any services that require authorization.

Please suggest if there is any other way to accomplish my task.

Thanks.

1
google-apps-scriptgoogle-sheetsadd-on
1. Is this a published addon 2. If not, Edit to show your manifest(View>): appscript.json - TheMaster
Also, a reproducible example would help both you and us find a solution - soMarios
@TheMaster, yeah it's published - Jayakrishnan
@Marios, I am getting the exception at this line only "SpreadsheetApp.getActive()" - Jayakrishnan
Then it's not possible to access the spreadsheet unless the user uses the add-on.. i.e., The add-on is enabled for the document. Use menu's to start a workflow first in AuthMode.None. Then, after getting AuthMode.FULL, do the rest. The documentation you linked states all this clearly with example code. - TheMaster

1 Answers

1
votes

It's not possible to access the spreadsheet unless the owner or editor of the spreadsheet uses the add-on in that spreadsheet. As written in the official document,

However, because an editor add-on automatically runs its onOpen(e) function to add menu items when a document opens, the behavior above adds some complexity to Apps Script's authorization rules. After all, users wouldn't be comfortable with an add-on accessing personal data every time they open a document.

The add-on needs to be enabled for the document for the addon to get access to the document. When it's enabled, onOpen() runs in AuthMode.LIMITED, where you get access to the bound document. This "enabled" state is caused by

Getting an add-on from the store while using that document, or Using a previously installed add-on in that document

In all other cases, add on runs in AuthMode.NONE. In this mode, You are only able to add menu items without access to any data including the bound spreadsheet. Add Menu items to request access to such data. Then, after getting AuthMode.FULL, the rest of the workflow can be done.

References: