0

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=["sadeeq.@gmail.com", "zahir.@gmail.com","khaled.@gmail.com","abir.@gmail.com","atiq.@gmail.com","hasib.@gmail.com"];
  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=["sadeeq.@gmail.com", "zahir.@gmail.com","khaled.@gmail.com","abir.@gmail.com","atiq.@gmail.com","hasib.@gmail.com"];
  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();
}
Zahirul Ahsan
  • 1
  • 1

1 Answers1

0

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.

bwall
  • 156
  • 9
  • Really thanks for detailed explanation. But for other users, I have checked and allowed authorization and it works only from macro or app script run button, not from opening the sheet. Can you please try my code with 2 different users, then you will get my problem – Zahirul Ahsan Mar 29 '22 at 12:32
  • I had to make every users self onOpen trigger, thats how it is working now. Its ok for some users, but for a bulk users, it will be a headache. – Zahirul Ahsan Mar 31 '22 at 03:27