Protect range (not entire rows, but multi height and partial width) based on another cell value-CodePudding

NOTE: my question is NOT answered by Protecting/unprotecting range based on another cell value My question asks about a range, with only part of the rows selected, NOT entire rows as in that question. I have extensively searched the site and there isn't anything that answers my question.

Please read my query properly prior to linking it to something else that does not answer it.

QUESTION

I have created a shared test Google Sheets workbook (TestWBook) to illustrate what I am trying to do. The 'Outgoing' sheet is where I need the restrictions to be applied; it is used to store order data.

The whole 'Outgoing' sheet (A1:AS), except range A2:M882, is currently protected so that only the owner and two other editors can make changes to it (let's call them [email protected] and [email protected]).

Other editors enter data in A2:M882 to specify new order requirements (which is why this range started by not being protected).

I now need to protect each row between columns A and M(active_row) from all editors but the owner and two other named editors ([email protected] and [email protected]) whenever a date is entered in column O (Date Sent) (or if that cell is not blank, either would work).

I don't want to protect an entire single row every time the script runs, as I don't want to end up with hundreds of protected ranges.

I want the protection to be applied dynamically to the range from A2 to M(active_row), so that effectively that one protected range is updated every time a date/value is entered in column O.

I have been looking through scripts for the last two days and cannot find a way to specify this dynamic range (sorry, I can adapt scripts but cannot write them up from scratch!)

Can anyone help?

CodePudding user response：

Try

function onEdit(event) {
  var sh = event.source.getActiveSheet();
  var rng = event.source.getActiveRange();
  if (sh.getName() != 'Outgoing') return;
  if (rng.getColumn() != 15) return;

  // save the protection parameters
  var p1 = sh.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];

  // delete actual protection
  let protection = sh.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
  if (protection) { if (protection.canEdit()) { protection.remove(); } }

  // rebuild protection
  var p2 = sh.protect();
  p2.setDescription(p1.getDescription());
  p2.setWarningOnly(p1.isWarningOnly());
  if (!p1.isWarningOnly()) {
    p2.removeEditors(p2.getEditors());
    p2.addEditors(p1.getEditors());
  }

  // define new uprotected area
  p2.setUnprotectedRanges([sh.getRange('A'   rng.getRow()   ':M882')]);
}