can_decrypt <- gargle::secret_has_key("GOOGLESHEETS4_KEY") knitr::opts_chunk$set( collapse = TRUE, comment = "#>", error = TRUE, fig.path = "man/figures/README-", out.width = "100%", purl = can_decrypt, eval = can_decrypt ) options(tibble.print_min = 5L, tibble.print_max = 5L)
message("No token available. Code chunks will not be evaluated.")
googlesheets4:::gs4_auth_docs()
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.
You can install the released version of googlesheets4 from CRAN with:
install.packages("googlesheets4")
And the development version from GitHub with:
#install.packages("pak") pak::pak("tidyverse/googlesheets4")
You can see how to read data with googlesheets4 in the data import cheatsheet, which also covers similar functionality in the related packages readr and readxl.
googlesheets4 will, by default, help you interact with Sheets as an authenticated Google user. If you don't plan to write Sheets or to read private Sheets, use gs4_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.
library(googlesheets4)
The main "read" function of the googlesheets4 package goes by two names, because we want it to make sense in two contexts:
read_sheet()
evokes other table-reading functions, like
readr::read_csv()
and readxl::read_excel()
. The sheet
in this case
refers to a Google (spread)Sheet.range_read()
is the right name according to the
naming convention
used throughout the googlesheets4 package.read_sheet()
and range_read()
are synonyms and you can use either one. Here we'll use read_sheet()
.
googlesheets4 is pipe-friendly (and reexports %>%
), but works just fine without the pipe.
Read from
dribble
produced by the googledrive package, which can lookup by file nameThese all achieve the same thing:
# URL read_sheet("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077") # Sheet ID read_sheet("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY") # a googledrive "dribble" googledrive::drive_get("gapminder") %>% read_sheet()
Note: the only reason we can read a sheet named "gapminder" (the last example) is because the account we're logged in as has a Sheet named "gapminder".
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.
gs4_create()
creates a brand new Google Sheet and can optionally send some initial data.
(ss <- gs4_create("fluffy-bunny", sheets = list(flowers = head(iris))))
sheet_write()
(over)writes a whole data frame into a (work)sheet within a (spread)Sheet.
head(mtcars) %>% sheet_write(ss, sheet = "autos") ss
sheet_append()
, range_write()
, range_flood()
, and range_clear()
are more
specialized writing functions. See the article Write Sheets for more about writing to Sheets.
googledrive::drive_trash(ss)
Get started is a more extensive general introduction to googlesheets4.
Browse the articles index to find articles that cover various topics in more depth.
See the function index for an organized, exhaustive listing.
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.
googlesheets4 draws on and complements / emulates other packages in the tidyverse:
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.