Fun with googledrive and readxl

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.

Attach packages

library(googlesheets4)
library(googledrive)
#> 
#> Attaching package: 'googledrive'
#> The following objects are masked from 'package:googlesheets4':
#> 
#>     request_generate, request_make
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"))
#> [1] "/tmp/RtmpCH9Ob3/iris-1f3727753d8d.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"))
#> Local file:
#> • /tmp/RtmpCH9Ob3/iris-1f3727753d8d.csv
#> Uploaded into Drive file:
#> • iris-1f3727753d8d <id: 1kL0iB6p-BKlk75Z2l47ZamiypeXO0cCy_hzlCj-jPPE>
#> With MIME type:
#> • application/vnd.google-apps.spreadsheet
#> # A dribble: 1 × 3
#>   name              id                                      drive_resource
#>   <chr>             <drv_id>                                <list>        
#> 1 iris-1f3727753d8d 1kL0iB6p-BKlk75Z2l47ZamiypeXO0cCy_hzlC… <named list>

# 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")
#> ✔ Reading from iris-1f3727753d8d.
#> ✔ Range B1:D6.
#> # A tibble: 5 × 3
#>   Sepal.Width Petal.Length Petal.Width
#>         <dbl>        <dbl>       <dbl>
#> 1         3.5          1.4         0.2
#> 2         3            1.4         0.2
#> 3         3.2          1.3         0.2
#> 4         3.1          1.5         0.2
#> 5         3.6          1.4         0.2

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))
#> [1] "/tmp/RtmpCH9Ob3/iris-1f3727753d8d.xlsx"
drive_download(iris_ss, path = iris_xlsxfile, overwrite = TRUE)
#> File downloaded:
#> • iris-1f3727753d8d <id: 1kL0iB6p-BKlk75Z2l47ZamiypeXO0cCy_hzlCj-jPPE>
#> Saved locally as:
#> • /tmp/RtmpCH9Ob3/iris-1f3727753d8d.xlsx

Read xlsx with readxl

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

if (requireNamespace("readxl", quietly = TRUE)) {
  readxl::read_excel(iris_xlsxfile)  
}
#> # A tibble: 150 × 5
#>    Sepal.Length Sepal.Width Petal.Length Petal.Width Species
#>           <dbl>       <dbl>        <dbl>       <dbl> <chr>  
#>  1          5.1         3.5          1.4         0.2 setosa 
#>  2          4.9         3            1.4         0.2 setosa 
#>  3          4.7         3.2          1.3         0.2 setosa 
#>  4          4.6         3.1          1.5         0.2 setosa 
#>  5          5           3.6          1.4         0.2 setosa 
#>  6          5.4         3.9          1.7         0.4 setosa 
#>  7          4.6         3.4          1.4         0.3 setosa 
#>  8          5           3.4          1.5         0.2 setosa 
#>  9          4.4         2.9          1.4         0.2 setosa 
#> 10          4.9         3.1          1.5         0.1 setosa 
#> # ℹ 140 more rows

Clean up

file.remove(iris_tempfile, iris_xlsxfile)
#> [1] TRUE TRUE
drive_trash(iris_ss)
#> File trashed:
#> • iris-1f3727753d8d <id: 1kL0iB6p-BKlk75Z2l47ZamiypeXO0cCy_hzlCj-jPPE>