• Stars
    star
    347
  • Rank 117,474 (Top 3 %)
  • Language
    R
  • License
    Other
  • Created almost 7 years ago
  • Updated about 1 month ago

Reviews

There are no reviews yet. Be the first to send feedback to the community and the maintainers!

Repository Details

Google Spreadsheets R API (reboot of the googlesheets package)

googlesheets4

CRAN status R-CMD-check Codecov test coverage

Overview

googlesheets4 provides an R interface to Google Sheets via the Sheets API v4. It is a reboot of an earlier package called googlesheets.

Why 4? Why googlesheets4? Did I miss googlesheets1 through 3? No.Β The idea is to name the package after the corresponding version of the Sheets API. In hindsight, the original googlesheets should have been googlesheets3.

Installation

You can install the released version of googlesheets4 from CRAN with:

install.packages("googlesheets4")

And the development version from GitHub with:

#install.packages("pak")
pak::pak("tidyverse/googlesheets4")

Cheatsheet

You can see how to read data with googlesheets4 in the data import cheatsheet, which also covers similar functionality in the related packages readr and readxl.

thumbnail of data import cheatsheet

Auth

googlesheets4 will, by default, help you interact with Sheets as an authenticated Google user. If you don’t plan to write Sheets or to read private Sheets, use gs4_deauth() to indicate there is no need for a token. See the article googlesheets4 auth for more.

For this overview, we’ve logged into Google as a specific user in a hidden chunk.

Attach googlesheets4

library(googlesheets4)

Read

The main β€œread” function of the googlesheets4 package goes by two names, because we want it to make sense in two contexts:

  • read_sheet() evokes other table-reading functions, like readr::read_csv() and readxl::read_excel(). The sheet in this case refers to a Google (spread)Sheet.
  • range_read() is the right name according to the naming convention used throughout the googlesheets4 package.

read_sheet() and range_read() are synonyms and you can use either one. Here we’ll use read_sheet().

googlesheets4 is pipe-friendly (and reexports %>%), but works just fine without the pipe.

Read from

  • a URL
  • a Sheet ID
  • a dribble produced by the googledrive package, which can lookup by file name

These all achieve the same thing:

# URL
read_sheet("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077")
#> βœ” Reading from "gapminder".
#> βœ” Range 'Africa'.
#> # A tibble: 624 Γ— 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.
#> # β„Ή 619 more rows

# Sheet ID
read_sheet("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY")
#> βœ” Reading from "gapminder".
#> βœ” Range 'Africa'.
#> # A tibble: 624 Γ— 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.
#> # β„Ή 619 more rows

# a googledrive "dribble"
googledrive::drive_get("gapminder") %>% 
  read_sheet()
#> βœ” The input `path` resolved to exactly 1 file.
#> βœ” Reading from "gapminder".
#> βœ” Range 'Africa'.
#> # A tibble: 624 Γ— 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.
#> # β„Ή 619 more rows

Note: the only reason we can read a sheet named β€œgapminder” (the last example) is because the account we’re logged in as has a Sheet named β€œgapminder”.

See the article Find and Identify Sheets for more about specifying the Sheet you want to address. See the article Read Sheets for more about reading from specific sheets or ranges, setting column type, and getting low-level cell data.

Write

gs4_create() creates a brand new Google Sheet and can optionally send some initial data.

(ss <- gs4_create("fluffy-bunny", sheets = list(flowers = head(iris))))
#> βœ” Creating new Sheet: "fluffy-bunny".
#> 
#> ── <googlesheets4_spreadsheet> ─────────────────────────────────────────────────
#> Spreadsheet name: "fluffy-bunny"                              
#>               ID: 1enILX4tYJeFEJ1RL8MsGgDRjb0NHTdm3ZD92R2RMWYI
#>           Locale: en_US                                       
#>        Time zone: Etc/GMT                                     
#>      # of sheets: 1                                           
#> 
#> ── <sheets> ────────────────────────────────────────────────────────────────────
#> (Sheet name): (Nominal extent in rows x columns)
#>    'flowers': 7 x 5

sheet_write() (over)writes a whole data frame into a (work)sheet within a (spread)Sheet.

head(mtcars) %>% 
  sheet_write(ss, sheet = "autos")
#> βœ” Writing to "fluffy-bunny".
#> βœ” Writing to sheet 'autos'.
ss
#> 
#> ── <googlesheets4_spreadsheet> ─────────────────────────────────────────────────
#> Spreadsheet name: "fluffy-bunny"                              
#>               ID: 1enILX4tYJeFEJ1RL8MsGgDRjb0NHTdm3ZD92R2RMWYI
#>           Locale: en_US                                       
#>        Time zone: Etc/GMT                                     
#>      # of sheets: 2                                           
#> 
#> ── <sheets> ────────────────────────────────────────────────────────────────────
#> (Sheet name): (Nominal extent in rows x columns)
#>    'flowers': 7 x 5
#>      'autos': 7 x 11

