library(googlesheets4)

Basic Sheet writing is shown in the Get started article. Here we explore the different uses for the writing functions:

  • sheets_create() emphasizes the creation of a new (spread)Sheet.
    • But it can create (work)sheets and write data.
  • sheets_write() emphasizes writing a data frame into a (work)sheet.
    • But it can create a new (spread)Sheet or a new (work)sheet.
  • sheets_append() is about adding rows to an existing data table.
  • sheets_create(), sheets_write(), and sheets_append() implement holistic operations for representing a single R data frame as a table in a (work)sheet within a Google Sheet.
    • sheets_create() and sheets_write() both impose this mentality via specific formatting, such as special treatment of the column header row.
    • All 3 functions aim to shrink-wrap the data.
  • sheets_edit() is the one function that helps you write arbitrary data into an arbitrary range, although it is still oriented around a data frame.

Auth

As a regular, interactive user, you can just let googlesheets4 prompt you for anything it needs re: auth.

Since this article is compiled noninteractively on a server, we have arranged for googlesheets4 to use a service account token (not shown).

sheets_create()

Create a brand new Sheet with sheets_create(). You can specify the new Sheet’s name (or accept a randomly generated name).

ss1 <- sheets_create("sheets-create-demo-1")
ss1
#>   Spreadsheet name: sheets-create-demo-1
#>                 ID: 1JA3BwoI6KYynVuwu1G2h9DWgENrftg48B7Z_hoTmc68
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       Sheet1: 1000 x 26

Every Sheet must have at least one (work)sheet, so Google Sheets automatically creates an empty “Sheet1”.

You can control the names and content of the initial (work)sheets with the sheets argument.

Send sheet names

Use a character vector to specify the names of one or more empty sheets.

ss2 <- sheets_create(
  "sheets-create-demo-2",
  sheets = c("alpha", "beta")
)
ss2
#>   Spreadsheet name: sheets-create-demo-2
#>                 ID: 1PuCuGm6qvZ_9jKP0BUtgO4nPtchzaDEccgiLpUAS51Y
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 2
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>        alpha: 1000 x 26
#>         beta: 1000 x 26

These sheets have no values and get their dimensions from Sheets default behaviour.

Send a data frame

If you provide a data frame, it is used to populate the cells of a sheet and to set sheet dimensions (number of rows and columns). The header row also gets special treatment. The sheet inherits the name of the data frame, where possible.

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

ss3 <- sheets_create(
  "sheets-create-demo-3",
  sheets = my_data
)
ss3
#>   Spreadsheet name: sheets-create-demo-3
#>                 ID: 1roKlr2FW9SSvkUjI24BK0BOmp33KxEtymqSd-A_3Pw4
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>      my_data: 4 x 2

Send multiple data frames

If you provide a list of data frames, each is used to populate the cells of one sheet and to set sheet dimensions (number of rows and columns). The header row also gets special treatment. The sheets inherit the names from the list, if it has names.

my_data_frames <- list(iris = head(iris), chickwts = head(chickwts))

ss4 <- sheets_create(
  "sheets-create-demo-4",
  sheets = my_data_frames
)
ss4
#>   Spreadsheet name: sheets-create-demo-4
#>                 ID: 1tCH-mbKoLLCpuMCnHA_g-_oxtXRqUI-ygisp4kqGXqA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 2
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>         iris: 7 x 5
#>     chickwts: 7 x 2

Write metadata

Most users won’t need to do this, but sheets_create() can set additional Sheet-level metadata, such as locale or time zone. To really make use of this feature, you need to read up on the spreadsheets.create endpoint.

Notice how the default empty Sheet here is named “Feuille 1”, since we request a French locale.

ss5 <- sheets_create(
  "sheets-create-demo-5",
  locale = "fr_FR",
  timeZone = "Europe/Paris"
)
ss5
#>   Spreadsheet name: sheets-create-demo-5
#>                 ID: 1RP_QFf-kgMdvHKOrpBQbjZpi2R70YYUe6lK4tz_vtDQ
#>             Locale: fr_FR
#>          Time zone: Europe/Paris
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>    Feuille 1: 1000 x 26

I would only do this if you have specific evidence that the default behaviour with respect to locale and time zone is problematic for your use case.

Clean up

Trash all the Sheets created above. This actually requires googledrive, since it is not possible to trash or delete Sheets through the Sheets API. In our hidden auth process, described earlier, we put a shared token into force for both Sheets and Drive. You can read how to do that in your own work in the article Using googlesheets4 with googledrive.

sheets_find("sheets-create-demo") %>%
  googledrive::drive_trash()
