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();
}
}
question from:
https://stackoverflow.com/questions/65945400/google-sheet-script-hiding-tabs-based-on-a-range-of-cells-containing-emails 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…