How to sent an alert when someone open the Spreadsheet using Google Script

Question

Situation:

I have a spreadsheet with 15 worksheets and 20 users logging in and modifying it.

I have the following script that should be send an email with the user email adress who open the spreadsheet.

Script Function Needed:

1. Send an Email when the user open the spreadsheet.

Problem:

Session.getEffectiveUser() is only getting the the email from the owner of script and not getting my user email.

The Session.getEffectiveUser() since July23 should be get a re-autorization from the user. I perform the following note to re-approve the script for other user Script and Google Account

Test Case:

This script have a trigger when someone open the spreadhseet.

    function LoginShift(){
      var ss = SpreadsheetApp.getActiveSpreadsheet();
         MailApp.sendEmail("xxxxxx@gmail.com", "Login Shift" , "Login UserName: " + Session.getEffectiveUser());
      }

1. When I open the spreadsheet with the owner of the script, the same sent me an email with the email address who open the spreadsheet.
2. When I open the spreadsheet with different user who is not owner of the script, the script run and send the email but using the owner mailbox and not the mailbox of the user who open the spreadsheet.

I have other script with the Session.getEffectiveuser() but onEdit and is working fine. I don't know why this script is not getting the email address from getEffectiveUser.

Answer 1

There are some features as described in the documentation:

Understanding Triggers

...

Installable Triggers

...

• When a Spreadsheet or the Form editor is opened. (Note that this trigger does not activate when a user opens a form to respond, but rather when an editor opens the form to modify it.) Unlike the simple trigger onOpen, the installable trigger can act as the user who installed the trigger.

...

The behavior you mention in question is as expected according to the documentation.

UPDATE

If you have a Simple Trigger (onInstall, onOpen, onEdit) as follows:

function onOpen() {
  Logger.log('getActiveUser(): ' + Session.getActiveUser());
  Logger.log('getEffectiveUser(): ' + Session.getEffectiveUser());
}

When executed by the owner of the script, the result is as follows:

getActiveUser(): owner_of_the_script
getEffectiveUser(): owner_of_the_script

When executed by another user (not the owner of the script), the result is as follows:

getActiveUser(): 
getEffectiveUser(): user_activates_the_trigger

IMPORTANT: read the limitations of Simple Triggers in the documentation.

If you have a Installable Trigger as follows and you add the function to the event onOpen of the spreadsheet:

function myOnOpen() {
  Logger.log('getActiveUser(): ' + Session.getActiveUser());
  Logger.log('getEffectiveUser(): ' + Session.getEffectiveUser());
}

When executed by the owner of the script, the result is as follows:

getActiveUser(): owner_of_the_script
getEffectiveUser(): owner_of_the_script

When executed by another user (not the owner of the script), the result is as follows:

getActiveUser(): 
getEffectiveUser(): owner_of_the_script