Hiding worksheets from specific users inside a spreadsheet

Question

Sorry I'm not familiar with coding, I have a spreadsheet with multiple worksheets, it's shared with many people, I'm trying to make those sheets hidden by defaults when it's shared with a new person, and define some users as an admin users, and user groups for each location, I could run the code, and it worked when it was shorter, I know there might be easiest ways, however do you beilive there is a speific reason I'm getting nothing running the code, and users still see the hidden sheets unless I delete the IF statements.

Many thanks in advance.

function onOpen() {
      var Adminusr= ['amw22test.com','[email protected]','[email protected]'];
      var Germanyusr = ['[email protected]', '[email protected]'];
      var Austriausr = ['[email protected]', '[email protected]'];
      var Greeceusr = ['[email protected]'];
      var Polandusr = ['[email protected], [email protected]'];
      var Spainusr = ['[email protected], [email protected], [email protected], [email protected]']; 


        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Germany').hideSheet()
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Austria').hideSheet()
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Greece').hideSheet()
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Poland').hideSheet()
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Spain').hideSheet()



      if (Adminusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0 || Germanyusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Germany').showSheet()
      }

      if  (Adminusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0 || Austriausr.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Austria').showSheet()
        }

      if (Adminusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0 || Greeceusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Greece').showSheet()
      }

      if (Adminusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0 || Polandusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Poland').showSheet()
      }

      if (Adminusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0 || Spainusr.indexOf(Session.getEffectiveUser().getEmail()) >= 0) {
        SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Spain').showSheet()
      }


    }

Rubén · Answer 1 · 2020-05-27T18:12:17.733

Your project has two main problems

Authorization
Algorithm

Authorization

You are missing the restrictions of onOpen and Class Session.

onOpen is a reserved name for the open simple trigger. When it's triggered by an open event, then can't execute methods that require authorization.

Session.getEffectiveUser().getEmail() and Session.getActiveUser().getEmail() might work when the owner open the spreadsheet but will return an empty string when the spreadsheet is opened by an editor. Related question: Session.getActiveUser().getEmail() returns no Value

One trick to use a custom menu to save the active user email address on the User Properties store, then onOpen could get the user email address from there instead of using Class Session. Related question: Determine current user in Apps Script

Algorithm

onOpen will be executed every time the owner or an editor, any editor, opens the spreadsheet no matter if another user is active, so if [email protected] opens de the spreadsheet and few moments later amw22test.com do the same, the first user will see all the sheets.

Maybe instead of using one spreadsheet for all users you should create one spreadsheet for each group and one master spreadsheet for the admin group.

Hiding worksheets from specific users inside a spreadsheet

1 Answers1

Authorization

Algorithm

Linked

Related