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

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