Fun with googledrive and readxl • googlesheets4

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.

No token available. Code chunks will not be evaluated.

Attach packages

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

Auth

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)