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

Question

Have this Google Sheet:

SourceSheet (sample data) is available: SourceSheet

---

Trying to implement protection rules:

• The ranges B2:B10 and B13:B21 can be edited by everyone.
• Ranges D2:D10 and F2:F10 can only be edited by users specified by email account.
• All the other remaining cells are protected and nobody can edit them.

---

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 protected]'];

  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?

Answer 1

I believe your goal is as follows.

• You want to make all users edit the cells ['B2:B10', 'B13:B21'].
• You want to make the specific users edit the cells ['D2:D10', 'F2:F10'].

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);
  });
}

• I thought that the important point might be as follows. When the sheet is protected, the ranges ['B2:B10', 'B13:B21'] and ['D2:D10', 'F2:F10'] are set as the unprotected ranges. After this, the ranges ['D2:D10', 'F2:F10'] is protected with the email.

Testing:

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

• The cells ['B2:B10', 'B13:B21'] can be edited by "user A", "user B" and the owner of Spreadsheet.
• The cells ['D2:D10', 'F2:F10'] can be edited by "user A" and the owner of the Spreadsheet.
• Cells except for ['B2:B10', 'B13:B21'] and ['D2:D10', 'F2:F10'] can be edited by only the owner of the Spreadsheet.