library(googlesheets4)

This article takes a quick tour of the main features of googlesheets4. Remember to see the articles for more detailed treatment of all these topics and more.

read_sheet(), a.k.a. sheets_read()

read_sheet() is the main “read” function and should evoke readr::read_csv() and readxl::read_excel(). It’s an alias for sheets_read(), because most functions in googlesheets4 actually start with sheets_. googlesheets4 is pipe-friendly (and reexports %>%), but works just fine without the pipe.

read_sheet() is designed to “just work”, for most purposes, most of the time. It can read straight from a Sheets browser URL:

sheets_read("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077")
#> Reading from "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.
#> # … with 619 more rows

However, these URLs are not pleasant to work with. More often, you will want to identify a Sheet by its ID:

sheets_read("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY")
#> Reading from "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.
#> # … with 619 more rows

or by its name, which requires an assist from the googledrive package (googledrive.tidyverse.org):

library(googledrive)

drive_get("gapminder") %>%
  sheets_read()
#> 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.
#> # … with 619 more rows

Note that the name-based approach above will only work if you have access to a Sheet named “gapminder”. Sheet names cannot be used as absolute identifiers; only a Sheet ID can play that role.

For more Sheet identification concepts and strategies, see the article Find and Identify Sheets. See the article Read Sheets for more about reading from a specific (work)sheet or ranges, setting column type, and getting low-level cell data.

Example Sheets and sheets_browse()

We’ve made a few Sheets available to “anyone with a link”, for use in examples and docs. Two helper functions make it easy to get your hands on these file IDs.

  • sheets_examples() lists all the example Sheets and it can also filter by matching names to a regular expression.
  • sheets_example() requires a regular expression and returns exactly 1 Sheet ID (or throws an error).
sheets_example("chicken-sheet") %>%
  sheets_read()
#> Reading from "chicken-sheet"
#> Range "chicken.csv"
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

If you’d like to see a Sheet in the browser, including our example Sheets, use sheets_browse():

Sheet metadata

sheets_get() exposes Sheet metadata, such as details on worksheets and named ranges.

ss <- sheets_example("deaths")

sheets_get(ss)
#>   Spreadsheet name: deaths
#>                 ID: 1tuYKzSbLukDLe5ymf_ZKdQA8SfOyeMM7rmf6D6NJpxg
#>             Locale: en
#>          Time zone: America/Los_Angeles
#>        # of sheets: 2
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>         arts: 1000 x 26
#>        other: 1000 x 26
#> 
#> (Named range): (A1 range)    
#>     arts_data: 'arts'!A5:F15 
#>    other_data: 'other'!A5:F15

sheets_sheet_properties(ss)
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

sheets_sheet_names(ss)
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

sheets_sheet_properties() and sheets_sheet_names() are two members of a larger family of functions for dealing with the (work)sheets within a (spread)Sheet.

The metadata exposed by sheets_get() is also revealed whenever you print an object that is known to be a sheets_id (an S3 class we use to make Sheet IDs).

sheets_get() is related to googledrive::drive_get(). Both functions return metadata about a file on Google Drive, such as its ID and name. However, sheets_get() reveals additional metadata that is specific to Drive files that happen to be Sheets, such as info about worksheets and named ranges.

Writing Sheets

The writing functions are the most recent additions and are likely to see some refinements re: user interface and which function does what.

sheets_write() writes a data frame into a Sheet. The only required argument is the data.

df <- data.frame(x = 1:3, y = letters[1:3])

ss <- sheets_write(df)
#> Creating new Sheet: "wailsome-sakimonkey"
ss
#>   Spreadsheet name: <unknown>
#>                 ID: 1zfSG2DpdiNb4hBIwVrauccjvDRWxexJM7eKSyzPqmVU
#>             Locale: <unknown>
#>          Time zone: <unknown>
#>        # of sheets: <unknown>
#> 
#> Unable to get metadata for this Sheet. Error details:
#> Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

