Trying to set a formula in a cell only when a checkbox is checked.

So far, the script is as follows, however ends up setting the formula regardless of the checkbox status and doesn't function on the checkbox status.

function onEdit(e) {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var cbxRnge = ss.getRange('B3')
  if (cbxRnge == true) {
    return;
  }
  var cell = ss.getRange("D3");
cell.setFormula('=IF(F3=1,"YES","NO")');
}

Hoping for the formula to remain in the cell until checkbox is unchecked

Expected Results

Checkbox Unchecked

.

Checkbox Checked

EDIT:

Have thought to remove the formula when the checkbox is false:

  if (cbxRnge == false) {
    return;
  }
  var cell = ss.getRange("D3");
cell.clearContent();

however ends up clearing and not returning the function when true.

Sample Sheet

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet1" && e.range.columnStart == 2 && e.range.rowStart == 12 && e.value == "TRUE") {
    sh.getRange("D13").setFormula("=J13>=1")
  }
}