Script to Set a Due Date a Week from Today When a Cell is Edited

Question

As the title says, I am adding a due date one week from today to a cell when one of two adjacent cells are edited. The first cell is the contributor, so when someone is assigned to the task the due date is set. The second cell is completion status, so when a task is set to "Completed" it will change the due date cell to say "done".

I don't want this to change when the current date changes. Also want it to stay the same if the conributor is edited later.

My first attempt had the script on individual cells and it would be automatically called when any of the parameters were edited. Not currently using the onEdit() trigger.

function setDate(contrib, progress) {
 //contrib: the current assigned contributor
 //progress: cell showing progress state (Not started, begun, completed)

  var range = SpreadsheetApp.getActiveRange();
  var d = range.getValue(); 

  if(progress == 'Completed') { return 'done'; } 
  if(d instanceof Date) { return; }
  if(contrib == '') { return; }
//if progress is completed, it should always return done
//if there is already a date there, it should not change it
//if contrib is blank, as long as there wasn't already a date, we don't want to add one

  today = new Date();
  due = new Date();
  due.setDate(today.getDate() + 7); //one week from today
  return due;
}

The issue is, when you edit the contributor or progress status, it changes the due date cell to "Loading..." so I can't actually retrieve the original content.

My second attempt was to make a function that would run through the whole column.

  function setDueDate(contribR, dueR, progressR) {
    today = new Date();
    due = new Date();
    due.setDate(today.getDate() + 7); //one week from today
    dates = ["Due Date"];
    //the first value in array has to be "Due Date" because the script is placed in the title cell for that column, the cell above the due date column I want to edit

    for(var i = 0; i < dueR.length; i++) {
        if(progressR[i] == 'Completed') {
            dates.push('done');
        }
        else if(dueR[i] instanceof Date) {
            dates.push(dueR[i]);
        }
        else if(contribR[i] != '') {
            dates.push(dueR[i]);
        }
      else {
        dates.push('');
      }
    }

    return dates;

   }

The issue here ended up being similar, though. When this script is called it first clears the whole due date column, so the original date will be lost.

Any thoughts, tips, suggestions appreciated.

Answer 1

The final code I came up with based on Cooper's suggestion:

function onEdit(e) {
    var sh=e.range.getSheet();
    if(sh.getName()!="MySheet") return;

    //if edited cell is in contributor column
    //and if there is not already a date in the due date column
    //then set the due date one week from today
    if(e.range.columnStart==9 && e.range.rowStart>=11 && e.value && e.value.length>0) {
      if((e.range.offset(0,1).getValue() instanceof Date) == false) {
        var td=new Date();
        var dd=new Date(td.getFullYear(),td.getMonth(),td.getDate()+7);    
        e.range.offset(0,1).setValue(Utilities.formatDate(dd, Session.getScriptTimeZone(), "MM/dd/yyyy"));
      }
    }

    //if edited cell is in the progress state column and state is "Completed"
    //then set due date cell to "Done"
    if(e.range.columnStart==10 && e.range.rowStart>=11 && e.value=="Completed") {
      e.range.offset(0,-1).setValue("Done");
    }


  }

I don't actually want to erase the due date if the contributor is erased. ("Oh, mistyped my name yesterday, let me just delete this and then correct it." The due date shouldn't change.) It shouldn't set a new due date every time the progress state is changed, either. So I just cut those two sections out from Cooper's answer.

Answer 2

try this:

You may wish to change the sheet name to what ever you want

function onEdit(e) {
  var sh=e.range.getSheet();
  if(sh.getName()!="Due Date")return;//limits actions to a sheet named Due Date
  if(e.range.columnStart==1 && e.range.rowStart>1 && e.value && e.value.length>0) {
    var td=new Date();
    var dd=new Date(td.getFullYear(),td.getMonth(),td.getDate()+7);    
    e.range.offset(0,2).setValue(Utilities.formatDate(dd, Session.getScriptTimeZone(), "MM/dd/yyyy"));
  }
  if(e.range.columnStart==1 && e.range.rowStart>1 && !e.value) { 
    e.range.offset(0,2).setValue('');
  }
  if(e.range.columnStart==2 && e.range.rowStart>1 && e.value=="Completed") {
    e.range.offset(0,1).setValue("Done");
  }
  if(e.range.columnStart==2 && e.range.rowStart>1 && e.value!="Completed") {
     var td=new Date();
    var dd=new Date(td.getFullYear(),td.getMonth(),td.getDate()+7);    
    e.range.offset(0,1).setValue(Utilities.formatDate(dd, Session.getScriptTimeZone(), "MM/dd/yyyy"));
  }
}

This is what my spreadsheet looks like:

I also included the dialog for the data validation on the second column.