So I'm currently working on Protected sheets & ranges. Scripts are included with this. The thing is I have these two buttons which are the Lock and Unlock.

Everything works fine on the owner's side. So, if we will view this as the user or client-side:

The Lock button functions as it removes the protected range setting that is created by the owner then creates a new setting that disables all the range from editing. The lock button is accessible by the owner and editors.

The Unlock button functions as it removes the settings that the lock button has been created and then create settings that have the "except certain cells" for the ranges that need to be editable. So, for the user side, I'm thinking that this button will only be accessible by the owner. The owner is the only one who can use the unlock button for security.

So the thing is when clicking the lock button on the user side, it gives me an error that says...

> Exception: You are trying to edit a protected cell or object. Please contact the spreadsheet owner to remove protection if you need to edit.

But I think I figured out the problem. The error shows when the user is not the one who created the said setting. In short, when the owner is the one who creates the protected settings, the user can't edit it.

So, I'm trying to figure out how can the editors use the two buttons without receiving that error. I'm new to this area so I don't have an idea on how to exactly execute this thing. As the owner, I need to create the settings before the user can open the file and be the one who can only unlock the sheet for security purposes.

Here's the visual of the thing that I'm working on...

Capture.JPG

Here's the code that I'm currently using

const row10 = SpreadsheetApp.getActiveSheet().getRange('B10').getValue();
const lock_row10 = () => Lock(row10);
const unlock_row10 = () => Unlock(row10);

const row11 = SpreadsheetApp.getActiveSheet().getRange('B11').getValue();
const lock_row11 = () => Lock(row11);
const unlock_row11 = () => Unlock(row11);

const row12 = SpreadsheetApp.getActiveSheet().getRange('B12').getValue();
const lock_row12 = () => Lock(row12);
const unlock_row12 = () => Unlock(row12);

const row13 = SpreadsheetApp.getActiveSheet().getRange('B13').getValue();
const lock_row13 = () => Lock(row13);
const unlock_row13 = () => Unlock(row13);

function Unlock(sheetName1) {
  
  var owner = "owner-email@gmail.com"
  var me = Session.getEffectiveUser();
  
  if(me == owner) {
    var confirm = Browser.msgBox('Confirmation','Are you sure you want to unlock this sheet?',Browser.Buttons.YES_NO);
    if(confirm =='yes'){
      var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
      var protection = sheet1.protect().setDescription('Sample protected range');
      var unprotected = protection.getUnprotectedRanges();
      unprotected.push(sheet1.getRange('F9:O52'));
      unprotected.push(sheet1.getRange('S9:AB52'));
      protection.setUnprotectedRanges(unprotected);
      protection.addEditor(me);
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
    Browser.msgBox("The sheet is now unlocked!")
  } else{
    Browser.msgBox('Access Denied','Please contact your IT Officer to unlock the sheet. \\nThank you!',Browser.Buttons.OK);
  }
}

function Lock(sheetName1) {
  
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName1);
  var protection = sheet1.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection && protection.canEdit() && sheet1.protect().setDescription('Sample protected range')) {
    protection.remove();
  }
  var confirm = Browser.msgBox('Confirmation','Are you sure you want to lock this sheet?',Browser.Buttons.YES_NO);
  if(confirm=='yes'){
    LockSheet(sheet1);
    Browser.msgBox("The sheet is locked!")
  }
}

function LockSheet(sheet1) {
  var protection = sheet1.protect().setDescription('Sample protected sheet');
  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
}