google-apps-script

onEdit script runs first part but not second part


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

  }
}
}

Solution

  • 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.

    Full Code:

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

    Output:

    Note: For visual output only.

    Output