sheet_append(), range_write(), range_flood(), and range_clear() are more specialized writing functions. See the article Write Sheets for more about writing to Sheets.

Where to learn more

Get started is a more extensive general introduction to googlesheets4.

Browse the articles index to find articles that cover various topics in more depth.

See the function index for an organized, exhaustive listing.

Contributing

If you’d like to contribute to the development of googlesheets4, please read these guidelines.

Please note that the googlesheets4 project is released with a Contributor Code of Conduct. By contributing to this project, you agree to abide by its terms.

Privacy

Privacy policy

Context

googlesheets4 draws on and complements / emulates other packages in the tidyverse:

  • googlesheets is the package that googlesheets4 replaces. Main improvements in googlesheets4: (1) wraps the current, most modern Sheets API; (2) leaves all β€œwhole file” operations to googledrive; and (3) uses shared infrastructure for auth and more, from the gargle package. The v3 API wrapped by googlesheets is deprecated. Starting in April/May 2020, features will gradually be disabled and it’s anticipated the API will fully shutdown in September 2020. At that point, the original googlesheets package must be retired.
  • googledrive provides a fully-featured interface to the Google Drive API. Any β€œwhole file” operations can be accomplished with googledrive: upload or download or update a spreadsheet, copy, rename, move, change permission, delete, etc. googledrive supports Team Drives.
  • readxl is the tidyverse package for reading Excel files (xls or xlsx) into an R data frame. googlesheets4 takes cues from parts of the readxl interface, especially around specifying which cells to read.
  • readr is the tidyverse package for reading delimited files (e.g., csv or tsv) into an R data frame. googlesheets4 takes cues from readr with respect to column type specification.

More Repositories

1

ggplot2

An implementation of the Grammar of Graphics in R
R
6,311
star
2

dplyr

dplyr: A grammar of data manipulation
R
4,627
star
3

tidyverse

Easily install and load packages from the tidyverse
R
1,575
star
4

rvest

Simple web scraping for R
R
1,455
star
5

tidyr

Tidy Messy Data
R
1,323
star
6

purrr

A functional programming toolkit for R
R
1,211
star
7

readr

Read flat files (csv, tsv, fwf) into R
R
985
star
8

magrittr

Improve the readability of R code with the pipe
R
952
star
9

datascience-box

Data Science Course in a Box
JavaScript
895
star
10

reprex

Render bits of R code for sharing, e.g., on GitHub or StackOverflow.
R
726
star
11

readxl

Read excel files (.xls and .xlsx) into R πŸ–‡
C++
713
star
12

lubridate

Make working with dates in R just that little bit easier
R
712
star
13

glue

Glue strings to data in R. Small, fast, dependency free interpreted string literals.
R
685
star
14

dtplyr

Data table backend for dplyr
R
656
star
15

tibble

A modern re-imagining of the data frame
R
641
star
16

multidplyr

A dplyr backend that partitions a data frame over multiple processes
R
636
star
17

vroom

Fast reading of delimited files
C++
604
star
18

stringr

A fresh approach to string manipulation in R
R
565
star
19

forcats

🐈🐈🐈🐈: tools for working with categorical variables (factors)
R
533
star
20

dbplyr

Database (DBI) backend for dplyr
R
455
star
21

haven

Read SPSS, Stata and SAS files from R
C
415
star
22

modelr

Helper functions for modelling
R
398
star
23

googledrive

Google Drive R API
R
312
star
24

style

The tidyverse style guide for R code
HTML
285
star
25

design

Tidyverse design principles
R
208
star
26

tidyverse.org

Source of tidyverse.org
HTML
186
star
27

hms

A simple class for storing time-of-day values
R
136
star
28

nycflights13

An R data package containing all out-bound flights from NYC in 2013 + useful metdata
R
121
star
29

tidyversedashboard

Tidyverse activity dashboard
R
71
star
30

tidy-dev-day

Tidyverse developer day
59
star
31

tidyeval

A guide to tidy evaluation
CSS
54
star
32

dsbox

Companion R package to Data Science Course in a Box
R
47
star
33

tidytemplate

A pkgdown template for core tidyverse packages
SCSS
45
star
34

blob

A simple S3 class for representing BLOBs
R
44
star
35

code-review

32
star
36

funs

Collection of low-level functions for working with vctrs
R
31
star
37

website-analytics

Web analytics for tidyverse + r-lib sites
R
28
star
38

tidyups

20
star
39

ggplot2-docs

ggplot2 documentation. Auto-generated from ggplot2 sources by pkgdown
HTML
10
star