I have one sheet where I want to add a date depending on entries into two different cells. So when a person adds their name, it adds a start date, and when they change status to complete, it adds an end date.
I had them in separate scripts initially and noticed whichever one was second in the list worked but the other one didn't. So I tried to combine them into one and now the first part (name adds start date) works but the second part doesn't.
So far, I can't figure out what I've done wrong but this is all a new experience! Thank you for any help!
function onEdit (e) {
var range = e.range;
var sheet = range.getSheet();
//set start date when owner is entered
if (range.getColumn() == 3 && sheet.getName() === "Projects"){
var ownerCell = range.getValue();
if (ownerCell !=="") {
var startdateCell = range.offset(0,5);
var currentDate = new Date();
startdateCell.setValue(currentDate);
} else {
range.offset(0,5).clearContent();
}
//set end date when completed is selected
if (range.getColumn() == 6 && sheet.getName() === "Projects"){
var statusCell = range.getValue();
if (statusCell ==="Complete") {
var enddateCell = range.offset(0,3);
var finishDate = new Date();
enddateCell.setValue(finishDate);
} else {
range.offset(0,3).clearContent();
}
}
}
}
The reason why the second part doesn't work
is because if (range.getColumn() == 6 && sheet.getName() === "Projects")
is inside if (range.getColumn() == 3 && sheet.getName() === "Projects")
.
To resolve this, move if (range.getColumn() == 6 && sheet.getName() === "Projects")
outside so both conditions are checked independently.
function onEdit(e) {
var range = e.range;
var sheet = range.getSheet();
if (range.getColumn() == 3 && sheet.getName() === "Projects") {
var ownerCell = range.getValue();
if (ownerCell !== "") {
var startdateCell = range.offset(0, 5);
var currentDate = new Date();
startdateCell.setValue(currentDate);
} else {
range.offset(0, 5).clearContent();
}
}
if (range.getColumn() == 6 && sheet.getName() === "Projects") {
var statusCell = range.getValue();
if (statusCell === "Complete") {
var enddateCell = range.offset(0, 3);
var finishDate = new Date();
enddateCell.setValue(finishDate);
} else {
range.offset(0, 3).clearContent();
}
}
}
Note: For visual output only.