This article provides advice on reading and writing datetimes with Google Sheets, specifically around the matter of time zones.
A related issue is how datetimes are formatted for presentation in the Sheet itself. You can read more about these formats in the Date and time format patterns section of the Sheets API docs. At the time of writing, googlesheets4 provides no user-friendly way to address these formats, although it may do so in the future.
Since we eventually create and edit Sheets, we also auth here in a hidden chunk. If you run this code, you should expect auth to happen.
The lubridate package (lubridate.tidyverse.org) is useful for this exploration, so we attach it now.
library(lubridate, warn.conflicts = FALSE)
Literally, you can’t.
I know this sounds very harsh, but it is the truth. Google Sheets offer essentially no support for time zones and your life will be simpler if you just make peace with this and accept that you will be looking at UTC times in Sheets.
A short demo: in R, capture the current time as
tt and reveal the current time zone. Note that
tt is displayed in R according to this time zone.
tt datetime to a Sheet, configured with the same time zone as the local R session, and create another cell that captures the exact text presented in the browser UI for
tt. Read this back into R.
dat <- tibble::tibble( datetime = tt, as_displayed = gs4_formula("=TO_TEXT(A2)") ) (ss <- gs4_create( "no-time-zone-effect", sheets = dat, timeZone = Sys.timezone() )) #> Creating new Sheet: "no-time-zone-effect" #> Spreadsheet name: no-time-zone-effect #> ID: 1rH4nOOFBFvqCLEcYqaPjMIPj7EHfcT21tLTRL2FbHx0 #> Locale: en_US #> Time zone: America/Vancouver #> # of sheets: 1 #> #> (Sheet name): (Nominal extent in rows x columns) #> dat: 2 x 2 read_sheet(ss) #> Reading from "no-time-zone-effect" #> Range "dat" #> # A tibble: 1 x 2 #> datetime as_displayed #> <dttm> <chr> #> 1 2020-04-23 19:48:57 2020-04-23 19:48:58
Note that the
tt datetime is displayed differently in Sheets than it is locally in R. Sheets presents datetimes in Coordinated Universal Time (time zone Etc/UTC), even if the Sheet’s metadata specifies a different time zone, such as America/Vancouver.
with_tz(tt, "Etc/UTC") #>  "2020-04-23 19:48:57 UTC"
If you want to understand more about datetimes in R, in Sheets, and how you can sort of hack around this time zone problem, keep reading.
Datetimes are a complicated topic. Here we dramatically oversimplify things, in the name of making a reader who is new to all of this at least minimally functional.
The main system used to represent times in the computing world is Unix epoch time:
A moment in time is represented by the number of seconds that have elapsed since 1 January 1970 00:00:00 UTC.
The “UTC” part stands for “Coordinated Universal Time”. Yes, the order of the letters is strangely different from the words! It’s a great metaphor for this entire subject, because nothing is as simple as you’d like. Just accept it and move on. UTC is what you may already think of as “Greenwich Mean Time”, if you’ve ever encountered that term.
There are three wrinkles we must acknowledge, even when oversimplifying:
You can read more in the Sheets API docs about Date/Time serial numbers.
R uses Unix epoch time.
R uses the POSIXct class to represent datetimes. (Yes, there’s also
POSIXlt, but I recommend and will focus on
If you ask for the current time, R prints it formatted for your time zone (or, at least, it tries). You can also ask R to reveal what it thinks your time zone is.
The time zone is purely matter of display, but it’s a really nice touch! It is comforting to get a time printed by R that matches your experience of what time it is, based on looking at the clock on your wall (“clock time”).
with_tz() function lets you explicitly associate a datetime with a time zone, e.g. your own or any other time zone recognized by your system. And this, in turn, affects how the time is formatted for human eyeballs.
Google Sheets have no ability to display a moment in time according to a specified time zone. Yes, each Sheet has an associated time zone, but this is not what it influences, even though you might expect or hope for that.
When we read datetimes out of a Google Sheet, we must:
When we write a datetime to a Google Sheet, we must:
Google Sheets use a spreadsheet-y version of epoch time. A datetime cell holds a so-called serial number, which is the elapsed days since the epoch of 30 December 1899 00:00:00 UTC. This number is then displayed in a more human-friendly way, according to a special token string. Currently googlesheets4 doesn’t offer any explicit support for dealing with these format strings, although one day it probably will.
Let’s gain some intuition by looking at datetimes shortly after the epoch and inspecting the underlying serial numbers. In a hidden chunk, we create a Sheet and read it into R.
#> Creating new Sheet: "near-the-epoch" #> Reading from "near-the-epoch" #> Range "dat" #> # A tibble: 2 x 2 #> datetime serial_number #> <dttm> <dbl> #> 1 1899-12-30 12:00:00 0.5 #> 2 1899-12-31 18:00:00 1.75
1899-12-30 12:00:00 is noon on the day that is the Google Sheets epoch. Its underlying serial number is 0.5, because one half-day has elapsed since the epoch. 1899-12-31 18:00:00 is 6pm in the evening on the day after the epoch. Its underlying serial number is 1.75, because it’s one plus three-quarters of a day since the epoch.
Every Google Sheet has an associated time zone. It is included in the metadata returned by
gs4_get(), like the locale, and is revealed by default when we print a Sheets ID.
(meta <- gs4_example("gapminder") %>% gs4_get()) #> Spreadsheet name: gapminder #> ID: 1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY #> Locale: en_US #> Time zone: America/Los_Angeles #> # of sheets: 5 #> #> (Sheet name): (Nominal extent in rows x columns) #> Africa: 625 x 6 #> Americas: 301 x 6 #> Asia: 397 x 6 #> Europe: 361 x 6 #> Oceania: 25 x 6 #> #> (Named range): (A1 range) #> canada: 'Americas'!A38:F49 meta$time_zone #>  "America/Los_Angeles"
However, this time zone has a very different impact – much less impact – on the user experience than the time zone in R.
The Sheets’ time zone does not influence the display of datetimes. There is no way to request that a datetime be displayed according to a specific time zone – not via the Sheet’s time zone, not via the format string, and not via a Sheets function.
Datetimes in Google Sheets are fundamentally UTC-based and always display as such.
If you want to see “9:14 am” in your Sheet, you must make sure the serial number in that cell represents 9:14 in the morning, UTC time.
As far as I can tell, here is the only effect of a Sheet’s time zone: The formulas
=TODAY() take the local clock time or date, according to the the Sheet’s time zone, and construct the UTC moment or date that will display as that time or date. Therefore
=NOW(), especially, is almost misleading! It does not capture the current moment, in UTC, but instead fabricates a UTC moment that matches current local clock time.
This suggests various hacks if you truly, deeply want to see specific clock times in your Sheet, for non-UTC time zones.
Starting with the UTC moments, you must determine and apply the offset yourself. At a very crude level, this can be done from first principles with datetime arithmetic in the Sheet (“Vancouver is −08:00, so subtract 8 hours”). But then there’s daylight savings time and other complexities (“Except, during DST, subtract 7 hours.”). In reality, no mere mortal will ever get this right, in general. If you doubt me, please watch the YouTube video Computerphile’s “The Problem with Time & Timezones”.
You need to use external, authoritative offset information, either within R or in the Sheet. Below, we show how to do this in R. In Sheets, people tend to use Google Apps script and solutions based on moment.js.
Let’s make all of this concrete. We construct a data frame in R with a datetime and various versions of it that explore time zone issues. We also include a couple of Google Sheet formulas, to trigger some datetime work once the data is written into a Sheet. We sketch the construction of this data frame here, with considerable abuse of notation (mixing R code and Sheets formulas):
|=(DATE(moment_utc) + TIME(moment_utc))||
Capture the current
moment in time, with
Sys.time(), which has no explicit time zone. Store versions of
moment with explicit time zones: America/New_York and Etc/UTC. Use
lubridate::force_tz() to create a new moment in time: the moment in UTC that has the same clock time as the original
moment in New York.
The first Sheets formula we use is
=NOW(), which you might expect to be the equivalent of R’s
Sys.time(). But it’s more like
force_tz(Sys.time(), tzone = "Etc/UTC"). The second formula we construct is more elaborate. It uses datetime functions in Sheets to explicitly construct
moment_utc in the Sheet. The last column uses
=TO_PURE_NUMBER() to reveal the underlying serial numbers for all of the datetimes.
Create 3 Sheets, with different approaches to the time zone:
ss_xx <- gs4_create("tz-default") #> Creating new Sheet: "tz-default" ss_ny <- gs4_create("tz-america-new-york", timeZone = "America/New_York") #> Creating new Sheet: "tz-america-new-york" ss_utc <- gs4_create("tz-etc-utc", timeZone = "Etc/UTC") #> Creating new Sheet: "tz-etc-utc" show_timezone <- function(ss) gs4_get(ss)$time_zone show_timezone(ss_xx) #>  "Etc/GMT" show_timezone(ss_ny) #>  "America/New_York" show_timezone(ss_utc) #>  "Etc/UTC"
Capture the current
Sys.time(), construct the data frame described above, and write it into each of the prepared Google Sheets.
dat <- populate_sheets(Sys.time(), c(ss_xx, ss_ny, ss_utc)) #> Writing to "tz-default" #> Writing to sheet "Sheet1" #> Editing "tz-default" #> Resizing one or more columns in "Sheet1" #> Editing "tz-default" #> Editing sheet "Sheet1" #> Writing to "tz-america-new-york" #> Writing to sheet "Sheet1" #> Editing "tz-america-new-york" #> Resizing one or more columns in "Sheet1" #> Editing "tz-america-new-york" #> Editing sheet "Sheet1" #> Writing to "tz-etc-utc" #> Writing to sheet "Sheet1" #> Editing "tz-etc-utc" #> Resizing one or more columns in "Sheet1" #> Editing "tz-etc-utc" #> Editing sheet "Sheet1"
First, let’s look at
dat, the data frame we sent.
dat #> # A tibble: 6 x 3 #> what datetime serial_number #> <chr> <list> <fmla> #> 1 moment <dttm > =ARRAYFORMULA(TO_PURE_NUMBER(B2:B)) #> 2 moment_ny <dttm > NA #> 3 moment_utc <dttm > NA #> 4 moment_ny_utc_force <dttm > NA #> 5 =NOW() <fmla> NA #> 6 =DATE(moment_utc) <fmla> NA
That’s hard to parse since the
datetime column is a list-column. Here’s a different look, with the most natural character representation of that column.
#> # A tibble: 6 x 3 #> what datetime serial_number #> <chr> <chr> <fmla> #> 1 moment 2020-04-23 12:49:20 PDT =ARRAYFORMULA(TO_PURE_NUMBER(B2:B)) #> 2 moment_ny 2020-04-23 15:49:20 EDT NA #> 3 moment_utc 2020-04-23 19:49:20 UTC NA #> 4 moment_ny_utc_f… 2020-04-23 15:49:20 UTC NA #> 5 =NOW() =NOW() NA #> 6 =DATE(moment_ut… =(DATE(2020,4,23)+time(19,4… NA
Read the Sheets back into R, the Sheet with no explicit time zone set.
read_sheet(ss_xx) %>% as.data.frame() #> Reading from "tz-default" #> Range "Sheet1" #> what datetime serial_number #> 1 moment 2020-04-23 19:49:20 43944.83 #> 2 moment_ny 2020-04-23 19:49:20 43944.83 #> 3 moment_utc 2020-04-23 19:49:20 43944.83 #> 4 moment_ny_utc_force 2020-04-23 15:49:20 43944.66 #> 5 =NOW() 2020-04-23 19:49:22 43944.83 #> 6 =DATE(moment_utc) 2020-04-23 19:49:20 43944.83 read_sheet(ss_ny) %>% as.data.frame() #> Reading from "tz-america-new-york" #> Range "Sheet1" #> what datetime serial_number #> 1 moment 2020-04-23 19:49:20 43944.83 #> 2 moment_ny 2020-04-23 19:49:20 43944.83 #> 3 moment_utc 2020-04-23 19:49:20 43944.83 #> 4 moment_ny_utc_force 2020-04-23 15:49:20 43944.66 #> 5 =NOW() 2020-04-23 15:49:23 43944.66 #> 6 =DATE(moment_utc) 2020-04-23 19:49:20 43944.83 read_sheet(ss_utc) %>% as.data.frame() #> Reading from "tz-etc-utc" #> Range "Sheet1" #> what datetime serial_number #> 1 moment 2020-04-23 19:49:20 43944.83 #> 2 moment_ny 2020-04-23 19:49:20 43944.83 #> 3 moment_utc 2020-04-23 19:49:20 43944.83 #> 4 moment_ny_utc_force 2020-04-23 15:49:20 43944.66 #> 5 =NOW() 2020-04-23 19:49:25 43944.83 #> 6 =DATE(moment_utc) 2020-04-23 19:49:20 43944.83
momentresult in the same serial number in all 3 Sheets. Lesson: in R, time zone is merely a matter of display and, in Sheets, there is only UTC.
moments NY clock time into UTC) results in the same serial number in all 3 Sheets. Lesson: If you want to see a specify clock time in the Sheet, force this on the R side, before writing to Sheets. But realize that you have fudged the datetime data in order to get the desired display.
=NOW()is one of the few things affected by a Sheet’s time zone (along with
=TODAY(). It allows you to force the Sheet’s clock time into UTC.