Google Apps Script onOpen() simple trigger is not working

Question

I have a simple onOpen code written to trigger on Google Sheets for multiple users. What this code does is, it hides the sheets pertaining to other users, but opens only the sheet belonging to that user. However this code seems to be working fine when I open the sheet, but does not trigger for my other users, it is keeping there sheets hidden and the reason is that onOpen() code is never triggering.


    /** @OnlyCurrentDoc */


function onOpen() {

  var app = SpreadsheetApp;
  var ProjectSummary = app.getActiveSpreadsheet().getSheetByName("Project Summary (YTD)");
  var ProjectSummaryMonth = app.getActiveSpreadsheet().getSheetByName("Project Summary (Monthly)");
  var AdminInputs = app.getActiveSpreadsheet().getSheetByName("AdminInputs");
  var Project = app.getActiveSpreadsheet().getSheetByName("ProjectInputs");
  var TimeTracker = app.getActiveSpreadsheet().getSheetByName("Login");

  //User Sheets
  var SRSheet = app.getActiveSpreadsheet().getSheetByName("SR");
  var DSSheet = app.getActiveSpreadsheet().getSheetByName("DS");
  var GESheet = app.getActiveSpreadsheet().getSheetByName("GE");
  var JCSheet = app.getActiveSpreadsheet().getSheetByName("JC");
  var JISheet = app.getActiveSpreadsheet().getSheetByName("JI");
  var MCSheet = app.getActiveSpreadsheet().getSheetByName("MC");
  var MQSheet = app.getActiveSpreadsheet().getSheetByName("MQ");
  var NSSheet = app.getActiveSpreadsheet().getSheetByName("NS");
  var PJSheet = app.getActiveSpreadsheet().getSheetByName("PJ");
  var SPSheet = app.getActiveSpreadsheet().getSheetByName("SP");


  // Logic Begins Here, we are checking time stamp and choosing only sheets based on if the sheet was active within past 3 hours or not, if it was not we hide that sheet

  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();

  var currDate = new Date();
  var currDateH = (currDate/1000)/(60*60);

  for(var i=0; i<=14; i++){ 
    for(var j = 2; j<=16; j++){
      if(sheets[i].getName()==TimeTracker.getRange("A"+(j)).getValue() && sheets[i].getName()!=="Login"  ){
        var userdatetime = (TimeTracker.getRange("B"+(j)).getValue()/1000)/(60*60);
        var diff = currDateH-userdatetime;
        if(diff>=0.5)
        {
          sheets[i].hideSheet();
        }
     }

   }
  }

  var SR = ['[email protected]'];
  var DS= ['[email protected]'];
  var GE = ['[email protected]'];
  var JI = ['[email protected]'];
  var JC = ['[email protected]'];
  var MC = ['[email protected]'];
  var MQ = ['[email protected]'];
  var NS = ['[email protected]'];
  var PJ = ['[email protected]'];
  var SP = ['[email protected]'];


  if (SR.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    SRSheet.showSheet();
    TimeTracker.getRange("B11").setValue(currDate); 
  }

  if (DS.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    DSSheet.showSheet();
    TimeTracker.getRange("B2").setValue(currDate); 
  }

  if (GE.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    GESheet.showSheet();
    Project.showSheet();

    ProjectSummary.showSheet();
    ProjectSummaryMonth.showSheet();
    TimeTracker.getRange("B3").setValue(currDate);

    TimeTracker.getRange("B13").setValue(currDate);
    TimeTracker.getRange("B14").setValue(currDate);
    TimeTracker.getRange("B15").setValue(currDate);
  }

  if (JI.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    JISheet.showSheet();
    TimeTracker.getRange("B5").setValue(currDate); 
  }

  if (JC.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    JCSheet.showSheet();
    Project.showSheet();

    ProjectSummary.showSheet();
    ProjectSummaryMonth.showSheet();
    TimeTracker.getRange("B4").setValue(currDate);

    TimeTracker.getRange("B13").setValue(currDate);
    TimeTracker.getRange("B14").setValue(currDate);
    TimeTracker.getRange("B15").setValue(currDate);

  }

  if (MC.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    MCSheet.showSheet();
    TimeTracker.getRange("B6").setValue(currDate); 
  }

  if (MQ.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    MQSheet.showSheet();
    TimeTracker.getRange("B7").setValue(currDate); 
  }

  if (NS.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    NSSheet.showSheet();
    Project.showSheet();

    ProjectSummary.showSheet();
    ProjectSummaryMonth.showSheet();
    TimeTracker.getRange("B8").setValue(currDate);

    TimeTracker.getRange("B13").setValue(currDate);
    TimeTracker.getRange("B14").setValue(currDate);
    TimeTracker.getRange("B15").setValue(currDate);
  }

  if (PJ.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    PJSheet.showSheet();
    TimeTracker.getRange("B9").setValue(currDate); 
  }

  if (SP.indexOf(Session.getEffectiveUser().getEmail()) >= 0){
    SPSheet.showSheet();
    TimeTracker.getRange("B10").setValue(currDate); 
  }

}

Try using an installable trigger. It may simply be a permissions issue. Simple triggers can't do things that require permission. The gogd news is that will probably fix your issue the bad news is that you users will have to authorize the script to run. — Cooper
In addition to what Cooper said, please, remove actual emails from the question and check before posting (alas, they are now part of the post history). Could you clarify if the script is deployed as an editor add-on? — Oleg Valter
Thank Oleg, removed actual emails..the script was written as owner and then ownership was transferred, not sure on how to deploy script as an editor add-on. — Someshwar Rao
Oh, no need to redeploy - I was just wondering if the script is simply written as a container-bound (document) script or is intended to be a Google Sheets add-on - this may be related. — Oleg Valter
You are not alone with this problem - check Rubén's question - likely related — Oleg Valter

ziganotschka ziganotschka · Accepted Answer · 2020-06-15T09:21:24

Keep in mind that everyone sees the same version of the spreadsheet

If more than one user have the file open at the same time, your script will lead to conflicts since it is not possible that different sheets will be hidden for different users

Scenario:

User A opens the spreadsheet first
Let's assume that the script works as intended and only the sheets "belonging" to user A will be shown and the rest hidden
Now user B opens the spreadsheet - this will fire the trigger again and hide user A's sheet but show user B's sheet
Now user A will be able to see user B's sheet, but not his own anymore!

Workround

I would recommend you to take different approach, namely create individual spreadsheets for each user (containing only the sheet(s) to which the user should have access) that will be synched with the master spreadsheet containing all the data
The data between the master spreadsheet and the individual user spreadsheets can be synched either with IMPORTRANGE or with an Apps Script with an onEdit trigger
The latter has the advantage of being able to sync the sheet both ways
That is - if you perform edits in the master spreadsheet, the onEdit trigger bound to the master spreadsheet will to set values into the user spreadsheet and vice versa. if you also implement an onEdit trigger in the suer spreadsheet, it will implement edits made by the user into the master spreadsheet
There won't be a "ping-pong effect" since only manual edits will fire the onEdittrigger

Google Apps Script onOpen() simple trigger is not working

1 Answers

Keep in mind that everyone sees the same version of the spreadsheet

Workround