0

I am trying to create a google sheets custom menu based on the user email. Code is as follows:

var AllowedReportRecipients = ["u1@gmail.com", "u2@gmail.com", "u3@gmail.com"];
var ReportRecipient = null;

function onOpen() 
{
  var ui = SpreadsheetApp.getUi();
  var menu = ui.createMenu('Custom');
  menu.addItem('Edit', 'editHtm')
      .addItem('History', 'historyHtm');

  var usr = Session.getActiveUser().getEmail();
  var isReportee = false;
  for (var i=0;i<AllowedReportRecipients.length; i++)
    if (AllowedReportRecipients[i] == usr)
    {
      ReportRecipient = usr;
      isReportee = true;
    }
  if (isReportee)
    menu.addItem('Request abc', 'sendABC');
  menu.addToUi();
}

This is an unpublished sheet script. As others have reported the call to getActiveUser().getEmail() returns blank when the sheet is opened by the non-owner of the sheet. However, a call to getActiveUser().getEmail() executed at a later time correctly returns the logged in user email. For example, when called in historyHtm in response to the "History" menu item click it works. Why the difference? How do I properly load my menus?

Rubén
  • 34,714
  • 9
  • 70
  • 166
rka
  • 13
  • 4

1 Answers1

2

The onOpen() function is a special reserved function, one of the [Simple Trigger][1] functions built into Google Apps Script. These functions run without requiring user authorization, so there are restrictions on what capabilities of the system they may access.

For your case, the important restriction is this:

They may or may not be able to determine the identity of the current user, depending on a [complex set of security restrictions][2]. ref

For users with consumer accounts or accounts that are not in the same Google Apps Domain as the developer, the onOpen() function will not be able to obtain the email address of the person running the script.

When you run other functions, for example those that are invoked via menu, they kick off the authorization cycle for the script, after which they are able to do more than the simple triggers.

That seems inconsistent - but is by design.

I think you need to rethink your user interface - I haven't been able to find a work-around that will produce the "privileged" portion of your menu upon opening the spreadsheet.

  • An installed onOpen trigger will have greater abilities than the simple trigger, but will run under the authority of the developer so it won't help your situation, as it will come up with your email, not the active user's.
  • Setting a timed trigger in the onOpen to update the menu seemed promising... except for the restriction that the timed trigger function has no access to the spreadsheet UI, of course.
Mogsdad
  • 44,709
  • 21
  • 151
  • 275