How to Remove Editors from Protected Cells or Permanently Protect Cells in Google Sheets

Question

I'm trying to permanently lock/protect certain cells on 14 different sheets (1 hidden from the workers for formula stuff). I have them all locked and no one can edit if I add them to it as an editor. But it is the template, I make copies of it for each client (and new clients) for the staff. The staff that works on the sheet and the employees are only allowed to edit certain cells for the work they do.

The problem is if I have Workbook1 with X cells locked on the different sheets, make a copy, rename it to Workbook - Client#ID, then add them employees John and Jane, who will be working on this client, as editors; they can now edit every cell, including the protected ones (they get added as editors to the protected cells too). It doesn't do this on the original, it only happens to the copy made of the template. I then have to go through all 13 sheets and remove them from the protected cells.

I'm trying to quickly remove them automatically with a script add-on that I want to turn into a button or something later...

Or is there a better way to fix this bug?

Google has an example of removing users and keeping sheet protected and I have tried to add in what I need to make it work, but it doesn't do anything when I run the test as an add-on for the spreadsheet. I open a new app script project from my spreadsheet and enter in the example code from google

   // Protect the active sheet, then remove all other users from the list of editors.
 var sheet = SpreadsheetApp.setActiveSheet(January);
 var protection = sheet.protect().setDescription('Activity Log');
 var unprotected = sheet.getRange('A2:N7');
  protection.setUnprotectedRanges([unprotected]);

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

Crescantia

Is “asking for code” appropriate if I intend to offer bounty? No, it's not. – Hosbein

It doesn't say give me code anywhere. I am demonstrating some attempt to use what little documentation google has. Their support literally says come here for help, so don't be rude dude. I'm working at trying to make it work myself and asking for help at the same time; perhaps you don't see me editing. Google has the shittiest documentation; it's merely here's an example and 1 liners as explanations. Forgive me for trying to get assistance where google says too. – Crescantia

Sorry you feel Iwas rude; not intended. I pointed out a meta post relevant to this question. I see your edits; but also that there was NO code before the bounty was offered, no evidence of effort until 2 hours ago. You'd received a prior answer, but acknowledged it just 1 hour ago, at which point your first comment appears to provide more specific specifications for code. You should have improved your question, rather than add a bounty. Your question is still too broad. You have no clear, specific problem statement. (Ref) – Hosbein

Answer 1

It MUST be run as SCRIPT and NOT as an add-on.

If you have already locked your sheets and made your exceptions you can easily use Google's example code. We can use a for loop to find all the sheets and names. Then add a button to the script to load at start.

function FixPermissions() {
  // Protect the active sheet, then remove all other users from the list of editors. Get all sheets in the workbook into an array
 var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
//Use a for loop to go through each sheet and change permissions and label it according to the name of the sheet
  for (var i=0; i < sheets.length; i++) {
    var name = sheets[i].getSheetName()
    var protection = sheets[i].protect().setDescription(name);
    // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
    // permission comes from a group, the script will throw an exception upon removing the group.
    var me = Session.getEffectiveUser();
    protection.addEditor(me);
    protection.removeEditors(protection.getEditors());
    if (protection.canDomainEdit()) {
      protection.setDomainEdit(false);
    }
  } 
}


//A special function that runs when the spreadsheet is open, used to add a custom menu to the spreadsheet.

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Fix Permission', functionName: 'FixPermissions'}
  ];
  spreadsheet.addMenu('Permissions', menuItems);
}

Now in the menu bar you will see a new item when you reload/load the spreadsheet labeled Permissions

Crescantia

var range = gpsht.getRange('A'+(i+1)+':Q'+(i+1)); var protection = range.protect().setDescription('Locked'); protection.removeEditors(users); Is giving error stating "Spreadsheet timed out accessing document with id ...." – Challis

Answer 2

Adding on @KRR's answer.

I changed the script to be dynamic.

function setProtection() {
  var allowed = ["[email protected],[email protected]"];
  addProtection("Sheet1","A1:A10",allowed);
}

function editProtection(sheetname,range,allowed,restricted) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetname);
  var range = sheet.getRange(range);

  //Remove previous protection on this range
  var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (var i = 0;i<protections.length;i++) {
    if (protections[i].getDescription() === sheetname + range){
      protections[i].remove();
    }
  }

  //Set new protection
  var protection = range.protect().setDescription(sheetname + range);

  // First remove all editors
  protection.removeEditors(protection.getEditors());

  // Add array of emails as editors of the range
  if (typeof(allowed) !== "undefined") {
    protection.addEditors(allowed.toString().split(","));
  }
}

You can add as many options as you want and make them run onOpen. Set your variable and call editProtection as many times as you need.

You can get the emails dynamically from spreadsheet editors.

Also you might want to add another script to protect the whole sheet and set you as the owner. Hope this helps.

Junoesque

Thank you, this would be perfect if I just needed a few cell ranges locked, sadly I need all but like 10 locked. I can't even get the example code to work either :(. I wanted to use the example code and just repeat it for each sheet and add getSheetByName(sheetname); instead of SpreadsheetApp.getActiveSheet(); – Crescantia

What did not work in the code? What is the error. Also you can add as many functions as you want in setProtection() and just run it. You can for example lock all sheets, and then add editors to those 10 ranges – Junoesque

the code worked, but the results aren't as easy or clean as the setup I finally figured out. This works fine for removing all but X users from protected permissions on ranges. The result though is it has to be individual sheets and is only ranges. I was trying to protect all but a couple cells or cell ranges (5 different small ranges/cells). This can do it, but it's messy looking on the board afterwards, you get a lot of protected blocks on the sidebar (at least for my sheets), makes it hard to manage. Using the protect sheet loop and setting up exclusions on each sheet prior is a lot cleaner – Crescantia

Clarify, my first comment I was referring to google's example code, and my error was trying to run it as an add-on instead of script. I thought the new add-on section was how scripts were working these days with all their changes. – Crescantia

Answer 3

For this you can write a script function to set the protection ranges and add editors for the sheets as well.

Please check the sample apps script code to add protection for a range in a sheet below:

function addProtection()
{

// Protect range A1:B10, then remove all other users from the list of editors.
 var ss = SpreadsheetApp.getActive();
 var range = ss.getRange('A1:B10');
 var protection = range.protect().setDescription('Sample protected range');

// var me = Session.getEffectiveUser();
  // array of emails to add them as editors of the range
 protection.addEditors(['email1','email2']);
  // array of emails to remove the users from list of editors 
 protection.removeEditors(['email3','email4']);
}

Hope that helps!

Hollowell

Close, I saw the examples, but what I need is more like example #3 from there doc in like a for each sheet loop that excludes certain cells depending on the page and to do it via a button or something. And I don't know how to do this with this, I barely know apps script. – Crescantia

The sheets are all the months + yearly overview + hiddeb formulas sheet. – Crescantia

I'm trying to merge the examples to get a working test going, but it's not working as of yet – Crescantia