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.
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:
You need to set your cell or range format in Google Sheets as Format->Number->DateTime
There is a more customizeable datetime format further down in the format list...
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:
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:
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.)
- What finally worked well:
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.
- 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.