#> Files trashed:
#>   * sheets-create-demo-5: 1RP_QFf-kgMdvHKOrpBQbjZpi2R70YYUe6lK4tz_vtDQ
#>   * sheets-create-demo-4: 1tCH-mbKoLLCpuMCnHA_g-_oxtXRqUI-ygisp4kqGXqA
#>   * sheets-create-demo-3: 1roKlr2FW9SSvkUjI24BK0BOmp33KxEtymqSd-A_3Pw4
#>   * sheets-create-demo-2: 1PuCuGm6qvZ_9jKP0BUtgO4nPtchzaDEccgiLpUAS51Y
#>   * sheets-create-demo-1: 1JA3BwoI6KYynVuwu1G2h9DWgENrftg48B7Z_hoTmc68

write_sheet(), a.k.a. sheets_write()

write_sheet() is aliased to sheets_write() and is meant to evoke readr::write_csv() or writexl::write_xlsx(). Whereas sheets_create() emphasizes the target Sheet, sheets_write() emphasizes the data you want to write.

The only required argument for sheets_write() is the data.

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

random_ss <- sheets_write(df)
random_ss
#>   Spreadsheet name: pestilent-ladybird
#>                 ID: 1Rm3tV8yub3qyRbCY8qJi6egNFQqSWNTfO1U43PD9mdo
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>           df: 4 x 2

This creates a new (work)sheet inside a new (spread)Sheet and returns its ID. You’ll notice the new 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. Then we’ll modify it with sheets_write(). We send one sheet name, “chickwts”, to prevent the creation of “Sheet1”, but we send no data.

googledrive::drive_rm(random_ss)
#> Files deleted:
#>   * pestilent-ladybird: 1Rm3tV8yub3qyRbCY8qJi6egNFQqSWNTfO1U43PD9mdo

ss1 <- sheets_create(
  "write-sheets-demo-1",
  sheets = "chickwts"
)
ss1
#>   Spreadsheet name: write-sheets-demo-1
#>                 ID: 1KBD3ZCVdu65Xj8C6BICBOc4TyYWig7KGf-5yjWh3xoA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>     chickwts: 1000 x 26

sheets_write() allows us to write the actual chickwts data into the sheet by that name.

sheets_write(chickwts, ss = ss1, sheet = "chickwts")
#> Writing to "write-sheets-demo-1"
#> Writing to sheet "chickwts"
ss1
#>   Spreadsheet name: write-sheets-demo-1
#>                 ID: 1KBD3ZCVdu65Xj8C6BICBOc4TyYWig7KGf-5yjWh3xoA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>     chickwts: 72 x 2

sheets_write() can also write data into a new sheet, if sheet implicitly or explicitly targets a non-existent sheet.

# explicitly make a new sheet named "iris"
sheets_write(iris, ss = ss1, sheet = "iris")
#> Writing to "write-sheets-demo-1"
#> Writing to sheet "iris"

# implicitly make a new sheet named "mtcars"
sheets_write(mtcars, ss = ss1)
#> Writing to "write-sheets-demo-1"
#> Writing to sheet "mtcars"

If no sheet name is given and it can’t be determined from data, a name of the form “SheetN” is automatically generated by Sheets.

sheets_write(data.frame(x = 1:2, y = 3:4), ss = ss1)
#> Writing to "write-sheets-demo-1"
#> Writing to sheet "Sheet1"
ss1
#>   Spreadsheet name: write-sheets-demo-1
#>                 ID: 1KBD3ZCVdu65Xj8C6BICBOc4TyYWig7KGf-5yjWh3xoA
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 4
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>     chickwts: 72 x 2
#>         iris: 151 x 5
#>       mtcars: 33 x 11
#>       Sheet1: 3 x 2

Clean up

sheets_find("write-sheets-demo") %>%
  googledrive::drive_trash()
#> Files trashed:
#>   * write-sheets-demo-1: 1KBD3ZCVdu65Xj8C6BICBOc4TyYWig7KGf-5yjWh3xoA

sheets_append()

sheets_append() can add one or more rows of data to an existing (work)sheet.

Let’s recreate the table of “other” deaths from an example Sheet, but without the annoying text above and below the data. First we bring that data into a local data frame and chop it into pieces.

(deaths <- sheets_example("deaths") %>%
   sheets_read(range = "other_data", col_types = "????DD"))
