Are dates/times handled differently in the Google Apps Script V8 Runtime?

Question

I recently switched to the V8 Runtime in Google Apps Script and I'm having a strange problem. (Which I can't seem to reproduce in a minimal format.)

My Google Sheet allows users to enter (in cells) a 'work_start' time and a 'work_end' time. For now I have chosen "9:30" and "18:00". With the old Runtime this becomes:

Sat Dec 30 1899 09:30:00 GMT+0009 (Central European Standard Time)
Sat Dec 30 1899 18:00:00 GMT+0009 (Central European Standard Time)

When I switch to the V8 Runtime, however, I get this:

Sat Dec 30 1899 08:39:21 GMT+0009 (Central European Standard Time)
Sat Dec 30 1899 17:09:21 GMT+0009 (Central European Standard Time)

If I run it again immediately, with the old runtime, it goes back to 9:30 and 18:00.

I have checked my code multiple times and I set these values once (as globals) but I never change them. I only use/read them. I even set a breakpoint on the first line of my main function.

I set up a new project and tried to recreate the problem, but for some reason the problem doesn't occur in a new sheet. I have also tried clearing the formatting of those two cells.

Next, I tried one last thing. I moved the code out of the global space and put it in a function, then had a breakpoint on the next line so I could check my variables:

function Main() {
  var work_start = cal.getRange("G1").getValue(); // work start time
  var work_end = cal.getRange("G2").getValue(); // work end time
  var test = 0; // SET A BREAKPOINT HERE
  ...
}

This gives me the same strange results: 8:39:21 and 17:09:21. (Again, only when part of my program. In a new sheet it gives 9:30 and 18:00, as expected.)

Not even sure how to begin to look for an answer to this bug, so any help or guidance will be appreciated.

Darn. I thought you were on to something because I'm in Germany but my locale was set to the United States. Unfortunately, changing the locale didn't help. The times still show as 8:39:21 and 17:09:21. Thanks for the suggestion, though! — John Cutter
The time difference between 08:39:21 and 9:30 and 17:09:21 and 18:00 is both the same, and it is 50 minutes and 39 seconds. And the time is earlier. But what that means I have no idea. I assume that you are further processing the date, because a year of 1899 isn't going to help you. Regardless of why it's doing this, you need a way to calculate the correct date. How do you determine the correct year, month, and day? If you include the correct year, month, day and time in the date creation, then it would avoid returning that date of 1899. — Alan Wells
@AlanWells Perhaps he is actually looking at a duration and that's why the dates are so small. — Cooper
Thank you for the replies. My sheet asks for work_start and work_end. When someone enters a time in a cell (i.e. "9:00") that automatically becomes a date/time in my code. Then I use getHours() and getMinutes() to extract just the time. This lets me compare two times. — John Cutter

Jescanellas Jescanellas · Accepted Answer · 2020-06-01T13:40:32

I could also experience this 50'39'' time difference between the value in the cell and what Apps Script reads. Enabling or disabling V8 didn't make any difference.

This is because Sheets uses the date 1899-12-30 0:00:00 as reference, while Apps Script (and most Google services) uses Unix Time (which starts at 1970-1-1 00:00:00 UTC). Leap seconds are ignored in Unix Time so it could be that.

As a workaround, you can:

Get the values with getDisplayValue instead.
Set the cell format to "plain text".

Are dates/times handled differently in the Google Apps Script V8 Runtime?

1 Answers