google-sheetsgoogle-apps-scriptgoogle-appsspreadsheet-protection

Protecting sheet. Selective editing permissions in Google Sheet

I have a table like this: enter image description here

SourceSheet (sample data) is available: SourceSheet

I need to protect all cells but certain ranges can be edited by everyone and some ranges can be edited by only users specified by emails.

  var rangesToEdit = [
    "D5:G11",
    "L5:O11",
    "D16:G22",
    "L16:O22",
    "D27:G33"
  ];

Above ranges can be edited by anyone.

 var ranges = ["H5:H11", "P5:P11", "H16:H22", "P16:P22", "H27:H33"];

Above ranges can be edited only by certain users by emails.

For now I protected the whole sheet, then gave permissions to anyone for editing rangesToEdit range.

But when I specify ranges that can be edited only by certain users it does not work.

I have the code:

function editSheet() {
  try {
  var sheetUrl = "https://docs.google.com/spreadsheets/d/1jxDEj894Ir_BQx1uEHo1D3SBmC6_Rd0zo2locLN9KAE/edit?usp=drive_link";
  var sheetName = "February";
  
  var spreadsheet = SpreadsheetApp.openByUrl(sheetUrl);
  var sheet = spreadsheet.getSheetByName(sheetName);

  
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0; i < protections.length; i++) {
    protections[i].remove();
  }


  var rangesToEdit = [
    "D5:G11",
    "L5:O11",
    "D16:G22",
    "L16:O22",
    "D27:G33"
  ];

  
  var protection = sheet.protect().setDescription('Protected Range');
  var unprotectedRanges = protection.getUnprotectedRanges();
  for (var i = 0; i < rangesToEdit.length; i++) {
    unprotectedRanges.push(sheet.getRange(rangesToEdit[i]));
  }
  protection.setUnprotectedRanges(unprotectedRanges);



  var emailList = ["osstem.data@gmail.com"]; // Замените адресами электронной почты нужные адреса     
  var ranges = ["H5:H11", "P5:P11", "H16:H22", "P16:P22", "H27:H33"]; // Укажите нужные диапазоны
  
  for (var i = 0; i < ranges.length; i++) {
    var range = sheet.getRange(ranges[i]);
    var protection = range.protect().setDescription('Редактирование доступно только определенным пользователям.');
    
    protection.addEditor(emailList[0]);
  }
  
} catch (error) {Logger.log(error.toString());}
}

So all cells protected, rangesToEdit can be edited by anyone, but ranges for some reason can not be edited by specified emails. How to set users by emails who can edit ranges. Other protectir rules also must stay.

Solution

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

    Based on the answer of @Tanaike:

    function myFunction() {
  // Please set variables for your situation.
  var sheetUrl = 'https://docs.google.com/spreadsheets/d/1veORLw3OrcIAtv83n4Vn1E4bhj4gYqV9N2ebtRjztrA/edit?usp=drive_link'; // Please set your Spreadsheet ID.
  var sheetName = 'February';

  var editableRanges = ["D5:G11","L5:O11","D16:G22","L16:O22","D27:G33"];
  var rangesToProtect = ["H5:H11", "P5:P11", "H16:H22", "P16:P22", "H27:H33"];

  var allowedUsers = ['email@gmail.com']; // 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);
  });
}

    Result:

    Thanks to @Tanaike.