Protect row when drop down menu changes to “Locked” using app scripts

Question

I am trying to simply protect an entire row (add editors) when someone changes the drop-down menu to "Locked", even the drop-down menu should be protected that is on the same row. This is something that I want to implement in almost all rows of the sheet.

I have found multiple resources that help but I can't combine it properly, would be very thankful for any help!

Bellow are some helpful links I found.

Google sheets: protect cells conditionally?

Protecting Cells Based on Contents of Other Cells in Google Sheets

Protect ranges with google apps script

Shrvan D Shrvan D · Accepted Answer · 2018-09-11T15:58:39

Add this code to your spreadsheet's script editor and set an onEdit trigger for the below mentioned function.

Replace the [email protected] string in code with your actual email

NOTE : This script assumes that first row of the sheet may have three option depending on which the protection of that range is controlled.

Lock : locks that particular column

Unlock : unlocks that particular column

blank : does nothing

function onEdit(e){
  var x = [e.range.columnStart,e.range.rowStart,e.range.columnEnd,e.range.rowEnd]
  if (x[0]==x[2] && x[1]==1 && x[3]==1){
    var lockRangeStart = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(1,x[0]).getA1Notation();
    var lastCol = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getLastRow();
    var lockRangeName = (lockRangeStart+":"+lockRangeStart.match(/(\D*)/)[1]+lastCol);
    var lockRange = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getRange(lockRangeName);
    if(e.value == "Lock"){
      var protection = lockRange.protect().setDescription('Locked Range');
      protection.addEditor("[email protected]");
      protection.removeEditors(protection.getEditors());
      if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
      }
    }
    else  if(e.value == "Unlock") {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      var protections = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
      for (var i = 0; i < protections.length; i++) {
        var protection = protections[i];
        if (protection.canEdit() && protection.getRange().getA1Notation() == lockRangeName ) {
          protection.remove();
        }
      }
    }
  }
}

Protect row when drop down menu changes to “Locked” using app scripts

1 Answers