Stack Overflow for Teams is a private, secure spot for you and your coworkers to find and share information.Learn more

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

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

a-change, Sorry for getting back so late but this worked out quite well. I only made a few changes to the time format to only include days, hours, and minutes. Chris Davis Dec 12 \'17 at 14:47function 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

site design / logo 2020 Stack Exchange Inc; user contributions licensed under cc by-sa. rev2020.5.18.36843 Stack Overflow works best with JavaScript enabled

>>> 更多资讯详情请访问蚂蚁淘商城