CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
John Cutter
John Cutter

Reputation: 91

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

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:

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

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.

Upvotes: 1

Views: 521

Answers (1)

Jescanellas
Jescanellas

Reputation: 2608

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".

Upvotes: 1

Related Questions