Search code examples
google-apps-scriptgoogle-apps-marketplacechrome-web-storegoogle-apps-script-addon

"Authorisation is required to perform that action" for alert/popup, Google Apps Script add-on for Spreadsheets

I am building an Google Apps Script add-on for Google Spreadsheets.

In the manifest I have explicitly requested the following scopes: "https://www.googleapis.com/auth/script.container.ui", "https://www.googleapis.com/auth/spreadsheets"

Also the "OAuth consent screen" within my Google Cloud project has the same scopes and these are already verified by Google.

Nevertheless it is not possible for me to give feedback to my users in case of a wrong input. I have tried the following:

  • SpreadsheetApp.getActiveSpreadsheet().show(htmlApp);
  • SpreadsheetApp.getActiveSpreadsheet().toast(message);
  • SpreadsheetApp.getUi().alert('Wrong input.');
  • SpreadsheetApp.getUi().showSidebar(html);
  • Browser.msgBox('Wrong input.');

Nothings seems to work, I always see the error "Authorisation is required to perform that action" within the stackdriver logs.

What would work is to create an "error sheet" and log all input errors into it, or to make one cell within the main sheet to an "error cell" and log all input errors into it. I think this is not a good solution in terms of user experience.

It seems that this problem has something to do with the authMode: https://developers.google.com/gsuite/add-ons/concepts/editor-auth-lifecycle#authorization_modes Because "Access to user interface" is only possible within "FULL" authMode, which is not the case during onEdit().

The strange thing is, that other add-ons are able to show an alert/popup. Is this a hack? Or do I need more scopes? Or do I have to change anything else?

EDIT 08.10.2019:

@Jacques-Guzel Heron: For instance: the user inputs a date in cell A1 and another in cell B1. Within the onEdit(e) trigger I want to check if the date in B1 is after the date in A1, if not I want to alert the user. All the above listed methods lead to the error "Authorisation is required to perform that action", when the app is published. When the app is not published .toast(), .alert() and .msgBox() work within my test environment. The strange thing is, that these methods then create a DIV-element, whereas the other add-ons create an IFRAME-element for their alert/dialog-box. Therefore I think they use another method and it has nothing to do with scopes. In addiation I had a look during the OAuth verification process of these add-ons and the scopes are listed within the dev-tools. They do not use any special "ui-scope", so I doubt the cause for this error is scope related.

EDIT 09.10.2019: Now I know how an alert/dialog-box containing of an IFRAME-element can be made. To do this, these methods can be used: 

var htmlString, msg, htmlOutput;

htmlString = '<p>my html string</p>';
msg = 'my message';

htmlOutput = HtmlService
    .createHtmlOutput(htmlString)
    .setSandboxMode(HtmlService.SandboxMode.IFRAME)
    .setHeight(140);

SpreadsheetApp
    .getUi()
    .showModalDialog(htmlOutput, msg);

But it is also not possible to call this method during onEdit(e).

All the above listed methods can be called, through clicking on the menu items and also through clicking on buttons within the sidebar, but not during onEdit(e).

In addition I have added all the scopes which other add-ons are using, but in vain. So again my assumption got confirmed that the problem is not scope related.

I have the impression that some smart people have managed to find an inofficial workaroung, please let me know :)

Solution

  • The solution are installable triggers: https://developers.google.com/apps-script/guides/triggers/installable

    They are running in FULL authMode and therefore are allowed to access the user interface: https://developers.google.com/gsuite/add-ons/concepts/editor-auth-lifecycle#authorization_modes