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

4
votes

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);
 }
3
google-apps-scriptgoogle-sheetsgoogle-docs
Is “asking for code” appropriate if I intend to offer bounty? No, it's not. – Mogsdad
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. – hakarune
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) – Mogsdad

3 Answers

4
votes

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!

4
votes

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.

1
votes

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