Active VS Effective User for Google Sheets

Question

I'm writing a simple google app script application which performs some data manipulation depends of the user who requests the page.

According to google documentation object Session has getActiveUser() and getEffectiveUser() which I currently use in order to determine the user. Have a look at the code:

var email = Session.getActiveUser().getEmail();
  switch (email){
    case '[email protected]':
      /*Some code here*/
      return true;
    case '[email protected]':
      /*Some code here*/
      return true;
    default:
      return false;
  }

It looks like that it should work, unfortunately it doesn't work as expected (at least for me).

The code above runs when onOpen trigger fires, and all permissions are set when the user attempts to run this code for the first time.

So, I've decided to perform tracing and found out that Session.getActiveUser().getEmail(); and Session.getEffectiveUser().getEmail(); return wrong emails for the users.

For the 1-st user (me) who created a script Session.getActiveUser().getEmail(); returns correct email, but for all the others it returns my email as well. Ok, I've decided to replace Session.getActiveUser().getEmail(); with Session.getEffectiveUser().getEmail(); and BOOM - it works for others but doesn't work for me...

How could it be? Any thoughts?

• I've noticed that when I run the code from the ScriptEditor, code works like a charm for all the users, but when It runs when onOpen fires it works unpredictable.

This spreadsheet is shared with several persons.

Any help is appreciated.

Answer 1

Short answer

The code runs the wrong statements because there isn't a break statement to avoid their execution. On the other hand, for consumer accounts (gmail.com) getActiveUser will only return a value when the script is run by the active user¹.

¹: https://developers.google.com/apps-script/reference/base/session#getActiveUser()

Explanation

You should add break; at the end of each case set of statements, otherwise the next the statements will be executed too. Instead, consider the following to for you tests.

Alternative test code

function onOpen(){
  myFunction('On open - simple trigger');
}

function onOpenInstallable(){
  myFunction('On open - installable trigger');
}

function runFromScriptEditor(){
  myFunction('Run - script editor');
}

function myFunction(context) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var range = sheet.getRange(lastRow + 1,1,1,4);
  var output = [[
    new Date(),
    context,
    Session.getActiveUser().getEmail(),
    Session.getEffectiveUser().getEmail()
    ]];
  range.setValues(output); 
}

Result

Owner: [email protected]
Editor: [email protected]

• Rows 2 and 3 added when spreadsheet was opened by [email protected]
• Rows 4 and 5 added when spreadsheet was opened by [email protected]
• Row 6 adden when stript was triggered by clicking on the Run button of the Script Editor by [email protected]

+---+------------+-------------------------------+-----------------+-----------------+
|   |     A      |               B               |        C        |        D        |
+---+------------+-------------------------------+-----------------+-----------------+
| 1 | Timestamp  | Context                       | Active User     | Effective User  |
| 2 | 10/29/2016 | On open - simple trigger      | [email protected] | [email protected] |
| 3 | 10/29/2016 | On open - installable trigger | [email protected] | [email protected] |
| 4 | 10/29/2016 | On open - simple trigger      |                 |                 |
| 5 | 10/29/2016 | On open - installable trigger |                 | [email protected] |
| 6 | 10/29/2016 | Run - script editor           | [email protected] | [email protected] |
+---+------------+-------------------------------+-----------------+-----------------+