You’ll notice the new (spread)Sheet has a randomly generated name. If that is a problem, use sheets_create() instead, which affords more control over various aspects of the new Sheet.

Let’s start over: we delete that Sheet and call sheets_create(), so we can specify the new Sheet’s name.

googledrive::drive_rm(ss)
#> Files deleted:
#>   * wailsome-sakimonkey: 1zfSG2DpdiNb4hBIwVrauccjvDRWxexJM7eKSyzPqmVU

ss <- sheets_create("fluffy-bunny", sheets = df)
#> Creating new Sheet: "fluffy-bunny"
ss
#>   Spreadsheet name: <unknown>
#>                 ID: 125SioRmuAK-yMxYUzmSKjfAb36_cDtY9BkQwObisqxk
#>             Locale: <unknown>
#>          Time zone: <unknown>
#>        # of sheets: <unknown>
#> 
#> Unable to get metadata for this Sheet. Error details:
#> Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

sheets_write() can write to new or existing (work)sheets in this Sheet. Let’s write the chickwts data to a new sheet in ss.

sheets_write(chickwts, ss)
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.
ss
#>   Spreadsheet name: <unknown>
#>                 ID: 125SioRmuAK-yMxYUzmSKjfAb36_cDtY9BkQwObisqxk
#>             Locale: <unknown>
#>          Time zone: <unknown>
#>        # of sheets: <unknown>
#> 
#> Unable to get metadata for this Sheet. Error details:
#> Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

We can also use sheets_write() to replace the data in an existing sheet.

sheets_write(data.frame(x = 4:10, letters[4:10]), ss, sheet = "df")
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.
ss
#>   Spreadsheet name: <unknown>
#>                 ID: 125SioRmuAK-yMxYUzmSKjfAb36_cDtY9BkQwObisqxk
#>             Locale: <unknown>
#>          Time zone: <unknown>
#>        # of sheets: <unknown>
#> 
#> Unable to get metadata for this Sheet. Error details:
#> Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

sheets_append() adds one or more rows to an existing sheet.

sheets_append(data.frame(x = 11, letters[11]), ss, sheet = "df")
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.
ss
#>   Spreadsheet name: <unknown>
#>                 ID: 125SioRmuAK-yMxYUzmSKjfAb36_cDtY9BkQwObisqxk
#>             Locale: <unknown>
#>          Time zone: <unknown>
#>        # of sheets: <unknown>
#> 
#> Unable to get metadata for this Sheet. Error details:
#> Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

sheets_edit() makes it possible to write arbitrary data, into an arbitrary range. It has a very different “feel” from sheets_create(), sheets_write(), and sheets_append(), all of which assume we’re writing or growing a table of data in a (work)sheet. sheets_edit() make fewer assumptions about what it’s writing and why.

There is also a family of sheets_sheet_*() functions that do pure (work)sheet operations, such as add and delete.

We take one last look at the sheets we created in ss, then clean up.

sheets_sheet_properties(ss)
#> Error: Client error: (429) RESOURCE_EXHAUSTED
#>   * Either out of resource quota or reaching rate limiting. The client should look for google.rpc.QuotaFailure error detail for more information.
#>   * Quota exceeded for quota group 'ReadGroup' and limit 'Read requests per user per 100 seconds' of service 'sheets.googleapis.com' for consumer 'project_number:603366585132'.
#> 
#> Error details:
#>   * Error details of type 'google.rpc.Help' are not implemented yet.
#>   * Workaround: use `tryCatch()` and inspect error payload yourself.
#>   * Please open an issue at https://github.com/r-lib/gargle/issues, so we can fix.

googledrive::drive_rm(ss)
#> Files deleted:
#>   * fluffy-bunny: 125SioRmuAK-yMxYUzmSKjfAb36_cDtY9BkQwObisqxk

The article Write Sheets has even more detail.