Google Sheets Script to lock or unlock a row depending on the value in the cell of a column-CodePudding

I use a script that allows me to lock a row if in the "BG" column I put an "X".

I would like that if I put a "0" then the protection is removed.

Also add a function so that the script runs automatically when I change the value in the "BG" cell.

My Script:

function Lock_Cells() {
var sheet = SpreadsheetApp.getActive();
for (var i = 5; i <= 1000; i  )
{
  var Check_Cell = "BG"   i;
  var Temp = sheet.getRange(Check_Cell).getValue();
  if (Temp == "X")
  {
     var Lock_Range = "B"   i   ":BG"   i;
     var protection = sheet.getRange(Lock_Range).protect();
     var description = "Ligne "   i;                 
     protection.setDescription(description);
     var eds = protection.getEditors();
     protection.removeEditors(eds);
  }
}  
}

CodePudding user response：

Instead of looping through all the rows, just use onEdit Trigger. onEdit Trigger will execute a function whenever a user edited or inserted value to the sheet. It has an Event Object which has property of range which you can use to determine the row and column of the edited cell. Using those properties you can easily lock a specific row.

Try this code:

function onEdit(e) {
  let range = e.range; //get the range of edited cell
  let row = range.getRow(); //get the row
  let col = range.getColumn(); //get the column 
  let value = e.value; //get the new value of edited cell
  let sheet = range.getSheet(); //get the sheet where edit is made
  
  if(col == 59 && row >= 5 && value == "X"){ //check if the edited cell is BG and row is equal or greater than 5 and value is X
    let lock_range = `B${row}:BG${row}`; //set lock range using row
    let protection = sheet.getRange(lock_range).protect() //set protection
      .setDescription(`Ligne ${row}`)  //add description
    protection.removeEditors(protection.getEditors()); //remove editors
  }else if(col == 59 && row >= 5 && value == "O"){ //check if the edited cell is BG and row is equal or greater than 5 and value is O
    var protectedRange = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); //get all protection with type range
    for (var i = 0; i < protectedRange.length; i  ) { //loop 
      if (protectedRange[i].getDescription() == `Ligne ${row}`) { //row matching
        protectedRange[i].remove(); //remove protection
      }
    }  
  }
}

Demo:

Test Sheet:

Adding "X":

Replacing "X" with "O":

References: