Using the googlesheets package to work with Google Sheets

by Andrie de Vries

Just more than a year ago I cobbled together some code to work with the (then) new version of Google Sheets. You can still find my musings and code at the blog post Reading data from the new version of Google Spreadsheets.

Since then, Jennifer Bryan (@JennyBryan) published a wonderful package that does far more than I ever tried, in a much more elegant way.

This package just works!

I read the first few lines of the excellent vignette, pasted the code into my R session, and had a listing of my Google sheets within seconds:

library(googlesheets)
library(dplyr)
(my_sheets <- gs_ls())
# (expect a prompt to authenticate with Google interactively HERE)
my_sheets %>% glimpse()

The package neatly handles handshaking with Google to get access to your authorisation token. It automatically opens a web browser where you log in to your Google account. Google then presents you with a token that you simply paste into your R session command line, and from there everything just works.

(my_sheets <- gs_ls())

Source: local data frame [6 x 10]

sheet_title                author      perm version         updated 
1 RRO with MKL benchmark   andrie         r new 2015-06-03 15:52:21
2 FMH: UAT Test plan (v1.… des.holmes    rw new 2015-02-10 00:07:26
3 Hospital Dat 2013_08_22  markratnaraj… rw new 2013-10-13 20:57:55
4 Strategy Time TBG        clementdata   rw new 2013-04-23 19:43:21
5 hspot-rfp-alpha-scoresh… benoit.chovet rw new 2013-02-22 16:20:37
6 2012-Jan Mobile interne… simbamangu     r new 2013-01-07 06:03:56
Variables not shown: ws_feed (chr), alternate (chr), self (chr), alt_key (chr)

Since the googlesheets package is on CRAN, you can install by using:

install.packages("googlesheets")

I highly recommend reading the following excellent resources:

• The package vignette at https://cran.r-project.org/web/packages/googlesheets/vignettes/basic-usage.html
• The README at the project's github page https://github.com/jennybc/googlesheets
• Jenny's presentation at UseR!2015, available at https://speakerdeck.com/jennybc/googlesheets-talk-at-user2015