Google Sheet Script – Hiding tabs based on a range of cells containing emails

So, what I’m trying to do is hide certain tabs for certain users for privacy reasons.

I have one tab that contains 8 columns and 51 rows. (One column for each group of users, one row for titles, and 50 slots for each group)

What I’m trying to do is take this sheet, separate out each group, then depending on which group the current (active) user is in, hide certain sheets and show others.

I have 2 functions, one function to update the groups (validUsers), and a second to update which tabs are hidden (updateHiddenSheets).

validUsers() currently has 1/1 successful trigger. (onEdit)
updateHiddenSheets() currently has 0/2 successful triggers. (onOpen)

Is there a better way to do this?

Edit:
I’ve been trying a few different things, and now I’m running into an error from validUsers().

TypeError: Cannot read property '0' of undefined
Line 18

(that is the one line inside the first for loop)

Code:

  var numEmails = 50;//This is the number of emails in a given column 


  var emailsList = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').getRange(2,1,numEmails,8);
  var stakePeople = [numEmails];
  var crystalHeights1 = [numEmails];
  var crystalHeights2 = [numEmails];
  var stratfordEast = [numEmails];
  var stratford = [numEmails];
  var highlandPark = [numEmails];
  var imperial1 = [numEmails];
  var imperial2 = [numEmails];

  for(i=1; i<=numEmails;i++) {
    crystalHeights1 [i] = emailsList[i][0];
  }
  for(i=1; i<=numEmails;i++) {
    crystalHeights2 [i] = emailsList[i][1];
  }
  for(i=1; i<=numEmails;i++) {
    stratfordEast [i] = emailsList[i][2];
  }
  for(i=1; i<=numEmails;i++) {
    stratford [i] = emailsList[i][3];
  }
  for(i=1; i<=numEmails;i++) {
    highlandPark[i] = emailsList[i][4];
  }
  for(i=1; i<=numEmails;i++) {
    imperial1 [i] = emailsList[i][5];
  }
  for(i=1; i<=numEmails;i++) {
    imperial2 [i] = emailsList[i][6];
  }
  for(i=1; i<=numEmails;i++) {
    stakePeople [i] = emailsList[i][7];
  }

}
  var currentUserEmail = Session.getActiveUser().getEmail()
  if (crystalHeights1.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').showSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (crystalHeights2.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (stratfordEast.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (stratford.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (highlandPark.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (imperial1.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (imperial2.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').hideSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').hideSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').hideSheet();
  } 
  else if (stakePeople.includes(currentUserEmail)) { 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 1st Ward').showSheet(); 
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Crystal Heights 2nd Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford East Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Stratford Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Highland Park Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 1st Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Imperial 2nd Ward').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Troubleshooting').showSheet();
    SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Emails').showSheet();
  } 
}

118 thoughts on “Google Sheet Script – Hiding tabs based on a range of cells containing emails”