14 Google Sheets

Google Sheets are a useful way to collect, store, and collaboratively work with data. The googlesheets4 package wraps the Sheets API, making it easy for you to work with Google Sheets in R.

The “4” in googlesheets4 refers to the most recent version (v4) of the Google Sheets API. There’s also an R package called googlesheets, which uses an older version (v3) of the Google Sheets API. If you’ve worked with the googlesheets package previously, note that the Sheets API v3 will be shut down on March 3, 2020, so you’ll need to switch over to googlesheets4.

14.1 Reading

Reading data stored in a Google Sheet into R will probably be your most common use of googlesheets4. Here, we’ll read in the data from our example sheet, which contains data from Gapminder.

To read in the data, we need a way to identify the Google Sheet. googlesheets4 supports multiple ways of identifying sheets, but we recommend using the sheet ID, as it’s stable and concise. You can find the ID of a Google Sheet in its URL:

If you want to extract an ID from a URL programmatically, you can also use the function as_sheets_id().

We’ve stored the ID for the Gapminder sheet in the parameters section up at the top. Here it is:

#> [1] "1BzfL0kZUz1TsI5zxJF1WNF01IxvC67FbOJUiiGMZ_mQ"

Now, we can use the googlesheets4 function read_sheet() to read in the data. read_sheet()’s first argument, ss, takes the sheet ID.

#> Reading from "test-gs-gapminder"
#> Range "Africa"
#> # A tibble: 624 x 6
#>   country continent  year lifeExp      pop gdpPercap
#>   <chr>   <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#> 1 Algeria Africa     1952    43.1  9279525     2449.
#> 2 Algeria Africa     1957    45.7 10270856     3014.
#> 3 Algeria Africa     1962    48.3 11000948     2551.
#> 4 Algeria Africa     1967    51.4 12760499     3247.
#> 5 Algeria Africa     1972    54.5 14760787     4183.
#> 6 Algeria Africa     1977    58.0 17152804     4910.
#> # … with 618 more rows

Notice that the original Sheet contains multiple sheets, one for each continent. We can list all these sheets by using the function sheets_sheets().

#> Warning: 'sheets_sheets()' is deprecated.
#> Use 'sheets_sheet_names()' instead.
#> See help("Deprecated") and help("googlesheets4-deprecated").
#> [1] "Africa"   "Americas" "Asia"     "Europe"   "Oceania"

By default, sheets_read() reads in the first sheet. Here, that’s the Africa sheet. If we want to read in Asia, we can specify the sheet argument.

#> Reading from "test-gs-gapminder"
#> Range "'Asia'"
#> # A tibble: 396 x 6
#>   country     continent  year lifeExp      pop gdpPercap
#>   <chr>       <chr>     <dbl>   <dbl>    <dbl>     <dbl>
#> 1 Afghanistan Asia       1952    28.8  8425333      779.
#> 2 Afghanistan Asia       1957    30.3  9240934      821.
#> 3 Afghanistan Asia       1962    32.0 10267083      853.
#> 4 Afghanistan Asia       1967    34.0 11537966      836.
#> 5 Afghanistan Asia       1972    36.1 13079460      740.
#> 6 Afghanistan Asia       1977    38.4 14880372      786.
#> # … with 390 more rows

14.2 Writing

As of 2019-12-05, you cannot write to Google Sheets with the googlesheets4 package. Check back for updates.

14.3 Finding sheets

It can sometimes be difficult to find the exact Google Sheet you’re looking for. googlesheets4 includes a handy function that will return the names of the all your sheets, alongside their IDs, in an object called a dribble. A dribble is a tibble specifically for storing metadata about Google Drive files.

#> # A tibble: 4 x 3
#>   name            id                                           drive_resource   
#> * <chr>           <chr>                                        <list>           
#> 1 top-secret-data 1JiEm6MtoLRDKPB_FZRgO51C15gQHUb0e6sS8e1NoIXA <named list [33]>
#> 2 important-data  138G9gqJn2Y5cuAx4Ll4oXrKSmD-h-Kp-3tlEsiNPsk4 <named list [33]>
#> 3 my-sheet-2      1Ozo1wdSIoWxhoxvJZHaTRE6Wko0RNGfMzKsDzNQ-xPc <named list [33]>
#> 4 my-sheet-1      16cmknhzUdYh-pY5F63AD9E9YaeVpV99mu8219W7yc0E <named list [33]>

Note that sheets_find() will lists both sheets that you own and private sheets that you have access to. These are the same sheets that you can see on your Google Sheets homepage.

Now, you can easily search for a sheet by piping the results of sheets_find() into view().

14.4 Authentication

14.4.1 Interactive session

When you run R code in the console or in an R Markdown chunk, you’re in an interactive session. R understands that it’s interacting with a human, and so can prompt you for input or actions. In an interactive session, you don’t need to worry much about authentication. googlesheets4 will do most of the work for you.

The first time you call a googlesheets4 function that requires authentication (e.g., sheets_read(ss = id_gapminder)), a browser tab will open and prompt you to sign into Google. Sign into your account and then return to RStudio.

By default, your user credentials will now be stored as something called a gargle token. gargle is the name of an R package for working with Google APIs. The next time googlesheets4 requires authentication, it will use this token to authenticate you. On a Mac, you can locate your gargle token by looking in ~/.R/gargle/.

14.4.2 Non-interactive session

When you knit an R Markdown, you’re using R non-interactively. googlesheets4 can’t prompt you to sign into Google, because it doesn’t assume that there’s a human standing by to do so. This should only be a problem if you’re trying to knit an R Markdown document that uses googlesheets4 and you’ve never authenticated with googlesheets4 before. The easiest way to quickly authenticate and set up your gargle token is to run googlesheets4::sheets_auth() (you can run this anywhere: console, R Markdown chunk, etc.). Once you’ve signed into Google and returned to RStudio, try knitting your document.

If you’ve authenticated with googlesheets4 before, but your R Markdown document never finishing knitting, you may need to update your gargle token. Run googlesheets4::sheets_auth() and then try knitting again.