Suspend time recording and resume using Apps script in Google sheets

Viewed 19

Is there a way to suspend and resume time record in Google sheets Apps Script? I already have a start and stop function, what I'm trying to do is to suspend the time and then resume it anytime.

I have this simple tracker created that automatically records start time and end time to capture handling time and just later realized I'd have to put a function that suspends and resumes the time as well for data quality purposes.

Below is my current code:

function onEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == "Sheet1" && e.range.columnStart == 2 && e.value) {
    e.range.offset(0, 7).setValue(new Date())
  }
  if (sh.getName() == "Sheet1" && 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';
  }
}

I would like to add the below image as an example. tracker in action sample image

As you can see, marking it done will just mark the end time. What I'm trying to do is add another validation in column H such as suspend and resume. Suspend will stop or somehow record the suspended time and then resume will just continue the time tracking.

Can anyone help or have an idea how to do that?

0 Answers
Related