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.
Advertisement
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(); } } }