Remove protection from a specific cell when contents are deleted

Question

I am trying to remove the protection from a specific cell after its content is deleted and then change its background to white.

Here is the script:

    function onEdit(e) {
      var spreadsheet = SpreadsheetApp.getActive();
      const range = e.range;
      var cell = SpreadsheetApp.getActiveSheet().getActiveCell();
      var a1o = cell.getA1Notation();
      a1 = "'" +a1o+ "'"
      var val = cell.getValue();
    
      if (val == "") {
        var allProtections = 
        spreadsheet.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
        var matchingProtections = allProtections.filter(function(existingProtection) {
          return existingProtection.getRange().getA1Notation() == +a1;
        });
        var protection = matchingProtections[0];
        protection.remove();
        ss.getActiveRangeList().setBackground('White');
      }
    }

Answer 1

If the user at the keyboard has editor access to a protected range, they can edit the range directly regardless of the protection. If they do not have editor access to a protected range, they cannot modify a value in the range in the first place, and no events get generated when they attempt to do so.

This means that you can only remove protection of a range "on the fly" when the user already has editor access to the range. The removal does not have effect on the user at the keyboard — it only opens up the range for editing for other users who until then may not have add editor access to the range.

Another issue is that the onEdit(e) function is a simple trigger. Simple triggers run in a restricted context where the identity of the user at the keyboard is usually not available. I don't think you can remove range protections in that context.

To make it work, rename the onEdit(e) function to something like removeProtectionOnEdit(e) and run it on an installable "edit" trigger.

These triggers run every time any value in the spreadsheet is edited, so it makes sense to spend a few minutes to optimize them. The most important thing is to minimize the number of SpreadsheetApp API calls. Use the event object e instead. See onEdit trigger best practices.

To detect whether e.range is within one of the protected ranges, you can use my getRangeIntersection_() utility function, like this:

  allProtections
    .filter(prot => getRangeIntersection_(e.range, prot.getRange()))
    .forEach(prot => prot.remove());

Note that the above may remove protection from multiple cells in the event the edited cell is part of a larger protected range.

Also note that it would be easier to protect the whole sheet instead of working with multiple protected ranges in the sheet. You could then just add the newly edited range to the sheet protection's list of "Except certain cells", like this:

/**
* When a single cell is edited, and the new value in the cell is blank,
* adds the cell to the sheet protection's list of unprotected ranges.
*
* Only works when the whole sheet has been protected. Does not work with
* ranges that have been protected individually.
* Runs on an installable trigger every time the user manually edits the spreadsheet.
* @see https://developers.google.com/apps-script/guides/triggers/installable
*
* @param {Object} e The "edit" event object.
*/
function removeProtectionOnEdit(e) {
  if (e.value || e.range.getHeight() || e.range.getWidth()) {
    return;
  }
  const sheetProtection = e.range.getSheet().getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (!sheetProtection) {
    return;
  }
  const unprotected = sheetProtection.getUnprotectedRanges();
  unprotected.push(e.range);
  sheetProtection.setUnprotectedRanges(unprotected);
}

See Sheet.getUnprotectedRanges() and Sheet.setUnprotectedRanges().

Finally, in the event you are only trying to "soft protect" cells against unintentional edits, but want the sheet to remain editable when the edit is purposeful, use the Show warning when editing this range when protecting the sheet or ranges. You will not need any code then.