Google Script onOpen() for Spreadsheet does not trigger to other users who has access on it [duplicate]

Question

I have shared a google sheet via email to other users. Everytime I open the sheet, this code works fine for me, but doesnt work for other users. I have also tried using onOpen(e) but this doesnt even work for me. I also tried installable triggers that also works for me but not for others. Can someone clarify what I am doing wrong?

//onOpen code
function onOpen() {
  var sessionemail= Session.getActiveUser().getEmail();
  var arrayemail=["[email protected]", "[email protected]","[email protected]","[email protected]","[email protected]","[email protected]"];
  if (arrayemail.indexOf(sessionemail)!== -1){
        var arrayfind=arrayemail.indexOf(sessionemail);
        var nameemail=sessionemail.split(".");
        var namesheet=nameemail[0].toUpperCase();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName(namesheet).activate();
        var activesheet=SpreadsheetApp.getActive().getSheetByName(namesheet).getRange(SpreadsheetApp.getActiveSheet().getLastRow(), 1);
        activesheet.activate();
        
    } else{
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ALL PROJECTS').activate();
    }
}


//intallable triggers code
function MyFunction() {
  var sessionemail= Session.getActiveUser().getEmail();
  var arrayemail=["[email protected]", "[email protected]","[email protected]","[email protected]","[email protected]","[email protected]"];
  if (arrayemail.indexOf(sessionemail)!== -1){
        var arrayfind=arrayemail.indexOf(sessionemail);
        var nameemail=sessionemail.split(".");
        var namesheet=nameemail[0].toUpperCase();
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName(namesheet).activate();
        var activesheet=SpreadsheetApp.getActive().getSheetByName(namesheet).getRange(SpreadsheetApp.getActiveSheet().getLastRow(), 1);
        activesheet.activate();
        
    } else{
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ALL PROJECTS').activate();
    }
}

function createSpreadsheetOpenTrigger() {
  const ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('MyFunction')
      .forSpreadsheet(ss)
      .onOpen()
      .create();
}

Answer 1

onOpen is what's considered a simple trigger and because it runs without asking for authorization, it can't run any code that does require authorization. See here:

https://developers.google.com/apps-script/guides/triggers?hl=en#restrictions

getActiveUser() and most other functions that determine the identity of the users can cause onOpen to fail to run because they need authorization, and it will not automatically prompt the user for authorization.

You can get around this by having each user manually authorize the script once. To do this each user needs to do the following:

1. Open the Spreadsheet and click Extensions->Apps Script
2. Choose onOpen (or another function) from the drop-down beside debug and click Run
3. This will try to run the script and prompt the user for authorization

Once they authorize the script correctly, they can close the script and close or reload the sheet and the onOpen function should run as expected

An installed trigger will run under the account of the user who created the trigger, so if you created it, it won't be able to run getActiveUser() for the user of the sheet. The trigger would have to be created by each individual user or under their account for the trigger to work.