Random TMZ on a dateTime [Spreadsheet]

2
votes

I have a starting time in "HH:MM:SS" format. In my spreadsheet, the user enters it in this format, like "09:00:00" for 9AM. When I get this cell's value in my script, I get this weirdo :

"Sat Dec 30 09:00:00 GMT+00:09 1899"

It seems like I have that random date of Sat Dec 30 1899, which I don't really care about, but also the "GMT+00:09", that bothers me a lot more. My spreadsheet and script are "GMT+01:00 Paris", so when I insert this data elsewhere, I get 09:50 GMT+01:00... Not even 51 minutes off, as I would have expected.

Do you have any idea how and why it happens ?

The fix I use right now is to display the time in my "HH:MM:SS" format, or "HH:MM", but set the value to, say "05/01/2018 09:00:00", so it is considered a full dateTime in my timezone. I would really like to be able to only specify the time here.

Thanks for your answers,

1
datetimegoogle-apps-scriptgoogle-sheets
One fast fix-solution is to set the number formatting in the cells to plain text. For some reason one of my spreadsheets always added 20min to the time when the format was 'time' or 'date'. Could not even reproduce the issue on other spreadsheets. And I cannot even make up a timezone that is 20min off to mine. – FatFingersJackson
That seems to be the same kind of problem, a 9-minutes offset is nowhere to be found about of course ! Plain text is just another quick fix, I found the most fitting fix for now, but it's not really what I am looking for here... Thanks for your input anyways =) – Josselin PEREZ

1 Answers

0
votes

How about this answer? This answer supposes the following condition.

  • Format of cell "A1" is HH:MM:SS for time.
  • Value of cell "A1" is 09:00:00.

In your 1st case :

When the value of "A1" is retrieved by getValue(), the value is Sat Dec 30 09:00:00 GMT+09:00 1899. (I'm sorry. "GMT+09:00" is due to the time zone. My time zone is Asia/Tokyo.) Because Spreadsheet uses Serial Number for Date/Time. The start of serial number is 12/30/1899 00:00:00. In this case, because there are no date information, Sat Dec 30 09:00:00 GMT+09:00 1899 is retrieved as the value which elapsed for 09:00:00 from the start.

If you want to retrieve 09:00:00 from cell "A1", please use getDisplayValue() instead of getValue(). By this, you can retrieve 09:00:00.

In your 2nd case :

I think that this is better usage. 05/01/2018 09:00:00 is imported to the cell with the format of HH:MM:SS. By this, the value has the information of both date and time. So the values of cell retrieved by getValue() and getDisplayValue() are Fri Jan 05 09:00:00 GMT+09:00 2018 and 09:00:00, respectively.

Sample :

In the case using getValue()

SpreadsheetApp.getActiveSheet().getRange("A1").getValue();

In the case using getDisplayValue()

SpreadsheetApp.getActiveSheet().getRange("A1").getDisplayValue();

References :

If I misunderstand your question, I'm sorry.