Remove protected cell

[Sebastian Quijas]

A brief description of the problem:
I would like to ask for help as I cannot make it so that when the checkbox is removed the range corresponding to that cell is unchecked, in this case it only removes the entered range of A2:E2 but I would like to remove the one for each corresponding one.

Quisiera quitar el de cada uno correspondiente y solo quito uno


A small code sample that reliably reproduces the problem. The sample should run as-is or with minimal configuration, with no external dependencies.
function protectValue() {
  var ActiveCell = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getActiveCell();
  var ActiveRow = ActiveCell.getRow();
  var ActiveSheet = ActiveCell.getActiveSheet();
  var desiredCol = 8;
  var rowUnwanted = 1;
  var sheetName = "Payments";

  if (activeCell.getColumn() == desiredCol && activeCell.getRow() != undesiredRow && activeCell.getSheet().getName() == sheetName && activeCell.getValue() === true) {
    var protectionss = activeSheet.getRange(activeRow, 1, 1, 1, 5);
    var protection = protectionss.protect().setDescription('Locked');
    protection.addEditors(protection.getEditors());
  } else {
    var protections = pagosss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
    for (var i = 0; i < protections.length; i++) {
      var protectionq = protections[i];
      if (protectionq.canEdit() && (protectionq.getRange().getA1Notation() === 'A2:E2')) {
        console.log(protectionq.getRange().getA1Notation());
        protectionq.remove();
      }
    }
  }
}

[cbmserv...@gmail.com]

Hi Sebastien,

 

I modified your script to get it to work on correct range. You had the A2:E2 hardcoded which means it will only handle that range. I also turned it into an onEdit simple trigger to get the value and row/column numbers of what is being changed

 

Try this:

 

function onEdit(e) {

  var row = e.range.getRow();

  var col = e.range.getColumn();

  var newVal = e.value;

  var activeSheet = SpreadsheetApp.getActiveSheet();

  var desiredCol = 8;

  var rowUnwanted = 1;

  var sheetName = "protect";

  var ui = SpreadsheetApp.getUi();

  ui.alert("New Value: " + newVal);

  if (col == desiredCol && row != rowUnwanted && activeSheet.getName() == sheetName && newVal == "TRUE") {

    var protectionss = activeSheet.getRange(row, 1, 1, 5);

    var protection = protectionss.protect().setDescription('Locked');

    protection.addEditors(protection.getEditors());

  } else {

    var protections = activeSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);

    for (var i = 0; i < protections.length; i++) {

      var protectionq = protections[i];

      if (protectionq.canEdit() && (protectionq.getRange().getRow() === row)) {

        protectionq.remove();

}

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/44c3c9cb-fc58-4ec7-ba66-1423c7e802a4n%40googlegroups.com.

[Greg Osborne]

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/009d01d8d45c%24531a6c70%24f94f4550%24%40gmail.com.