Force ask authorization onOpen() (force popup)

Question

SUMMARY: Is it possible to ask for authorization onOpen()?

DETAILED VERSION: I have a spreadsheet with buttons that gets distributed to a lot of people. When any button is pressed, some functions that require permissions are called, so Google Apps Script shows this popup:

AFTER this is accepted, everything runs well, since it has authorization now. However, I want to run things that require permissions BEFORE a button is pushed, when the Workbook is opened. However, if you place authorization-requiring code into an onEdit or onOpen function, it runs with no privileges by default and crashes halfway, rather than showing the popup and asking for permissions.

Here is some code that exemplifies this - crashing instead of asking for permissions to create a trigger (also works for CalendarApp, etc.):

function onOpen(e) {
  Browser.msgBox("This is shown to the user");
  try {
    ScriptApp.newTrigger('someFunction').forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet()).onEdit().create();
  }
  catch (e) {
    Browser.msgBox(e);
  }
  Browser.msgBox("This message is never shown to the user if there is no try catch because it crashes");
}

Alan Wells Alan Wells · Accepted Answer · 2017-07-04T01:44:27

Note: A simple trigger cannot access services that require authorization. For example, a simple trigger cannot send an email because the Gmail service requires authorization

onOpen() is a reserved function name for a function that is a simple trigger.

There is a way to check the authorization status, and then get the authorization URL if the code needs to be authorized. However, if the ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL) method needs authorization by the user, then it won't work when run from onOpen()

The onOpen method can not open a sidebar, so the authorization status can't be checked from the onOpen trigger. The user would need to click a menu item that opened the sidebar or a dialog box first.

I'm not sure that the code 'ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL)` works that well. To force a re-authorization prompt, you may need to run code that would cause the scopes to be evaluated.

Code.gs

function onOpen() {
  //Create custom menu or add-on menu
}

function showSidebar() {
  var html = HtmlService.createTemplateFromFile('HTML_Sidebar').evaluate()
      .setTitle('Log Tools')
      .setWidth(300);
  SpreadsheetApp.getUi() 
      .showSidebar(html);
}

HTML_Sidebar

<!DOCTYPE html>
<html>
  <head>
    <base target="_blank">
  </head>
  <body>

    <div><?!= getAuthUrl(); ?></div>
  </body>

</html>

GS_Sidebar

function getAuthUrl() {
  var authInfo,msg;

  authInfo = ScriptApp.getAuthorizationInfo(ScriptApp.AuthMode.FULL);

  msg = 'This spreadsheet needs permission to use Apps Script Services.  Click ' +
    'this url to authorize: <br><br>' + 
      '<a href="' + authInfo.getAuthorizationUrl() +
      '">Link to Authorization Dialog</a>' +      
      '<br><br> This spreadsheet needs to either ' +
      'be authorized or re-authorized.';

  //return msg;//Use this for testing

  //ScriptApp.AuthMode.FULL is the auth mode to check for since no other authorization mode requires
  //that users grant authorization
  if (authInfo.getAuthorizationStatus() === ScriptApp.AuthorizationStatus.REQUIRED) {
    return msg;
  } else {
    return "No Authorization needed";
  }
}

Force ask authorization onOpen() (force popup)

2 Answers

Code.gs

HTML_Sidebar

GS_Sidebar