Removing protection from a range of cells before automatically deleting with a google script based on date

Question

I'm trying to make a custom shift cover Google sheet in which people can add the shifts they need to be covered and then others can pick up the shifts. As part of this, I have written two pieces of google script code. The first one automatically protects each cell a user types in so shift covers and times can't be deleted or changed by anyone except the owner or the original editor. This script also removes protection from cells when they are cleared. The second script automatically deletes all the shift covers which have passed to keep the sheet tidy. Both of these scripts work well on their own but together they are causing an issue which I can't seem to fix. When the auto-delete script runs automatically at the end of every day the protection rules for the cells are left behind but since these cells don't exist anymore they just show up as "#REF" in the protection list. This doesn't cause any problems immediately but over time there are going to be hundreds of them building up which I think could cause some problems. Is there a way that the delete script can remove all protections from the selected cells before deleting them? The "#REF" I'm talking about

The auto protection code:

function onEdit(e){
if (e.value == null){
  let prot = SpreadsheetApp.getActiveSheet().getProtections(SpreadsheetApp.ProtectionType.RANGE);
  for (let i in prot){
    if (prot[i].getRange().getA1Notation() == e.range.getA1Notation())
      prot[i].remove();
  }
} else {
  let protection = e.range.protect();
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit())
    protection.setDomainEdit(false);
}
}

The auto-delete code:

function deleterow() {
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 3;
var numRows = sheet.getLastRow()-1;
var dataRange = sheet.getRange(startRow, 2, numRows);
var data = dataRange.getValues();
var today = new Date();
today.setHours(0,0,0,0);
for (i = data.length-1; i > -1; i--) {
  var row = data[i];
  var sheetDate = new Date(row);
  sheetDate.setHours(0,0,0,0);
  if (today > sheetDate) {
    var range = sheet.getRange(i+3,2,1,15);
    range.deleteCells(SpreadsheetApp.Dimension.ROWS)
  }
} 

}

Ideally I would like the auto-delete code to delete the cell and their protections or if there is a way to have another script that deletes any protections which are #REF that would work too.

Answer 1

I believe your goal is as follows.

• When the cells are deleted by the function deleterow(), you want to remove the protected ranges.
• From your onEdit and your image, each protected range is always a single cell.

If my understanding is correct, how about the following modification? In this modification, your deleterow is modified.

Modified script:

function deleterow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var p = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  var obj = p.reduce((o, e) => {
    var range = e.getRange();
    var k = `${range.getRow()}_${range.getColumn()}`;
    o[k] = o[k] ? [...o[k], e] : [e];
    return o;
  }, {});
  var startRow = 3;
  var numRows = sheet.getLastRow() - 1;
  var dataRange = sheet.getRange(startRow, 2, numRows);
  var data = dataRange.getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  for (i = data.length - 1; i > -1; i--) {
    var row = data[i];
    var sheetDate = new Date(row);
    sheetDate.setHours(0, 0, 0, 0);
    if (today > sheetDate) {
      var range = sheet.getRange(i + 3, 2, 1, 15);
      var rowStart = range.getRow();
      var rowEnd = rowStart + range.getNumRows() - 1;
      var colStart = range.getColumn();
      var colEnd = colStart + range.getNumColumns() - 1;
      for (var r = rowStart; r <= rowEnd; r++) {
        for (var c = colStart; c <= colEnd; c++) {
          var k = `${r}_${c}`;
          if (obj[k]) {
            obj[k].forEach(e => e.remove());
          }
        }
      }
      range.deleteCells(SpreadsheetApp.Dimension.ROWS);
    }
  }
}

When this script is run, before range.deleteCells(SpreadsheetApp.Dimension.ROWS);, when the cells sheet.getRange(i + 3, 2, 1, 15) are protected, those are removed. But, in this case, from your question, it supposes that the protected ranges are the single cell. Please be careful about this.

Sample script:

About if there is a way to have another script that deletes any protections which are #REF that would work too., when you want to remove the protected ranges of #REF!, how about the following sample script?

function sample() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var p = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  p.forEach(e => {
    if (e.getRange().getA1Notation() == "#REF!") {
      e.remove();
    }
  });
}

When this script is used, your function deleterow might be able to be modified as follows.

function deleterow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var numRows = sheet.getLastRow() - 1;
  var dataRange = sheet.getRange(startRow, 2, numRows);
  var data = dataRange.getValues();
  var today = new Date();
  today.setHours(0, 0, 0, 0);
  for (i = data.length - 1; i > -1; i--) {
    var row = data[i];
    var sheetDate = new Date(row);
    sheetDate.setHours(0, 0, 0, 0);
    if (today > sheetDate) {
      var range = sheet.getRange(i + 3, 2, 1, 15);
      range.deleteCells(SpreadsheetApp.Dimension.ROWS)
    }
  }

  SpreadsheetApp.flush(); // This line might not be required to be used.
  var p = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
  p.forEach(e => {
    if (e.getRange().getA1Notation() == "#REF!") {
      e.remove();
    }
  });
}