Skip to content

Why is this ‘else if’ function not working with onEdit on Google Appscript?

I want to make it so whenever a cell in column 9 is edited, if the new cell value is different from “Ok”, the range “newRange” gets it value set to “Yes”; and if the new cell value is equal to “Ok”, “newRange” gets its content cleared.

Here’s my attempt:

function onEdit(e){
  
  var range = e.range;//The range of cells edited
  var columnOfCellEdited = range.getColumn();
  
  var newRange = range.offset(0,3);  
  var rangeValue = range.getValue();
  
  if (columnOfCellEdited === 9 && rangeValue != "Ok") {
    newRange.setValue("Yes");
  }
  else if (columnOfCellEdited === 9 && rangeValue === "Ok") {
    newRange.clear();
  }
}  

The first part works great, but the content in “newRange” doesn’t get cleared if I change the value of a cell in column 9 to “Ok”. Can someone please tell me what I’m doing wrong? Thanks in advance.

Answer

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Your desire sheet name') {
    if (e.range.columnStart == 9 && e.value != "Ok") {
      e.range.offset(0, 3).setValue("Yes");
    } else if (e.range.columnStart == 9 && e.value == "Ok") {
      e.range.offset(0, 3).clear();
    }
  }
}