0

I've been trying to make a script to protect a row when one of the drop down values from the row is set to a certain value ('ON HOLD'). The closest I've been to finding a solution was (Protect row when drop down menu changes to “Locked” using app scripts) unfortunately that solution isn't descriptive so I don't know where to begin changing it. This is what I have so far on my solution

function onEdit(event){
var eventsheet = event.source.getActiveSheet();
var editedcell = eventsheet.getActiveCell();

var step = event.range;
var step2 = step.getRow();

Logger.log(step2);

var protectcol = 5;

if(editedcell.getColumn() == protectcol){
  if(editedcell.getDisplayValue() == 'ON HOLD'){
    var protectionrg = step2.protect();
    protectionrg.addEditor('Email@mail.com'); //Entire Row Protect
  }
 } 
}

What should I do?

David-
  • 47
  • 5
  • There's not a good way to do what you're attempting. Essentially you're trying to have a protected sheet, that is totally available to edit (effectively unprotected) then setting up exceptiong ranges as on and off. It could be done, but I'd recommend finding a different approach. – pgSystemTester Jul 27 '21 at 20:15

1 Answers1

2

Here is a sample code:

function onEdit(event){
var eventsheet = event.source.getActiveSheet();
var editedcell = event.range;

var rowRange = eventsheet.getRange("A"+editedcell.getRow()+":"+editedcell.getRow());

Logger.log(rowRange.getA1Notation());

var protectcol = 5;

  if(editedcell.getColumn() == protectcol){
    if(editedcell.getDisplayValue() == 'ON HOLD'){
      var protectionrg = rowRange.protect();
      protectionrg.addEditor('Email@mail.com'); //Entire Row Protect
      protectionrg.removeEditors(protectionrg.getEditors());
      Logger.log("PROTECTED");
    }
    else{
      //Remove protection
      var protections = eventsheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      protections.forEach(protection => {
        var protectedRange = protection.getRange();
        if(protectedRange.getRow() == editedcell.getRow()){
          //Remove protection
          protection.remove();
        }
      });
    }
  } 
}

Pre-requisite:

Use Installable On-Edit Trigger

enter image description here

Modifications done:

  • getRow() returns an integer. If you want to use protect(), you need to get either sheet or range object. In your case, you want to protect the entire row. Hence you need to get the range of the entire row. I used Sheet.getRange(a1Notation) to get the range of the entire row, where a1Notation is in this format A1:1 (to select row1)
  • When the cell in columnE is set to ON HOLD, protect the entire row by adding your preferred editor and by removing existing editors using removeEditors(emailAddresses)

Note:

removeEditors() does not allow the current user to be removed. If the sheet was modified by another user (not Email@mail.com) and set it to ON HOLD. Your simple onEdit trigger won't be able to remove this another user as an editor in your protected range. As a workaround, you need to use onEdit installable trigger, to run the code created by the owner.

Reference: Unable to remove the effective user from editing rights of protected range using script - google sheets

  • Lastly, When the cell in columnE is set any value other than ON HOLD, get all protected range in the current sheet using getProtections(type), with protection type set to RANGE. Then loop all existing protection. Check if the protected range's row index is the same with the modified cell's row index. Remove protection using remove()
Ron M
  • 5,791
  • 1
  • 4
  • 16
  • Please let me know if you encountered some issues so i could update the answer accordingly – Ron M Jul 27 '21 at 22:46
  • Luckily haven't encountered any issues yet, this solution is working though! Thanks very much for the help – David- Jul 28 '21 at 12:35