googlesheets talk at UseR2015

Transcript

1. googlesheets
   

   View Slide

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

   View Slide

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

   View Slide

4. View Slide

5. 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
   

   View Slide

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

   View Slide

7. 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
   

   View Slide

8. Craig won :(
   

   View Slide

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

   View Slide

10. ~1800 consecutive
    days of weight data
    

    View Slide

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

    View Slide

12. 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))
    

    View Slide

13. View Slide

14. I wanted live stats!
    

    View Slide

15. 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
    

    View Slide

16. 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"
    

    View Slide

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

    View Slide

18. gs_*
    

    View Slide

19. > 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)
    

    View Slide

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

    View Slide

21. > 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
    

    View Slide

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

    View Slide

23. 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())
    

    View Slide

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

    View Slide

25. gs_download()
    .csv .pdf .xlsx
    

    View Slide

26. gs_new()          
    gs_copy()  
    gs_upload()
    

    View Slide

27. gs_delete()  
    gs_grepdel()  
    gs_vecdel()
    

    View Slide

28. gs_ws_*
    

    View Slide

29. gs_ws_ls()  
    gs_ws_new()  
    gs_ws_rename()  
    gs_ws_delete()
    

    View Slide

30. gs_read()
    

    View Slide

31. 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)))
    

    View Slide

32. gs_new()  
    gs_ws_new()  
    gs_edit_cells()  
    gs_add_row()
    

    View Slide

33. gs_auth()  
    gs_user()
    

    View Slide

34. 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
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide