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 'test@gmail.com':
      /*Some code here*/
      return true;
    case 'test2@gmail.com':
      /*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.

Check on the trigger [restrictions](https://developers.google.com/apps-script/guides/triggers/) I believe it may have something to do with that. Simple triggers don't play nice with authorization and in extent with getting the user that is running them — Vytautas, Oct 13 '16 at 11:56
@Vytautas Many thanks for the response, however that was the second resource (page) that I've looked through when writing the script. It seems to me, moreover, I'm pretty sure that all the requirements are met (and were rechecked again when writing this comment). — Alexander Druzhynin, Oct 13 '16 at 13:44
Two questions: 1. Are all your users from the same Google Apps for Business domain? If so `Session.getActiveUser().getEmail()` should work. If not, the specs say that it will return an blank string. 2. Have you looked into using the [event object](https://developers.google.com/apps-script/guides/triggers/events)? — Joshua Dawson, Oct 14 '16 at 03:01
@JoshDawson, 1) I just created a google sheets document, shared it with my friends, implemented all the requested logic and added a trigger for this exact project. So it seems to me that I don't have business domain, am I right? 2) That spec tells that a "User object, representing the owner of the document", but can I use Session object in order to determine the user email address for which the trigger is being processed? — Alexander Druzhynin, Oct 19 '16 at 07:48
I've replaced all the triggers for all the users with one for the project and found out that Session.getActiveUser().getEmail() doesn't work (blank) for those users with whom this document was shared and works fine (my email is returned) for me.. — Alexander Druzhynin, Oct 19 '16 at 07:54

score 2 · Accepted Answer · edited Apr 08 '23 at 05:07

#Short answer The code rans the wrong statements because there aren't a break statements to avoid their execution, by the other hand, for consumer accounts (gmail.com) getActiveUser only will 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: testA@gmail.com
Editor: testB@gmail.com

Rows 2 and 3 added when spreadsheet was opened by testA@gmail.com
Rows 4 and 5 added when spreadsheet was opened by testB@gmail.com
Row 6 adden when stript was triggered by clicking on the Run button of the Script Editor by testB@gmail.com

+---+------------+-------------------------------+-----------------+-----------------+
|   |     A      |               B               |        C        |        D        |
+---+------------+-------------------------------+-----------------+-----------------+
| 1 | Timestamp  | Context                       | Active User     | Effective User  |
| 2 | 10/29/2016 | On open - simple trigger      | testA@gmail.com | testA@gmail.com |
| 3 | 10/29/2016 | On open - installable trigger | testA@gmail.com | testA@gmail.com |
| 4 | 10/29/2016 | On open - simple trigger      |                 |                 |
| 5 | 10/29/2016 | On open - installable trigger |                 | testA@gmail.com |
| 6 | 10/29/2016 | Run - script editor           | testB@gmail.com | testB@gmail.com |
+---+------------+-------------------------------+-----------------+-----------------+

If using _return_ statement inside _switch case_ the workflow **will return** from the first matching _case_. Have a look at the your link [Switch](http://stackoverflow.com/documentation/javascript/221/conditions/2061/switch-statement#t=201610300117212480493), section "Multiple Inclusive Criteria for Cases" — Alexander Druzhynin, Jan 29 '17 at 13:44
_for consumer accounts (gmail.com) getActiveUser only will return a value when the script is run by the active user_ It seems that this is the answer — Alexander Druzhynin, Jan 29 '17 at 13:46
Also for installable trigger, it will only show the user who installed that trigger — vstepaniuk, Dec 23 '21 at 08:23

Active VS Effective User for Google Sheets

1 Answers1

Linked

Related