I’m working with a Google Sheets document that has three columns: timestamp, time difference, and notes. I created a script that triggers when someone types in the notes column. It should automatically fill in the current timestamp and calculate how much time passed since the previous entry.
Here’s my current code:
function onEdit(event) {
if(event.source.getActiveSheet().getName() == "MySheet" ) {
var column = event.source.getActiveCell().getColumn();
if(column == 3 ) {
var timestampCell = event.range.offset(0,-2);
var durationCell = event.range.offset(0,-1);
var previousTimeCell = event.range.offset(-1,-2);
var currentTime = new Date();
var startTime = previousTimeCell.getValue().getTime();
var endTime = currentTime.getTime();
var difference = String(endTime - startTime);
timestampCell.setValue(currentTime);
durationCell.setValue(difference);
}
}
}
The problem is that the duration column shows a long number instead of a proper time format like hours:minutes:seconds. When I use a formula like =B3-B2 directly in the spreadsheet, it displays the time difference correctly. How can I make my script produce the same formatted result?
yeah, ur math’s right but you’re forguetting how sheets handles time formatting. try this: var difference = new Date(endTime - startTime); then set the cell value directly without converting to string. keep it as a date object so sheets can format it properly instead of just dumping raw milliseconds.
This issue arises because you’re converting the time difference to a string and working with milliseconds. When you compute endTime - startTime, the result is in milliseconds, while Google Sheets expects time durations as decimal fractions of a day. To resolve this, you should divide your millisecond difference by 86400000 (the number of milliseconds in a day) before you set the cell value. Modify your difference calculation to: var difference = (endTime - startTime) / 86400000; and then use durationCell.setValue(difference);. Make sure to format the duration column to show time correctly, either through the sheet’s formatting options or by adding: durationCell.getRange().setNumberFormat(‘[h]:mm:ss’); This adjustment will ensure your script’s output matches the format that the built-in spreadsheet formula yields when subtracting two timestamps.
The problem’s with getTime() returning milliseconds, but Google Sheets wants fractional days for time differences. I hit this exact issue last year building a similar tracker. Don’t convert to string or mess with milliseconds directly - stick with Date objects. Replace your difference calculation with: var difference = (currentTime - previousTimeCell.getValue()) / (1000 * 60 * 60 * 24); This converts milliseconds to days, which is what Sheets expects. Set the cell value as a number, not a string, then apply duration formatting. Here’s the key thing: when you manually subtract cells in Sheets, it handles the date math and formatting automatically. In Apps Script, you’ve got to manually convert the time units to match what Sheets expects internally.