timleavitt/googlesheets4

Google Spreadsheets R API (reboot of the googlesheets package)

googlesheets4

Overview

googlesheets4 provides an R interface to Google Sheets via the Sheets API v4. It is a reboot of an earlier package called googlesheets.

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.

Installation

You can install the released version of googlesheets4 from CRAN with:

install.packages("googlesheets4")

And the development version from GitHub with:

# install.packages("devtools")
devtools::install_github("tidyverse/googlesheets4")

Auth

googlesheets4 will, by default, help you interact with Sheets as an authenticated Google user. If you don’t need to access private Sheets, use sheets_deauth() to indicate there is no need for a token. See the article googlesheets4 auth for more.

For this overview, we’ve logged into Google as a specific user in a hidden chunk.

Attach googlesheets4

library(googlesheets4)

Read

read_sheet() is the main “read” function and should evoke readr::read_csv() and readxl::read_excel(). It’s an alias for sheets_read(), because most functions in googlesheets4 actually start with sheets_. googlesheets4 is pipe-friendly (and reexports %>%), but works just fine without the pipe.

Read from a URL, a Sheet ID, or a dribble produced by the googledrive package. These all achieve the same thing:

read_sheet("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077")
#> Reading from "gapminder"
#> Range "Africa"
#> # A tibble: 624 x 6
#>   country continent  year lifeExp      pop gdpPercap
#>   <chr>   <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#> 1 Algeria Africa     1952    43.1  9279525     2449.
#> 2 Algeria Africa     1957    45.7 10270856     3014.
#> 3 Algeria Africa     1962    48.3 11000948     2551.
#> 4 Algeria Africa     1967    51.4 12760499     3247.
#> 5 Algeria Africa     1972    54.5 14760787     4183.
#> # … with 619 more rows

read_sheet("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY")
#> Reading from "gapminder"
#> Range "Africa"
#> # A tibble: 624 x 6
#>   country continent  year lifeExp      pop gdpPercap
#>   <chr>   <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#> 1 Algeria Africa     1952    43.1  9279525     2449.
#> 2 Algeria Africa     1957    45.7 10270856     3014.
#> 3 Algeria Africa     1962    48.3 11000948     2551.
#> 4 Algeria Africa     1967    51.4 12760499     3247.
#> 5 Algeria Africa     1972    54.5 14760787     4183.
#> # … with 619 more rows

googledrive::drive_get("gapminder") %>% 
  sheets_read()
#> Reading from "gapminder"
#> Range "Africa"
#> # A tibble: 624 x 6
#>   country continent  year lifeExp      pop gdpPercap
#>   <chr>   <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#> 1 Algeria Africa     1952    43.1  9279525     2449.
#> 2 Algeria Africa     1957    45.7 10270856     3014.
#> 3 Algeria Africa     1962    48.3 11000948     2551.
#> 4 Algeria Africa     1967    51.4 12760499     3247.
#> 5 Algeria Africa     1972    54.5 14760787     4183.
#> # … with 619 more rows

See the article Find and Identify Sheets for more about specifying the Sheet you want to address. See the article Read Sheets for more about reading from specific sheets or ranges, setting column type, and getting low-level cell data.

Write

sheets_create() creates a brand new (spread)Sheet and can optionally send some initial data.

(ss <- sheets_create("fluffy-bunny", sheets = list(flowers = head(iris))))
#>   Spreadsheet name: fluffy-bunny
#>                 ID: 1Gl1dCldb7VWKE0B-p5RrhZfPRvZklkSNnln4nWpr5CA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>      flowers: 7 x 5

sheets_write() (over)writes a whole data frame into a (work)sheet within a (spread)Sheet.

head(mtcars) %>% 
  sheets_write(ss, sheet = "autos")
#> Writing to "fluffy-bunny"
#> Writing to sheet "autos"
ss
#>   Spreadsheet name: fluffy-bunny
#>                 ID: 1Gl1dCldb7VWKE0B-p5RrhZfPRvZklkSNnln4nWpr5CA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 2
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>      flowers: 7 x 5
#>        autos: 7 x 11

sheets_edit() and sheets_append() are additional writing functions that are useful in specific situations. See the article Write Sheets for more about writing to Sheets.

Where to learn more

• Get started
• googlesheets4 auth
• Find and Identify Sheets
• Read Sheets
• Write Sheets
• Using googlesheets4 with googledrive
• Fun with googledrive and readxl
• How to create a googlesheets4 reprex
• All the functions!

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

Privacy policy

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. The v3 API wrapped by googlesheets goes offline in March 2020, at which point the package must be retired.
• googledrive provides a fully-featured interface to the Google Drive API. Any “whole file” operations can be accomplished with googledrive: upload or download or update a spreadsheet, copy, rename, move, change permission, delete, etc. googledrive 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.