Can anyone help me with an Apps Script in Google sheets that would automatically record start time in column I as soon as there is an input in column B as shown in the image below?

1st image

Then, automatically add an end time in column J once the "done" option is clicked on column H as shown on the image below

2nd image

Do nothing or continuously record the time once "pend" option is chosen as shown on the screenshot below.

3rd image

Lastly, automatically calculate the total handling time as shown on the screenshot on column K.

Appreciate any responses.

CodePudding user response：

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet0" && e.range.columnStart == 2 && e.value) {
    e.range.offset(0, 6).setValue(new Date())
  }
  if (sh.getName() == "Sheet0" && e.range.columnStart == 8 && e.value) {
    e.range.offset(0, 2).setValue(new Date());
    let Start = new Date(sh.getRange(e.range.rowStart,8).getValue());
    let End = new Date(sh.getRange(e.range.rowStart,9).getValue());
    sh.getRange(e.range.rowStart,10).setValue(timeDiff1(Start,End));
  }

}

function timeDiff1(Start, End) {
  if (Start && End) {
    var second = 1000;
    var minute = 60 * second;
    var t1 = new Date(Start).valueOf();
    var t2 = new Date(End).valueOf();
    var d = t2 - t1;
    var minutes = Math.floor(d % day % hour / minute);
    var seconds = Math.floor(d % day % hour % minute / second);
    return 'mm:ss\n'   minutes   ':'   seconds;
  }
  else {
    return 'Invalid Inputs';
  }
}