there is a Google table. there are 20 sheets in it. I need each user to have access to a specific sheet, for example, for a user [email protected] there was access to sheet 2 , and the rest of the sheets were blocked for it, as well as for the user [email protected] there was only access to the sheet 3
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var user = Session.getActiveUser().getEmail();
var sheets = ss.getSheets();
var sheetsToHide = [
"sheet1", "sheet2", "sheet3"
];
Logger.log('Active user: ' + user); // Логируем email активного пользователя
sheets.forEach(function(sheet) {
var sheetName = sheet.getName();
Logger.log('Processing sheet: ' + sheetName); // Логируем обрабатываемый лист
// Удаляем существующую защиту сначала
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
protections.forEach(function(protection) {
protection.remove();
});
if ((user === "[email protected]" && (sheetName === "name1" || sheetName === "пустой")) ||
(user === "[email protected]" && sheetName === "Sheet2")) {
Logger.log('Showing sheet: ' + sheetName); // Логируем показ листа
sheet.showSheet();
} else if (sheetsToHide.includes(sheetName)) {
Logger.log('Hiding sheet: ' + sheetName); // Логируем скрытие листа
sheet.hideSheet();
// Применяем защиту на лист
var protection = sheet.protect().setDescription('Protected sheet');
var me = Session.getEffectiveUser();
protection.addEditor(me); // Разрешаем владельцу скрипта редактировать
protection.removeEditors(protection.getEditors().filter(function(editor) {
return editor.getEmail() !== me.getEmail();
}));
if (user === "[email protected]") {
protection.removeEditor(user);
}
} else {
Logger.log('Leaving sheet as is: ' + sheetName); // Логируем оставление листа без изменений
sheet.showSheet(); // Убедитесь, что другие листы видимы
}
});
}
New contributor
Natalie Chetverikova is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.