Experiments with Logging DateTimes to Google Sheets

Tutorials and Guides
1

Here are some experimental results and recommendations for tagging your Google Sheets rows with date time stamps.

  • =NOW() did not work...

The Google Sheets function '=now()' changes its value when you load a sheet for edit, just by refreshing (F5) your display. So don't bother trying to save it as a timestamp into a sheet row.

Capture

  • Don't bother trying to save an Instant directly to a Sheet cell. There is no conversion. You will get an error message at run time.

  • Google Sheets datetime values are stored as numerical values, specially formatted:

(From a Sheets Help page I lost:)

Instructs date, time, datetime, and duration fields to be output as doubles in "serial number" format, as popularized by Lotus 1-2-3. The whole number portion of the value (left of the decimal) counts the days since December 30th 1899. The fractional portion (right of the decimal) counts the time as a fraction of the day. For example, January 1st 1900 at noon would be 2.5, 2 because it's 2 days after December 30th 1899, and .5 because noon is half a day. February 1st 1900 at 3pm would be 33.625. This correctly treats the year 1900 as not a leap year.

Here is a link to a discussion of some of the history and effects of this coding choice:
https://rongpeng.li/google-sheets/an-anecdote-about-google-sheet-data-types-microsoft-lotus-1-2-3-and-december-30th-1899/

Also see

https://www.iwpcug.org/docs/18991230.php

  • You need to set your cell or range format in Google Sheets as Format->Number->DateTime

    Untitled
    Untitled1360×768 131 KB

    There is a more customizeable datetime format further down in the format list...
    Capture
    Capture513×580 24.7 KB

    This is similar to the AI2 date format block, except you get pulldowns, and between the pulldowns are tiny invisible text boxes where you can insert punctuation between the datetime parts.

  • You need to have your Sheet's Time Zone set properly in the Sheet File->Settings menu:

    Untitled
    Untitled1360×768 116 KB

Capture
Capture725×462 27.4 KB

Unfortunately, Google does not follow Daylight Savings Time well. I was an hour off when I tried directly calculating and uploading a datetime value as such a number:

blocks
blocks1468×618 101 KB

blocks (1)
blocks (1)1242×264 30.7 KB

This is not a deficiency in my SERIAL_NUMBER conversion, just a difference of opinion between my Google profile, my current location (NYC vicinity), and my temporal location (early November, right before the USA time zone shift.)
(Coincidentally, one of my digital clocks needed a one hour tweak just yesterday. It had builtin to it an outmoded idea of when Daylight Savings shifts should occur.)

This takes into account the local setting of time zone in the originating Android environment, and gets converted by Google Sheets into its internal datetime format well.

D1
D1695×87 4.43 KB

E1
E1799×98 4.84 KB

F1
F1938×98 5.36 KB

  • Recommendations:
    • Set your Sheet Settings to your Time Zone
    • Set your column format to your favorite NUMERIC date time format, to make available all Google Sheets Date functions
    • Use the AI2 Clock FormatDateTime block to make your local time readable by Google Sheets on upload.

blocks (2)
blocks (2)1304×316 34.9 KB

3 Likes
7

How about ?

image
image699×474 40.5 KB

image

ref

You could also send a formula to the sheet:

image

1 Like
8

Coercing clock instances into values compatible with Google Sheets should probably be natively supported by the component. @Susan_Lane can you take a look at this?

9

You're right. The component should do this. I'll put in an issue and see what I can do.

10

Watch out for time zone considerations.
They can be tricky, given the centralized nature of the sheet versus the localized nature of the mobile device.

1 Like
11

This is a good point. Full-featured datetime compatibility could get to be more work that we can commit to.

I'm only thinking about coercing the format. I don't think we can commit to tracking time zones. If it's relevant, the user will have to do it.

I guess spreadsheets don't generally know what time zone they're in, so that's not unreasonable.

12

I suppose it all depends if you want to align the time on the smartphone with the time on the google sheet.

Usually, one would post the smartphone time.

Just don't use google sheet date/ time formulas.

1 Like
13

Developers might upload all time in GMT, UTC or whatever to the SS and use their app to convert UTC to local time on a user's device.

1 Like
14

Regarding @SteveJG's point, most spreadsheet software uses the Microsoft format, which is a fractional form where the integer part is days since Dec 30, 1899 (ref Why 30th December 1899?) and the fractional part is multiplied by 86400 to get a particular second in a day (excepting leap seconds...). Therefore, I expect the "right" format will likely be this one support for Unix timestamps would be appreciated (but I'm not going to hold my breath).

15

Also, regarding timezones:

1 Like
16

As a stopgap, I suggest just formatting the datetime according to
image
which would be accepted by Google Sheets and leaving it to the app developer to follow the other recommendations at the bottom of Experiments with Logging DateTimes to Google Sheets

An AI2 app developer spanning time zones with their app would need to deal with their time zone issues on their own, while a new coder would still have a safety net under them if they send an Instant to Sheets.

17

I do think ISO 8601 tends to be a pretty good option (especially if you go the extra step to include the source timezone). In most cases, apps will likely not leave the original time zone as most of our users are students, but either UTC or ISO 8601 + timezone should cover most use cases, and for more advanced folks hopefully they're willing to take the dive into the time zone quagmire.

18

Whatever datetime format you use, it would be wise to see if Sheets can parse it using the =VALUE() function.

Here is the failure I got when i tried to send a 'z' format suffix:
Capture

(The VALUE() works when I don't send the EST.)

19

So according to the Google Sheets documentation, VALUE requires that the input be a datetime format that Google Sheets understands (a bit of a tautology, but anyway...).

According to this document, it looks like there isn't a way to assert time zones with the Google functionality, a bit surprising if true.

If this were a properly formatted ISO 8601 date-time, you'd want to be able to parse something like 2022-11-08T04:20:38-05:00

20

I tried the ISO 8601 X X X date format, from SimpleDateFormat (Java Platform SE 7 )
but it is not recognized by Sheets:

blocks
blocks1370×316 36.2 KB

Capture

So far, there is no way to encode the time zone into the datetime that Sheets will recognize.

21

I don't know how particular sheets is on this front, but there shouldn't be a space between the time and the timezone.

22

Eh. I've tried a bunch of different things, but it looks like Sheets just will not accept a time zone per the ISO 8601 guidelines...

Relevant XKCD:

23

Removing the space did not improve Sheet's recognition capabilities.

blocks (1)
blocks (1)1416×316 37.9 KB

Capture

In baseball, the solution would be to punt bunt:

blocks (2)
blocks (2)1304×316 34.9 KB

Capture

24

Unix Convert Unix Timestamp

What one uses for time depends on what one is gonna' do with it. Unix is fuzzy, UTC is simple.

25

The challenge here is that with something like Sheets as a backend, there is the potential that it is shown to someone outside of the context of the app. Neither Unix timestamps or UTC timestamps are particularly useful in that regard since you want to show the information as it is relevant to the viewer.