0

I need to remove protection for a user that has access to certain cells but needs all the rows and columns when the script is running. After that i then need to protect the sheet again apart from the editable cells.

// Unprotect cells F:AP in addition to any other unprotected ranges in the protected sheet.
var sheet = SpreadsheetApp.getActiveSheet();
var protection = sheet.protect();
var unprotected = protection.getUnprotectedRanges();
unprotected.push(sheet.getRange('F:AP'));
protection.setUnprotectedRanges(unprotected);

// Hides columns

var sheet = ss.getSheets()[1];
var range = sheet.getRange("F:AP");
sheet.hideColumn(range);

The above code works for me, but for someone else it throws an error as they don't have permission to edit.

aynber
  • 22,380
  • 8
  • 50
  • 63
Robert Hall
  • 191
  • 3
  • 11

1 Answers1

0

To protect the sheet apart from editable rows you could use this code,

function protectsheet() {

  // Protect the active sheet, then remove all other users from the list of editors.

  var sheet = SpreadsheetApp.getActiveSheet();
  var protection = sheet.protect().setDescription('Protected sheet');

  var ranges = protection.getUnprotectedRanges();
  var range1 = sheet.getRange("A2:B6"); //Replace the range with the editable cells
  var range2 = sheet.getRange("D2:D6"); //Replace the range with the editable cells 
//Skip this if you have one range
  ranges.push(range1);
  ranges.push(range2); //Skip this if you have one range
  protection.setUnprotectedRanges(ranges); //This combines both the ranges into an array

// Ensure the current user is an editor before removing others. Otherwise, if the user's edit
// permission comes from a group, the script throws an exception upon removing the group.
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
   protection.setDomainEdit(false);
 }
  protection.addEditors(["EmailID"])
}

Hope this helps!

Mishal
  • 450
  • 9
  • 27