When a cell in column I is edited with the value "Complete, you want to clear the contents of the cell on the same row in Column E.
Note some of the differences between this answer and your script:
- each of the watched values are defined (column and value) as is the target column. While this is NOT essential, it does makes it easier to manage.
- there is a single
IF
statement in which the conditions are described.
- each of the conditions is separated by
&&
("AND")
- The target cell doesn't need to be defined as an offset; it can just be defined as the same row, and a given column.
sheet.getRange(e.range.rowStart, clearCol).clearContent()
function onEdit(e) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
// identify the range and values to watch and clear
var watchCol=9; // Column I
var watchValue = "Complete";
var clearCol = 5; // Column E
//
// Logger.log("DEBUG: the column = "+e.range.columnStart+", and the value = "+e.value)
if (e.range.columnStart === watchCol && e.value === watchValue) {
// Logger.log("DEBUG: the conditions were met");
sheet.getRange(e.range.rowStart, clearCol).clearContent()
}
return false;
}