How to make onEdit(e) run on protected sheet? Google Apps Script

1
votes

I have a code:

function onEdit(e){
  if (e.range.columnStart != 7 && e.range.columnStart != 8 || e.value <= 0) return;
  let d = new Date();
  if (e.range.columnStart == 7 && e.value == null){
      e.range.offset(0,-2).setValue(null);}
      else if (e.range.columnStart == 7 && e.value != null) {
      e.range.offset(0,-2).setValue(d);}
      else if (e.range.columnStart == 8 && e.value != null) {
      e.range.offset(0,-3).setValue(d);}
}
function Lock(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var protection = sh.protect().setDescription('Lock');
  var me = Session.getEffectiveUser();
  protection.addEditor(me).setUnprotectedRanges(sh.getRange("I2:I").createTextFinder("^(?!Done).*$").matchEntireCell(true).useRegularExpression(true).findAll().map(r => r.offset(0, -3, 1, 3)));
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()){
  protection.setDomainEdit(false);}
}
  • After running 2 functions. I edit in column 7 or column 8, will put the current date in the specified cell (column 5 in sheet), and will lock the range if I2:I has the value Done.
  • Everything works great with owner. But collaborators who are authorized to edit the spreadsheet cannot, onEdit(e) cannot run (error : You are trying to edit a protected cell or object. Please contact the spreadsheet owner to disable protection if you need to edit). Because function Lock() protected the sheet leaving only those cells where column I2:I is not done.
  • Is there any solution? Thanks!
1
google-apps-scriptgoogle-sheets

1 Answers

2
votes

I thought that in your situation, to use the simple trigger might be the reason of your issue. So, how about the following modification? In this modification, the function name is changed from onEdit to installedOnEdit, and the installable OnEdit trigger is installed to the renamed function.

From:

function onEdit(e){

To:

function installedOnEdit(e){

And also, please install the OnEdit trigger to the function installedOnEdit. And, please test it again.

Reference: