Record Cell Value Time

1
votes

I am trying to find away to record the amount of time a cell has a specific value. example: If H4 = ABC and then change to XYZ; I need to record the amount of time cell H4 was ABC before it was changed to XYZ. I have not been able to locate a timer function in the Google script library. Any help would be appreciated. Here is the link to the sheet and all the scripts currently running. https://docs.google.com/spreadsheets/d/1eqOXVR9_2fe246PejvwUZd-Z1RoDN8OyS9B7Hk8FZRI/edit?usp=sharing

2
google-apps-scriptgoogle-sheets

2 Answers

0
votes

I don't know about a timer function in GAS either and anyway it wouldn't be optimal to have it running all the time. A better way seems to be to calculate time difference on cell change between the timestamp that you have and current date.

I've tweaked this function a bit so that it also includes difference in hours:

function dateDiff(start, end) {
  var diff = { years: 0, months: 0, days: 0 };
  var timeDiff = end - start;

  if (timeDiff > 0) {
    diff.years = end.getFullYear() - start.getFullYear();
    diff.months = end.getMonth() - start.getMonth();
    diff.days = end.getDate() - start.getDate();
    diff.hours = end.getHours() - start.getHours();
    if (diff.months < 0) {
      diff.years--;
      diff.months += 12;
    }

    if (diff.days < 0) {
      diff.months = Math.max(0, diff.months - 1);
      diff.days += 30;
    }
    if (diff.hours < 0) {
      diff.days = Math.max(0, diff.days - 1);
      diff.hours += 24;
    }

  }

  return diff;
};

And your onEdit function that now uses this dateDiff function:

function onEdit() {
  // This section returns if you're not on the SBC/HP spreadsheet.
  var sheet = "SBC/HP";

  var s = SpreadsheetApp.getActiveSheet();
  var r = s.getActiveCell();
  // if r is in column h { do the rest }
  if( r.getColumn() != 11){
    var row = r.getRow();
    var previousDate = new Date(s.getRange(row, 11).getValue()); //getting the timestamp of the previous change. Looks like the year should be YYYY, not YY as it is now
    var time = new Date();
    var timeDiff = dateDiff(previousDate, time); //calculating time difference in a separate function
    time = Utilities.formatDate(time, "GMT-05:00", "MM/dd/yy, hh:mm:ss");
    s.getRange(row, 12).setValue(timeDiff.years + ' years ' + timeDiff.months + ' months ' + timeDiff.days + ' days ' + timeDiff.hours + ' hours'); //pasting time difference in some format in a separate cell
    s.getRange('K' + row.toString()).setValue(time);
  }
}

You can check how it works here

0
votes

Combine Stackdriver Logging with onEdit(). Then use Log Filters and/or Log Exporting to extract the timestamps of the INFO log.

function onEdit (e) {
  var message = e.range.getA1Notation() + " changed to " + e.value;

  console.info({message:message});
}

Get to that projects Log Viewer View > Stackdriver Logging in the script editor. From here you can filter out the console.info() messages and get the time difference from the timestamps containing the same e.range.getA1Notation() in the Stackdriver Logging message.

Stackdriver Docs