#> Reading from "deaths"
#> Range "other_data"
#> # A tibble: 10 x 6
#>    Name           Profession   Age `Has kids` `Date of birth` `Date of death`
#>    <chr>          <chr>      <dbl> <lgl>      <date>          <date>         
#>  1 Vera Rubin     scientist     88 TRUE       1928-07-23      2016-12-25     
#>  2 Mohamed Ali    athlete       74 TRUE       1942-01-17      2016-06-03     
#>  3 Morley Safer   journalist    84 TRUE       1931-11-08      2016-05-19     
#>  4 Fidel Castro   politician    90 TRUE       1926-08-13      2016-11-25     
#>  5 Antonin Scalia lawyer        79 TRUE       1936-03-11      2016-02-13     
#>  6 Jo Cox         politician    41 TRUE       1974-06-22      2016-06-16     
#>  7 Janet Reno     lawyer        78 FALSE      1938-07-21      2016-11-07     
#>  8 Gwen Ifill     journalist    61 FALSE      1955-09-29      2016-11-14     
#>  9 John Glenn     astronaut     95 TRUE       1921-07-28      2016-12-08     
#> 10 Pat Summit     coach         64 TRUE       1952-06-14      2016-06-28

deaths_zero  <- deaths[integer(), ] # "scaffolding" data frame with 0 rows
deaths_one   <- deaths[1:5, ] 
deaths_two   <- deaths[6, ]
deaths_three <- deaths[7:10, ]

We use sheets_create() to create a new (spread)Sheet and initialize a new (work)sheet with the “deaths” column headers, but no data. A second empty row is created (it must be, in order for us to freeze the top row), but it will soon be filled when we append.

ss <- sheets_create("sheets-append-demo", sheets = list(deaths = deaths_zero))
ss
#>   Spreadsheet name: sheets-append-demo
#>                 ID: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       deaths: 2 x 6

If you’re following along, I recommend you open this Sheet in a web browser with sheets_browse() and revisit as we go along, to see how the initial empty row gets consumed and how additional rows are added to the targetted sheet automatically.

Send the data, one or more rows at a time. Keep inspecting in the browser if you’re doing this yourself.

ss
#>   Spreadsheet name: sheets-append-demo
#>                 ID: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       deaths: 2 x 6

# send several rows
sheets_append(deaths_one, ss)
#> Writing to "sheets-append-demo"
#> Appending 5 row(s) to "deaths"
ss
#>   Spreadsheet name: sheets-append-demo
#>                 ID: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       deaths: 6 x 6

# send a single row
sheets_append(deaths_two, ss)
#> Writing to "sheets-append-demo"
#> Appending 1 row(s) to "deaths"
ss
#>   Spreadsheet name: sheets-append-demo
#>                 ID: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       deaths: 7 x 6

# send remaining rows
sheets_append(deaths_three, ss)
#> Writing to "sheets-append-demo"
#> Appending 4 row(s) to "deaths"
ss
#>   Spreadsheet name: sheets-append-demo
#>                 ID: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM
#>             Locale: en_US
#>          Time zone: Etc/GMT
#>        # of sheets: 1
#> 
#> (Sheet name): (Nominal extent in rows x columns)
#>       deaths: 11 x 6

Now the big reveal: have we successfully rebuilt that data through incremental updates?

deaths_replica <- sheets_read(ss, col_types = "????DD")
#> Reading from "sheets-append-demo"
#> Range "deaths"
identical(deaths, deaths_replica)
#> [1] TRUE

Gosh I hope that’s still TRUE as it was the last time I checked this article!

Clean up

sheets_find("sheets-append-demo") %>%
  googledrive::drive_trash()
#> Files trashed:
#>   * sheets-append-demo: 1OMMuYZe1ZVU2swVolJGbzpre_zumpy1rPsvIr9V1EWM

sheets_edit()

sheets_edit() is the least opinionated writing function. It writes data into a range. It does no explicit formatting, although it can effectively apply formatting by clearing existing formats via reformat = TRUE (the default).

We focus here on the geometry of sheets_edit(), i.e. which cells are edited.

In a hidden chunk, we’ve created a demo Sheet ss_edit and we’ve filled the cells with “-”. We’ve also created read_this(), a wrapper around sheets_read() that sets col_names = FALSE and names columns by letter.

Here’s the initial state of ss_edit:

read_this(ss_edit)
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 -     -     -     -     -     -     -    
#> 2 -     -     -     -     -     -     -    
#> 3 -     -     -     -     -     -     -    
#> 4 -     -     -     -     -     -     -    
#> 5 -     -     -     -     -     -     -    
#> 6 -     -     -     -     -     -     -    
#> 7 -     -     -     -     -     -     -

df is a small data frame we’ll send as the data argument of sheets_edit():

(df <- tibble(V1 = head(LETTERS,3), V2 = tail(LETTERS, 3)))
#> # A tibble: 3 x 2
#>   V1    V2   
#>   <chr> <chr>
#> 1 A     X    
#> 2 B     Y    
#> 3 C     Z

If we do not specify the range, df is written into the upper left corner and only affects cells spanned by df. To see where we’ve written, focus on the cells are NOT “x”.

