README.Rmd

---
output:
  github_document:
    toc: true 
    toc_depth: 3
---

<!-- README.md is generated from README.Rmd. Please edit that file -->

[![Build Status](https://travis-ci.org/jennybc/googlesheets.svg?branch=master)](https://travis-ci.org/jennybc/googlesheets) [![Coverage Status](https://coveralls.io/repos/jennybc/googlesheets/badge.svg)](https://coveralls.io/r/jennybc/googlesheets) [![DOI](https://zenodo.org/badge/16122/jennybc/googlesheets.svg)](http://dx.doi.org/10.5281/zenodo.21972) [![CRAN version](http://www.r-pkg.org/badges/version/googlesheets)](https://cran.r-project.org/package=googlesheets) ![](http://cranlogs.r-pkg.org/badges/grand-total/googlesheets)

```{r, echo = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>",
  fig.path = "README-"
)
```

```{r make-clean, echo = FALSE, include = FALSE}
## if previous compilation errored out, intended clean up may be incomplete
suppressWarnings(
  file.remove(c("~/tmp/gapminder-africa.csv", "~/tmp/gapminder.xlsx")))
googlesheets::gs_vecdel(c("foo", "mini-gap", "iris"), verbose = FALSE)
```
---

Google Sheets R API
---

Access and manage Google spreadsheets from R with `googlesheets`. 

Features:

  - Access a spreadsheet by its title, key or URL.
  - Extract data or edit data.
  - Create | delete | rename | copy | upload | download spreadsheets and worksheets.
  - Upload local Excel workbook into a Google Sheet and vice versa.

`googlesheets` is inspired by [gspread](https://github.com/burnash/gspread), a Google Spreadsheets Python API

The exuberant prose in this README is inspired by [Tabletop.js](https://github.com/jsoma/tabletop): If you've ever wanted to get data in or out of a Google Spreadsheet from R without jumping through a thousand hoops, welcome home!

### Install googlesheets

The released version is available on CRAN

```{r eval = FALSE}
install.packages("googlesheets")
```

Or you can get the development version from GitHub:

```{r eval = FALSE}
devtools::install_github("jennybc/googlesheets")
```

### Vignettes

GitHub versions:

  * [Basic usage](https://rawgit.com/jennybc/googlesheets/master/vignettes/basic-usage.html)
  * [Formulas and formatted numbers](https://rawgit.com/jennybc/googlesheets/master/vignettes/formulas-and-formatted-numbers.html)
  * [Managing OAuth tokens](https://rawgit.com/jennybc/googlesheets/master/vignettes/managing-auth-tokens.html)

### Talks

  * [Slides](https://speakerdeck.com/jennybc/googlesheets-talk-at-user2015) for a talk in July 2015 at [useR! 2015](http://user2015.math.aau.dk)
  * [Slides](https://speakerdeck.com/jennybc/googlesheets-1) for an [rOpenSci Community Call in March 2016](https://github.com/ropensci/commcalls/issues/9)

### Load googlesheets

`googlesheets` is designed for use with the `%>%` pipe operator and, to a lesser extent, the data-wrangling mentality of [`dplyr`](https://cran.r-project.org/package=dplyr). This README uses both, but the examples in the help files emphasize usage with plain vanilla R, if that's how you roll. `googlesheets` uses `dplyr` internally but does not require the user to do so. You can make the `%>%` pipe operator available in your own work by loading [`dplyr`](https://cran.r-project.org/package=dplyr) or [`magrittr`](https://cran.r-project.org/package=magrittr).

```{r load-package}
library("googlesheets")
suppressPackageStartupMessages(library("dplyr"))
```

### Function naming convention

To play nicely with tab completion, we use consistent prefixes:

  * `gs_` = all functions in the package.
  * `gs_ws_` = all functions that operate on worksheets or tabs within a spreadsheet.
  * `gd_` = something to do with Google Drive, usually has a `gs_` synonym, might one day migrate to a Drive client.

### Quick demo

Here's how to get a copy of a Gapminder-based Sheet we publish for practicing and follow along. You'll be sent to the browser to authenticate yourself with Google at this point.

```{r copy-gapminder, eval = FALSE}
gs_gap() %>% 
  gs_copy(to = "Gapminder")
## or, if you don't use pipes
gs_copy(gs_gap(), to = "Gapminder")
```

Register a Sheet (in this case, by title):

```{r}
gap <- gs_title("Gapminder")
```

Here's a registered `googlesheet` object:

```{r}
gap
```

Visit a registered `googlesheet` in the browser:

```{r eval = FALSE}
gap %>% gs_browse()
gap %>% gs_browse(ws = "Europe")
```

Read all the data in a worksheet:

```{r}
africa <- gs_read(gap)
str(africa)
head(africa)
```

Some of the many ways to target specific cells:

```{r eval = FALSE}
gap %>% gs_read(ws = 2, range = "A1:D8")
gap %>% gs_read(ws = "Europe", range = cell_rows(1:4))
gap %>% gs_read(ws = "Africa", range = cell_cols(1:4))
```

Full `readr`-style control of data ingest -- highly artificial example!

```{r}
gap %>%
  gs_read(ws = "Oceania", col_names = paste0("Z", 1:6),
          na = c("1962", "1977"), col_types = "cccccc", skip = 1, n_max = 7)
```

Create a new Sheet from an R object:

```{r}
iris_ss <- gs_new("iris", input = head(iris, 3), trim = TRUE)
```

Edit some arbitrary cells and append a row:

```{r}
iris_ss <- iris_ss %>% 
  gs_edit_cells(input = c("what", "is", "a", "sepal", "anyway?"),
                anchor = "A2", byrow = TRUE)
iris_ss <- iris_ss %>% 
  gs_add_row(input = c("sepals", "support", "the", "petals", "!!"))
```

Look at what we have wrought:

```{r}
iris_ss %>% 
  gs_read()
```

Download this precious thing as csv (other formats are possible):

```{r}
iris_ss %>% 
  gs_download(to = "iris-ish-stuff.csv", overwrite = TRUE)
```

Download this precious thing as an Excel workbook (other formats are possible):

```{r}
iris_ss %>% 
  gs_download(to = "iris-ish-stuff.xlsx", overwrite = TRUE)
```

Upload a Excel workbook into a new Sheet:

```{r}
gap_xlsx <- gs_upload(system.file("mini-gap", "mini-gap.xlsx",
                                  package = "googlesheets"))
```

Clean up our mess locally and on Google Drive:

```{r eval = FALSE}
gs_vecdel(c("iris", "Gapminder"))
file.remove(c("iris-ish-stuff.csv", "iris-ish-stuff.xlsx"))
```

```{r include = FALSE}
gs_delete(iris_ss)
file.remove(c("iris-ish-stuff.csv", "iris-ish-stuff.xlsx"))
```

Remember, [the vignette](https://rawgit.com/jennybc/googlesheets/master/vignettes/basic-usage.html) shows a lot more usage.

### Overview of functions

```{r include = FALSE}
fxn_table <-
"fxn,description
gs_ls(), List Sheets
gs_title(), Register a Sheet by title
gs_key(), Register a Sheet by key
gs_url(), Register a Sheet by URL
gs_gs(), Re-register a `googlesheet`
gs_browse(), Visit a registered `googlesheet` in the browser
gs_read(), Read data and let `googlesheets` figure out how
gs_read_csv(), Read explicitly via the fast exportcsv link
gs_read_listfeed(), Read explicitly via the list feed
gs_read_cellfeed(), Read explicitly via the cell feed
gs_reshape_cellfeed(), Reshape cell feed data into a 2D thing
gs_simplify_cellfeed(), Simplify cell feed data into a 1D thing
gs_edit_cells(), Edit specific cells
gs_add_row(), Append a row to pre-existing data table
gs_new(), Create a new Sheet and optionally populate
gs_copy(), Copy a Sheet into a new Sheet
gs_rename(), Rename an existing Sheet
gs_ws_ls(), List the worksheets in a Sheet
gs_ws_new(), Create a new worksheet and optionally populate
gs_ws_rename(), Rename a worksheet
gs_ws_delete(), Delete a worksheet
gs_delete(), Delete a Sheet
gs_grepdel(), Delete Sheets with matching titles
gs_vecdel(), Delete the named Sheets 
gs_upload(), Upload local file into a new Sheet
gs_download(), Download a Sheet into a local file
gs_auth(), Authorize the package
gs_deauth(), De-authorize the package
gs_user(), Get info about current user and auth status
gs_webapp_auth_url(), Facilitates auth by user of a Shiny app
gs_webapp_get_token(), Facilitates auth by user of a Shiny app
gs_gap(), Registers a public Gapminder-based Sheet (for practicing)
gs_gap_key(), Key of the Gapminder practice Sheet
gs_gap_url(), Browser URL for the Gapminder practice Sheet
"
```

```{r as.is = TRUE, echo = FALSE}
knitr::kable(read.csv(text = fxn_table))
```

### What the hell do I do with this?

Think of `googlesheets` as a read/write CMS that you (or your less R-obsessed friends) can edit through Google Docs, as well via R. It's like Christmas up in here.

Use a [Google Form](http://www.google.com/forms/about/) to conduct a survey, which populates a Google Sheet.

  * The `googleformr` package provides an R API for Google Forms, allowing useRs to POST data securely to Google Forms without authentication. On [CRAN](https://cran.r-project.org/package=googleformr) and [GitHub](https://github.com/data-steve/googleformr) (README has lots of info and links to blog posts).

Gather data while you're in the field in a Google Sheet, maybe [with an iPhone](https://itunes.apple.com/us/app/google-sheets/id842849113?mt=8) or [an Android device](https://play.google.com/store/apps/details?id=com.google.android.apps.docs.editors.sheets&hl=en). Take advantage of [data validation](https://support.google.com/docs/answer/139705?hl=en) to limit the crazy on the way in. You do not have to be online to edit a Google Sheet! Work offline via [the Chrome browser](https://support.google.com/docs/answer/2375012?hl=en), the [Sheets app for Android](https://play.google.com/store/apps/details?id=com.google.android.apps.docs.editors.sheets&hl=en), or the [Sheets app for iOS](https://itunes.apple.com/us/app/google-sheets/id842849113?mt=8).

There are various ways to harvest web data directly into a Google Sheet. For example:

  * [IFTTT](https://ifttt.com), which stands for "if this, then that", makes it easy to create recipes in which changes in one web service, such as Gmail or Instagram, trigger another action, such as writing to a Google Sheet.
    - Martin Hawksey blog post about [feeding a Google Sheet from IFTTT](https://mashe.hawksey.info/2012/09/ifttt-if-i-do-that-on-insert-social-networkrss-feedother-then-add-row-to-google-spreadsheet/). 
  * `IMPORTXML(), IMPORTHTML(), IMPORTFEED()`: Google Sheets offer functions to populate Sheets based on web data.
    - Aylien.com [blog post](http://blog.aylien.com/post/114757623598/sentiment-analysis-of-restaurant-reviews) on using `=IMPORTXML()` to populate a Google Sheet with restaurant reviews and ratings from TripAdvisor.
    - Martin Hawksey blog post, [Feeding Google Spreadsheets](https://mashe.hawksey.info/2012/10/feeding-google-spreadsheets-exercises-in-import/), shows how to scrape web data into a Google Sheet with no programming.
  * Martin Hawksey offers [TAGS](https://tags.hawksey.info), a free Google Sheet template to setup and run automated collection of search results from Twitter.

Use `googlesheets` to get all that data into R.

Use it in a Shiny app! *[Several example apps](inst/shiny-examples) come with the package.*

What other ideas do you have?