-
Notifications
You must be signed in to change notification settings - Fork 51
/
googlesheets4.Rmd
161 lines (108 loc) · 5.86 KB
/
googlesheets4.Rmd
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
---
title: "Get started with googlesheets4"
---
```{r setup, include = FALSE}
can_decrypt <- gargle::secret_has_key("GOOGLESHEETS4_KEY")
knitr::opts_chunk$set(
collapse = TRUE,
comment = "#>",
error = TRUE,
purl = can_decrypt,
eval = can_decrypt
)
options(tibble.print_min = 5L, tibble.print_max = 5L)
```
```{r eval = !can_decrypt, echo = FALSE, comment = NA}
message("No token available. Code chunks will not be evaluated.")
```
```{r index-auth, include = FALSE}
googlesheets4:::gs4_auth_docs()
```
```{r}
library(googlesheets4)
```
This article takes a quick tour of the main features of googlesheets4.
Remember to see [the articles](https://googlesheets4.tidyverse.org/articles/index.html) for more detailed treatment of all these topics and more.
## `read_sheet()`, a.k.a. `range_read()`
`read_sheet()` is the main "read" function and should evoke `readr::read_csv()` and `readxl::read_excel()`. It's an alias for `range_read()`, which is the correct name for this function according to the scheme for naming googlesheets4 functions. You can use them interchangeably. 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:
```{r}
read_sheet("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077")
```
However, these URLs are not pleasant to work with. More often, you will want to identify a Sheet by its ID:
```{r}
read_sheet("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY")
```
or by its name, which requires an assist from the googledrive package ([googledrive.tidyverse.org](https://googledrive.tidyverse.org)):
<!-- remove the 'message = i' later -->
```{r message=4}
library(googledrive)
drive_get("gapminder") %>%
read_sheet()
```
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](https://googlesheets4.tidyverse.org/articles/articles/find-identify-sheets.html). See the article [Read Sheets](https://googlesheets4.tidyverse.org/articles/articles/read-sheets.html) for more about reading from a specific (work)sheet or ranges, setting column type, and getting low-level cell data.
## Example Sheets and `gs4_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.
* `gs4_examples()` lists all the example Sheets and it can also filter by
matching names to a regular expression.
* `gs4_example()` requires a regular expression and returns exactly 1 Sheet
ID (or throws an error).
```{r}
gs4_example("chicken-sheet") %>%
read_sheet()
```
If you'd like to see a Sheet in the browser, including our example Sheets, use `gs4_browse()`:
```{r}
gs4_example("deaths") %>%
gs4_browse()
```
## Sheet metadata
`gs4_get()` exposes Sheet metadata, such as details on worksheets and named ranges.
```{r}
ss <- gs4_example("deaths")
gs4_get(ss)
sheet_properties(ss)
sheet_names(ss)
```
`sheet_properties()` and `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 `gs4_get()` is also revealed whenever you print an object that is (or can be converted to) a `sheets_id` (an S3 class we use to mark Sheet IDs).
`gs4_get()` is related to `googledrive::drive_get()`. Both functions return metadata about a file on Google Drive, such as its ID and name. However, `gs4_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 may still see some refinements re: user interface and which function does what. We're very interested to hear how these functions feel in terms of ergonomics.*
`sheet_write()` writes a data frame into a Sheet. The only required argument is the data.
```{r}
df <- data.frame(x = 1:3, y = letters[1:3])
ss <- sheet_write(df)
ss
```
You'll notice the new (spread)Sheet has a randomly generated name. If that is a problem, use `gs4_create()` instead, which affords more control over various aspects of the new Sheet.
Let's start over: we delete that Sheet and call `gs4_create()`, so we can specify the new Sheet's name.
```{r}
googledrive::drive_trash(ss)
ss <- gs4_create("testy-hedgehog", sheets = df)
ss
```
`sheet_write()` can write to new or existing (work)sheets in this Sheet. Let's write the `chickwts` data to a new sheet in `ss`.
```{r}
sheet_write(chickwts, ss)
ss
```
We can also use `sheet_write()` to replace the data in an existing sheet.
```{r}
sheet_write(data.frame(x = 4:10, letters[4:10]), ss, sheet = "df")
read_sheet(ss, sheet = "df")
```
`sheet_append()` adds one or more rows to an existing sheet.
```{r}
ss %>% sheet_append(data.frame(x = 11, letters[11]), sheet = "df")
read_sheet(ss, sheet = "df")
```
A related function -- `range_write()` -- writes arbitrary data, into an arbitrary range. It has a very different "feel" from `gs4_create()`, `sheet_write()`, and `sheet_append()`, all of which assume we're writing or growing a table of data in a (work)sheet. `range_write()` is much more surgical and limited. `range_write()` makes fewer assumptions about what it's writing and why.
There is also a family of `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.
```{r}
sheet_properties(ss)
googledrive::drive_trash(ss)
```
The article [Write Sheets](https://googlesheets4.tidyverse.org/articles/articles/write-sheets.html) has even more detail.