sheets_edit(ss_edit, data = df) %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 V1    V2    -     -     -     -     -    
#> 2 A     X     -     -     -     -     -    
#> 3 B     Y     -     -     -     -     -    
#> 4 C     Z     -     -     -     -     -    
#> 5 -     -     -     -     -     -     -    
#> 6 -     -     -     -     -     -     -    
#> 7 -     -     -     -     -     -     -

(Here, and between all subsequent chunks, we reset ss_edit to its initial state.)

If we target a single cell with range, it specifies the upper left corner of the target area. The cells written are determined by the extent of the data.

sheets_edit(ss_edit, data = df, range = "C2") %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 -     -     -     -     -     -     -    
#> 2 -     -     V1    V2    -     -     -    
#> 3 -     -     A     X     -     -     -    
#> 4 -     -     B     Y     -     -     -    
#> 5 -     -     C     Z     -     -     -    
#> 6 -     -     -     -     -     -     -    
#> 7 -     -     -     -     -     -     -

If range specifies multiple cells (it can even be unbounded on some sides), it is taken literally and all covered cells are written. If range is larger than the data, this results in some cells being cleared of their values. In this example, the range is “too big” for the data, so the remaining cells are cleared of their existing “-” value.

sheets_edit(ss_edit, data = df, range = "D4:G7") %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 -     -     -     -     -     -     -    
#> 2 -     -     -     -     -     -     -    
#> 3 -     -     -     -     -     -     -    
#> 4 -     -     -     V1    V2    <NA>  <NA> 
#> 5 -     -     -     A     X     <NA>  <NA> 
#> 6 -     -     -     B     Y     <NA>  <NA> 
#> 7 -     -     -     C     Z     <NA>  <NA>

Here’s another case where the range is bigger than it needs to be and it’s unbounded on the bottom and top:

sheets_edit(ss_edit, data = df, range = "B:E") %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <lgl> <lgl> <chr> <chr>
#> 1 -     V1    V2    NA    NA    -     -    
#> 2 -     A     X     NA    NA    -     -    
#> 3 -     B     Y     NA    NA    -     -    
#> 4 -     C     Z     NA    NA    -     -    
#> 5 -     <NA>  <NA>  NA    NA    -     -    
#> 6 -     <NA>  <NA>  NA    NA    -     -    
#> 7 -     <NA>  <NA>  NA    NA    -     -

Here’s another range that’s unbounded on the left and “too big”:

sheets_edit(ss_edit, data = df, range = "B2:6") %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 7 x 7
#>   A     B     C     D     E     F     G    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 -     -     -     -     -     -     -    
#> 2 -     V1    V2    <NA>  <NA>  <NA>  <NA> 
#> 3 -     A     X     <NA>  <NA>  <NA>  <NA> 
#> 4 -     B     Y     <NA>  <NA>  <NA>  <NA> 
#> 5 -     C     Z     <NA>  <NA>  <NA>  <NA> 
#> 6 -     <NA>  <NA>  <NA>  <NA>  <NA>  <NA> 
#> 7 -     -     -     -     -     -     -

The target sheet will be expanded, if necessary, if and only if range is a single cell (i.e. it gives the upper left corner).

sheets_edit(ss_edit, data = df, range = "G6") %>% read_this()
#> Editing "sheets-edit-demo"
#> Writing to sheet "Sheet1"
#> Changing dims: (7 x 7) --> (9 x 8)
#> Reading from "sheets-edit-demo"
#> Range "Sheet1"
#> # A tibble: 9 x 8
#>   A     B     C     D     E     F     G     H    
#>   <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 -     -     -     -     -     -     -     <NA> 
#> 2 -     -     -     -     -     -     -     <NA> 
#> 3 -     -     -     -     -     -     -     <NA> 
#> 4 -     -     -     -     -     -     -     <NA> 
#> 5 -     -     -     -     -     -     -     <NA> 
#> 6 -     -     -     -     -     -     V1    V2   
#> 7 -     -     -     -     -     -     A     X    
#> 8 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  B     Y    
#> 9 <NA>  <NA>  <NA>  <NA>  <NA>  <NA>  C     Z

Although the data argument of sheets_edit() must be a data frame, note that this does not actually limit what you can write:

  • Use col_names = FALSE to suppress sending the column names.
  • By definition, each variable of a data frame can be of different type.
  • By using a list-column, each row of a data frame column can be of different type.

The examples for sheets_edit() show writing data of disparate type to a 1-row or a 1-column region.

Clean up

sheets_find("sheets-edit-demo") %>%
  googledrive::drive_trash()
#> Files trashed:
#>   * sheets-edit-demo: 1i7FEpiAO45BibPd64KHbKBf9GQxljDjWut78UtiY7Jo