googlesheets4
googlesheets4 provides an R interface to Google Sheets via the Sheets API v4. It is a reboot of the existing googlesheets package.
Why 4? Why googlesheets4? Did I miss googlesheets1 through 3? No. The idea is to name the package after the corresponding version of the Sheets API. In hindsight, the original googlesheets should have been googlesheets3.
The best source of information is always the package website: googlesheets4.tidyverse.org
Installation
You can install the released version of googlesheets4 from CRAN with:
# NO, NO YOU CANNOT ... BUT SOON!
# install.packages("googlesheets4")
And the development version from GitHub with:
# install.packages("devtools")
devtools::install_github("tidyverse/googlesheets4")
Load googlesheets4
library(googlesheets4)
Auth
googlesheets4 will, by default, help you interact with Sheets as an authenticated Google user. The package facilitates this process upon first need. For this overview, we’ve logged into Google as a specific user in a hidden chunk. For more about auth, visit the package website: googlesheets4.tidyverse.org.
read_sheet()
read_sheet()
is the main “read” function and should evoke
readr::read_csv()
and readxl::read_excel()
. It’s an alias for
sheets_read()
. Most functions in googlesheets4 actually start with
sheets_
. googlesheets4 is pipe-friendly (and reexports %>%
), but
works just fine without the pipe.
We demonstrate basic functionality using some world-readable example
sheets accessed via sheets_examples()
and sheets_example()
.
Read everything:
sheets_example("chicken-sheet") %>%
read_sheet() # or use sheets_read()
#> Reading from 'chicken-sheet'
#>
#> # A tibble: 5 x 4
#> chicken breed sex motto
#> <chr> <chr> <chr> <chr>
#> 1 Foghorn Leghorn Leghorn roost… That's a joke, ah say, that's a jo…
#> 2 Chicken Little unknown hen The sky is falling!
#> 3 Ginger Rhode Islan… hen Listen. We'll either die free chic…
#> 4 Camilla the Chic… Chantecler hen Bawk, buck, ba-gawk.
#> 5 Ernie The Giant … Brahma roost… Put Captain Solo in the cargo hold.
Read specific cells, from a specific sheet, using an A1-style notation:
sheets_example("deaths") %>%
read_sheet(range = "arts!A5:F15")
#> Reading from 'deaths'
#> Range "'arts'!A5:F15"
#> # A tibble: 10 x 6
#> Name Profession Age `Has kids` `Date of birth`
#> <chr> <chr> <dbl> <lgl> <dttm>
#> 1 Davi… musician 69 TRUE 1947-01-08 00:00:00
#> 2 Carr… actor 60 TRUE 1956-10-21 00:00:00
#> 3 Chuc… musician 90 TRUE 1926-10-18 00:00:00
#> 4 Bill… actor 61 TRUE 1955-05-17 00:00:00
#> 5 Prin… musician 57 TRUE 1958-06-07 00:00:00
#> 6 Alan… actor 69 FALSE 1946-02-21 00:00:00
#> 7 Flor… actor 82 TRUE 1934-02-14 00:00:00
#> 8 Harp… author 89 FALSE 1926-04-28 00:00:00
#> 9 Zsa … actor 99 TRUE 1917-02-06 00:00:00
#> 10 Geor… musician 53 FALSE 1963-06-25 00:00:00
#> # … with 1 more variable: `Date of death` <dttm>
Read from a named range or region and specify (some of the ) column types:
sheets_example("deaths") %>%
read_sheet(range = "arts_data", col_types = "??i?DD")
#> Reading from 'deaths'
#> Range "arts_data"
#> # A tibble: 10 x 6
#> Name Profession Age `Has kids` `Date of birth` `Date of death`
#> <chr> <chr> <int> <lgl> <date> <date>
#> 1 David Bowie musician 69 TRUE 1947-01-08 2016-01-10
#> 2 Carrie Fish… actor 60 TRUE 1956-10-21 2016-12-27
#> 3 Chuck Berry musician 90 TRUE 1926-10-18 2017-03-18
#> 4 Bill Paxton actor 61 TRUE 1955-05-17 2017-02-25
#> 5 Prince musician 57 TRUE 1958-06-07 2016-04-21
#> 6 Alan Rickman actor 69 FALSE 1946-02-21 2016-01-14
#> 7 Florence He… actor 82 TRUE 1934-02-14 2016-11-24
#> 8 Harper Lee author 89 FALSE 1926-04-28 2016-02-19
#> 9 Zsa Zsa Gáb… actor 99 TRUE 1917-02-06 2016-12-18
#> 10 George Mich… musician 53 FALSE 1963-06-25 2016-12-25
There are various ways to specify the target Sheet. The simplest, but ugliest, is to provide the URL.
# url of the 'chicken-sheet' example
url <- "https://docs.google.com/spreadsheets/d/1ct9t1Efv8pAGN9YO5gC2QfRq2wT4XjNoTMXpVeUghJU"
read_sheet(url)
#> Reading from 'chicken-sheet'
#>
#> # A tibble: 5 x 4
#> chicken breed sex motto
#> <chr> <chr> <chr> <chr>
#> 1 Foghorn Leghorn Leghorn roost… That's a joke, ah say, that's a jo…
#> 2 Chicken Little unknown hen The sky is falling!
#> 3 Ginger Rhode Islan… hen Listen. We'll either die free chic…
#> 4 Camilla the Chic… Chantecler hen Bawk, buck, ba-gawk.
#> 5 Ernie The Giant … Brahma roost… Put Captain Solo in the cargo hold.
For more information, see the package website: googlesheets4.tidyverse.org.
What’s yet to come?
Writing into Sheets. The googledrive package
(googledrive.tidyverse.org) can
already be used to write into Sheets at the “whole file” level, because
that is carried out via the Drive API. googledrive::drive_upload()
and
googledrive::drive_update()
are very useful for this.
But, if you need more granular control, such as writing to specific worksheets or cells, that requires the Sheets API. This is not yet implemented in googlesheets4, but will be.
Contributing
If you’d like to contribute to the development of googlesheets4, please read these guidelines.
Please note that the ‘googlesheets4’ project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.
Privacy
Context
googlesheets4 draws on and complements / emulates other packages in the tidyverse:
- googlesheets is the package that googlesheets4 is replacing. Main improvements in googlesheets4: (1) wraps the current, most modern Sheets API; (2) leans on googledrive for all “whole file” operations; and (3) uses shared infrastructure for auth and more, from the gargle package. Main deficiency: googlesheets4 doesn’t write yet.
- googledrive already provides a fully-featured interface to the Google Drive API. Any “whole file” operations can already be accomplished today with googledrive: upload or download or update a spreadsheet, copy, rename, move, change permission, delete, etc. googledrive already supports Team Drives.
- readxl is the tidyverse package for reading Excel files (xls or xlsx) into an R data frame. googlesheets4 takes cues from parts of the readxl interface, especially around specifying which cells to read.
- readr is the tidyverse package for reading delimited files (e.g., csv or tsv) into an R data frame. googlesheets4 takes cues from readr with respect to column type specification.