Using the googlesheets package to work with Google Sheets
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.
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
R-bloggers.com offers daily e-mail updates about R news and tutorials about learning R and many other topics. Click here if you're looking to post or find an R/data-science job.
Want to share your content on R-bloggers? click here if you have a blog, or here if you don't.