Slide 1

Slide 1 text

googlesheets

Slide 2

Slide 2 text

useR! 2015 Dr. Jennifer (Jenny) Bryan Dept. of Statistics & Michael Smith Laboratories, UBC [email protected] @JennyBryan http://stat545-ubc.github.io @STAT545 http://www.stat.ubc.ca/~jenny/ @jennybc googlesheets Google Spreadsheets R API

Slide 3

Slide 3 text

+ googlesheets = R wrapper around the Sheets & Drive APIs +

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

Why would a UseR use a Google Sheet? collaboration - link, don’t attach! built-in functions to import web data - importxml(), importhtml(), importfeed() magic templates - TAGS, Amazon Price Tracker web services - ifttt.com

Slide 6

Slide 6 text

Why important to have an R interface to Google Sheets? Just. Because. Lightweight data store for Shiny

Slide 7

Slide 7 text

The participant who loses the most weight (as measured by the final weight as a percentage of his starting weight) shall be declared the winner. Jim Craig

Slide 8

Slide 8 text

Craig won :(

Slide 9

Slide 9 text

“Examples of permitted services include, but are not limited to, cooking, gardening, handiwork, caddying, office work, serving food and drinks or running errands.”

Slide 10

Slide 10 text

~1800 consecutive days of weight data

Slide 11

Slide 11 text

iOS Android enter data from a phone enter data w/o WiFi

Slide 12

Slide 12 text

library(googlesheets) ... jc_ss <- gs_title("jim-craig-weight-loss") jc_dat <- gs_read(jc_ss) jc_dat <- jc_dat %>% mutate(date = as.Date(date, format = "%m/%d/%Y")) %>% gather("who", "weight", Jim, Craig) p <- ggplot(jc_dat, aes(x = date, y = weight, color = who)) + geom_point(alpha = 0.35) + geom_smooth(se = FALSE, lwd = 2) + scale_y_continuous(labels = lbs_formatter) print(direct.label(p))

Slide 13

Slide 13 text

No content

Slide 14

Slide 14 text

I wanted live stats!

Slide 15

Slide 15 text

vn_ss <- gs_title("2015-05-23_seaRM-at-vanNH") #> Sheet successfully identifed: "2015-05-23_seaRM-at-vanNH" game_play <- gs_read(vn_ss, ws = 10, range = cell_limits(c(2, NA), c(1, 2))) #> Accessing worksheet titled "10" head(game_play) #> Source: local data frame [6 x 2] #> #> Offense Defense #> 1 NA 42P #> 2 21pu NA #> 3 NA 29pu #> 4 NA 31 #> 5 NA 29 #> 6 NA 31

Slide 16

Slide 16 text

point_info <- vn_ss %>% gs_read_cellfeed(ws = 10, range = "D1:D4") %>% gs_simplify_cellfeed(col_names = FALSE) #> Accessing worksheet titled "10" point_info #> D1 D2 D3 D4 #> "Seattle Rainmakers" "1" "3:12:00" "1:49:00"

Slide 17

Slide 17 text

install.packages(“googlesheets”)   library(devtools)   install_github(“jennybc/googlesheets”) not there yet but it’s in process

Slide 18

Slide 18 text

gs_*

Slide 19

Slide 19 text

> gs_ls("Gap") Source: local data frame [4 x 10] sheet_title author perm version updated 1 Gapminder 2007 Can Write gspreadr rw new 2015-04-15 19:33:55 2 Gapminder gspreadr rw new 2015-03-23 20:59:10 3 Gapminder_old gspreadr rw new 2015-03-23 20:23:06 4 Gapminder by Continent joannaazhao rw new 2015-01-20 22:23:56 Variables not shown: sheet_key (chr), ws_feed (chr), alternate (chr), self (chr), alt_key (chr)

Slide 20

Slide 20 text

ss  <-­‐  gs_title("Gapminder")   ss  <-­‐  gs_key("1BzfL0...")   ss  <-­‐  gs_url("https://docs.google.com/spreadsheets/d/1BzfL0.../" ) register a Sheet

Slide 21

Slide 21 text

> ss <- gs_title("Gapminder") > ss Spreadsheet title: Gapminder Date of googlesheets registration: 2015-06-02 04:32:55 GMT Date of last spreadsheet update: 2015-03-23 20:34:08 GMT visibility: private permissions: rw version: new Contains 5 worksheets: (Title): (Nominal worksheet extent as rows x columns) Africa: 625 x 6 Americas: 301 x 6 Asia: 397 x 6 Europe: 361 x 6 Oceania: 25 x 6 Key: 1HT5B8SgkKqHdqHJmn5xiuaC04Ngb7dG9Tv94004vezA

Slide 22

Slide 22 text

gs_gap()   gs_gap_key()   gs_gap_url() Gapminder Sheet available for examples, testing, practice

Slide 23

Slide 23 text

ss  <-­‐  gs_gap()   ss  <-­‐  gs_gap_key()  %>%  gs_key()   ss  <-­‐  gs_gap_url()  %>%  gs_url()   ss  <-­‐  gs_gap()   ss  <-­‐  gs_key(gs_gap_key())   ss  <-­‐  gs_url(gs_gap_url())

Slide 24

Slide 24 text

gs_upload() .xls .xlsx .xlt .xltx .xltm .xlsm .csv .tsv .txt .tab .ods

Slide 25

Slide 25 text

gs_download() .csv .pdf .xlsx

Slide 26

Slide 26 text

gs_new()           gs_copy()   gs_upload()

Slide 27

Slide 27 text

gs_delete()   gs_grepdel()   gs_vecdel()

Slide 28

Slide 28 text

gs_ws_*

Slide 29

Slide 29 text

gs_ws_ls()   gs_ws_new()   gs_ws_rename()   gs_ws_delete()

Slide 30

Slide 30 text

gs_read()

Slide 31

Slide 31 text

gs_read(...,  ws  =  3,  ...)   gs_read(...,  ws  =  “Europe”,  ...)   gs_read(...,  range  =  "D12:F15")   gs_read(...,  range  =  "R1C12:R6C15")   gs_read(...,  range  =  cell_limits(c(1,  6),  c(1,  15)))   gs_read(...,  range  =  cell_limits(c(3,  NA),  c(NA,  10)))   gs_read(...,  range  =  cell_rows(1:100))   gs_read(...,  range  =  cell_cols(3:8))   gs_read(...,  range  =  cell_cols("B:MZ"))   gs_read(...,  range  =  anchored("B4",  dim  =  c(2,  10)))

Slide 32

Slide 32 text

gs_new()   gs_ws_new()   gs_edit_cells()   gs_add_row()

Slide 33

Slide 33 text

gs_auth()   gs_user()

Slide 34

Slide 34 text

Technical bits of interest flexible auth flow: - interactive - unattended “batch” use - Shiny use we are actually testing - against the Sheets and Drive APIs - including operations that require authentication - on Travis-CI

Slide 35

Slide 35 text

https://github.com/hrbrmstr/nifffty https://ifttt.com

Slide 36

Slide 36 text

=IMPORTXML(URL, XPATH) https://jennybc.shinyapps.io/03_craigslist-lost-and-found

Slide 37

Slide 37 text

https://github.com/jennybc/googlesheets/tree/master/inst/shiny-examples Sample apps

Slide 38

Slide 38 text

thanks Joanna Zhao! also Karthik Ram, Hadley Wickham, Noam Ross