OiO.lk Community platform!

Oio.lk is an excellent forum for developers, providing a wide range of resources, discussions, and support for those in the developer community. Join oio.lk today to connect with like-minded professionals, share insights, and stay updated on the latest trends and technologies in the development field.
  You need to log in or register to access the solved answers to this problem.
  • You have reached the maximum number of guest views allowed
  • Please register below to remove this limitation

I have a Google spreadsheet that has 20 sheets

  • Thread starter Thread starter Natalie Chetverikova
  • Start date Start date

Natalie Chetverikova

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) {

    if ((user === "[email protected]" && (sheetName === "name1" || sheetName === "пустой")) || 
        (user === "[email protected]" && sheetName === "Sheet2")) {
      Logger.log('Showing sheet: ' + sheetName);  // Логируем показ листа
    } else if (sheetsToHide.includes(sheetName)) {
      Logger.log('Hiding sheet: ' + sheetName);  // Логируем скрытие листа

      // Применяем защиту на лист
      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]") {
    } else {
      Logger.log('Leaving sheet as is: ' + sheetName);  // Логируем оставление листа без изменений
      sheet.showSheet();  // Убедитесь, что другие листы видимы

Continue reading...