protect and unprotect a checkbox with a warning give me a erroneous behaviour

Question

The purpose is to be able to execute some code by checking the Checkbox. Checked visually remains you that the process is already executed. If you Uncheck you are warned of consequences to do it.

Checking a checkbox creates a protection protection.setWarningOnly(true), but unchecking protection.remove() does not erase this protection, as programmed in the AppsScript code.

I reduced the problem to a minimum Sheet, with a minimum code. the sheet is only a cell ("A1" in the example) with a checkbox. (You can Add More...) and a trigger 'OnEdit' to protectCells(e).

function protectCell(e) {
  var sheet = e.source.getActiveSheet();
  var eRange = e.range;
  var protection = eRange.protect();
  var isChecked = eRange.getValue();

  //Browser.msgBox("Checkbox state: " + isChecked);

  if (isChecked) {
    // Lock the cell when the checkbox is checked
    protection.setWarningOnly(true);
    //Browser.msgBox("Protection set with warning.");
  } else {
    // Unlock the cell when the checkbox is unchecked
    protection.remove();
    //Browser.msgBox("Protection removed.");
  }
}

you can test it at : 1

Duplicated protections

Answer 1

• As written in the doc, calling range.protect() duplicates the protection:

  If the range is already protected, this method creates a new protected range that overlaps the existing one.

  Therefore call .protect() only after removing all existing protections. Use the description as key to avoid removing protections not set by the script.

/**
 * @param {GoogleAppsScript.Events.SheetsOnEdit} e
 */
function protectCell(e) {
  const ss = e.source;
  const sheet = ss.getActiveSheet();
  const eRange = e.range;
  const thisProtectionDescription = 'protectedByScript1689';
  const isChecked = eRange.isChecked();

  //Don't run for all sheets
  if (sheet.getName() !== 'Sheet1') return;

  //Remove all existing RANGE protections with description=protectionDescription
  ss.getProtections(SpreadsheetApp.ProtectionType.RANGE).forEach(
    (protection) => {
      if (protection.getDescription() === thisProtectionDescription)
        protection.remove();
    }
  );

  if (isChecked && eRange.getA1Notation() === 'A1') {
    // Lock the cell when the checkbox is checked
    const protection = eRange.protect();
    protection.setDescription(thisProtectionDescription);
    protection.setWarningOnly(true);
    //Browser.msgBox("Protection set with warning.");
  }
}