This article demonstrates how to use googlesheets4, googledrive, and readxl together. We demonstrate a roundtrip for data that starts and ends in R, but travels in spreadsheet form, via Google Sheets.
can_decrypt <- gargle::secret_has_key("GOOGLESHEETS4_KEY") knitr::opts_chunk$set( collapse = TRUE, comment = "#>", error = TRUE, purl = can_decrypt, eval = can_decrypt )
message("No token available. Code chunks will not be evaluated.")
library(googlesheets4) library(googledrive) library(readxl)
# happens in .onLoad() when IN_PKGDOWN, but need this for local dev/preview googlesheets4:::gs4_auth_docs(drive = TRUE) # attempt to reduce quota exhaustion problems if (identical(Sys.getenv("IN_PKGDOWN"), "true")) Sys.sleep(30)
As a regular, interactive user, you can just let googlesheets4 prompt you for anything it needs re: auth.
Since this article is compiled noninteractively on a server, we activate a service token here, in a hidden chunk. We are also using a shared token for Sheets and Drive. You can read how to do that in your own work in the article Using googlesheets4 with googledrive.
Put the iris data into a csv file.
(iris_tempfile <- tempfile(pattern = "iris-", fileext = ".csv")) write.csv(iris, iris_tempfile, row.names = FALSE)
Use googledrive::drive_upload()
to upload the csv and simultaneously convert to a Sheet.
(iris_ss <- drive_upload(iris_tempfile, type = "spreadsheet")) # visit the new Sheet in the browser, in an interactive session! drive_browse(iris_ss)
Read data from the private Sheet into R.
read_sheet(iris_ss, range = "B1:D6")
Download the Sheet as an Excel workbook.
(iris_xlsxfile <- sub("[.]csv", ".xlsx", iris_tempfile)) drive_download(iris_ss, path = iris_xlsxfile, overwrite = TRUE)
Read the iris data back in via readxl::read_excel()
.
if (requireNamespace("readxl", quietly = TRUE)) { readxl::read_excel(iris_xlsxfile) }
file.remove(iris_tempfile, iris_xlsxfile) drive_trash(iris_ss)
Add the following code to your website.
For more information on customizing the embed code, read Embedding Snippets.