google-sheetsgoogle-apps-scriptgoogle-appsspreadsheet-protection

Google Sheet different cell ranges protection and access levels (all, specific, email-based)

Have this Google Sheet: enter image description here

SourceSheet (sample data) is available: SourceSheet

Trying to implement protection rules:

function setEditPermissions() {
  var sheetUrl = 'https://docs.google.com/spreadsheets/d/1r3nQZ1ZS0Y9zUv1ZfdjQlWIVHSqf-wF4neNDksCse6Y/edit?usp=drive_link';
  var sheetName = 'Sheet1';

  protectAllCells(sheetUrl, sheetName )
  setEditable(sheetUrl, sheetName)
  setEmailAccess(sheetUrl, sheetName) 
}


function protectAllCells(sheetUrl, sheetName ) {
  var ss = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = ss.getSheetByName(sheetName);

  // Защищаем все ячейки на листе
  var protection = sheet.protect().setDescription('Защита всего листа');

  // Устанавливаем права доступа на редактирование
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
}



function setEditable(sheetUrl, sheetName) {
  var ss = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = ss.getSheetByName(sheetName);

  var editableRanges = ['B2:B10', 'B13:B21'];

 editableRanges.forEach(function(rangeStr) {
    var range = sheet.getRange(rangeStr);
    var protection = range.protect().setDescription('Защита диапазона ' + rangeStr);
    protection.remove();
  });
}


function setEmailAccess(sheetUrl, sheetName) {
  var ss = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = ss.getSheetByName(sheetName);

  // Диапазоны, которые нужно защитить
  var rangesToProtect = ['D2:D10', 'F2:F10'];

  // Пользователи, которым разрешено редактировать диапазоны
  var allowedUsers = ['email@gmail.com'];

  rangesToProtect.forEach(function(rangeStr) {
    var range = sheet.getRange(rangeStr);
    var protection = range.protect().setDescription('Защита диапазона ' + rangeStr);
    // Удаляем все предыдущие редакторы
    protection.removeEditors(protection.getEditors());
    // Запрещаем редактирование всем, кроме указанных пользователей
    protection.addEditors(allowedUsers);
    // Разрешаем только указанным пользователям редактировать
    //protection.setWarningOnly(true);
  });
}

First step (protecting the sheet worked by function protectAllCells). The next 2 steps (functions: setEditable, setEmailAccess) did not worked. But logic seems to be correct.

Is it possible to implement it using Google Apps Script and Google Sheets by codes?

Solution

  • I believe your goal is as follows.

    To achieve this goal, I prepared the following flow. To test my script, please do the following flow.

    1. Prepare a Google Spreadsheet.
    2. Share the Spreadsheet with "user A" and "user B". I am the owner of Spreadsheet.
    3. Prepare a sample script.
      • Please set variables for your situation.
    4. Run a sample script.
      • var editableRanges = ['B2:B10', 'B13:B21'] can be edited by "user A", "user B" and me.
      • var rangesToProtect = ['D2:D10', 'F2:F10'] can be edited by "user A" and me.
      • Cells except for var editableRanges = ['B2:B10', 'B13:B21'] and var rangesToProtect = ['D2:D10', 'F2:F10'] can be edited by only me.

    The sample script is as follows.

    Sample script:

    Please copy and paste the following script to the script editor of Spreadsheet. And, please set your variables.

    In this script, the email address of "user A" is var allowedUsers = ['###']; .

    function myFunction() {
  // Please set variables for your situation.
  var sheetUrl = 'https://docs.google.com/spreadsheets/d/{spreadsheetId}/edit'; // Please set your Spreadsheet ID.
  var sheetName = 'Sheet1';
  var editableRanges = ['B2:B10', 'B13:B21'];
  var rangesToProtect = ['D2:D10', 'F2:F10'];
  var allowedUsers = ['###']; // Please set email address you want to permit to edit the cells "rangesToProtect".


  // Remove all protections.
  var sheet = SpreadsheetApp.openByUrl(sheetUrl).getSheetByName(sheetName);
  [...sheet.getProtections(SpreadsheetApp.ProtectionType.SHEET), ...sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)].forEach(p => p.remove());

  // Protect a sheet and set unprotect ranges.
  var editableRangesObj = sheet.getRangeList(editableRanges).getRanges();
  var rangesToProtectObj = sheet.getRangeList(rangesToProtect).getRanges();
  var p1 = sheet.protect().setDescription('Защита всего листа');
  p1.removeEditors(p1.getEditors());
  p1.setUnprotectedRanges([...editableRangesObj, ...rangesToProtectObj]).setDescription('Защита диапазона ' + editableRanges.join(","));

  // Protect ranges with emails.
  rangesToProtectObj.forEach(r => {
    var p2 = r.protect().setDescription('Защита диапазона ' + r.getA1Notation());
    p2.removeEditors(p2.getEditors());
    p2.addEditors(allowedUsers);
  });
}

    Testing:

    When this script is run, the following result is obtained.