google-sheetsgoogle-apps-script

How do I stop Google AppScripts (Sheets) from creating two protected range entries


I have a Google sheet where I have set a row to become protected when I check a tickbox in Col U. But when I perform this action with the account I setup the code with, it makes two entries of protect ranges.

When I tick the box from another account, I get only 1 entry and it seems to work correctly.

Double entry of range protection with main account

Sample 1

Correct single entry when using a different account

Sample2

My code is the following. I made it to only protect rows with a warning.

function onEdit() {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getActiveSheet();
  let cell = sheet.getActiveCell()
  let col = cell.getColumn();//gets column numnber
  let rownum = cell.getRow();//gets row number
 

  if(col==21 && cell.getValue() == true){//if changed to true protects row to end of data
    let protRange = sheet.getRange(rownum,1,1,22).protect().setWarningOnly(true).setDescription(rownum);
    }
  
  else if(col == 21 && cell.getValue() ==false){//if changed to false cycles through all the protected ranges on the sheet and unprotects if the row number is the same
    let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE)
    for (let i = 0;i<protections.length;i++){
      let proDesc = protections[i].getDescription();
      if(parseInt(proDesc)==rownum){
      protections[i].remove()
      }
    }
  }
}

Solution

  • I changed this part of your code:

    if (col == 21 && cell.getValue() == true) {//if changed to true protects row to end of data
      let protRange = sheet.getRange(rownum, 1, 1, 22).protect().setWarningOnly(true).setDescription(rownum);
    }
    

    Your code does not check for existing protections. As a result, every time the checkbox is checked, it creates a new protection, which causes the main problem. To handle duplicate protections for the same row, I use the some() method before creating a new one to avoid duplicate entries.

    I also converted rownum to a string using toString(), to ensure compatibility with setDescription().

    Complete Code:

    function onEdit() {
      let ss = SpreadsheetApp.getActiveSpreadsheet();
      let sheet = ss.getActiveSheet();
      let cell = sheet.getActiveCell();
      let col = cell.getColumn();
      let rownum = cell.getRow();
    
      if (col == 21 && cell.getValue() === true) {
        let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
        let isAlreadyProtected = protections.some(protection => {
          return protection.getRange().getRow() === rownum && protection.getRange().getNumRows() === 1;
        });
    
        if (!isAlreadyProtected) {
          sheet.getRange(rownum, 1, 1, 22).protect()
            .setWarningOnly(true)
            .setDescription(rownum.toString());
        }
      } else if (col == 21 && cell.getValue() === false) {
        let protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
        for (let i = protections.length - 1; i >= 0; i--) {
          if (protections[i].getDescription() === rownum.toString()) {
            protections[i].remove();
          }
        }
      }
    }
    

    Sample Output:

    Output

    Reference:

    Class Protection

    some()