In jennybc/googlesheets2: Access Google Sheets using the Sheets API V4

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.")

Attach packages

library(googlesheets4)
library(googledrive)
library(readxl)

Auth

# 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.

Create a private Sheet from csv with the Drive API

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")

Create a local xlsx from a Sheet with the Drive API

Download the Sheet as an Excel workbook.

(iris_xlsxfile <- sub("[.]csv", ".xlsx", iris_tempfile))
drive_download(iris_ss, path = iris_xlsxfile, overwrite = TRUE)

Read xlsx with readxl

Read the iris data back in via readxl::read_excel().

if (requireNamespace("readxl", quietly = TRUE)) {
  readxl::read_excel(iris_xlsxfile)  
}

Clean up

file.remove(iris_tempfile, iris_xlsxfile)
drive_trash(iris_ss)

jennybc/googlesheets2 documentation built on Dec. 10, 2023, 12:56 a.m.