googlesheets4

While googlesheets4 handles the content inside spreadsheets (reading/writing cells and ranges), it relies on the googledrive package for "whole file" operations.

Use googledrive for:

• Uploading or downloading spreadsheets.
• Copying, renaming, or moving files.
• Changing file permissions.
• Deleting files.
• Managing Team Drives.

googlesheets4 is a reboot of the older googlesheets package, updated to use the modern Sheets API v4 and shared authentication infrastructure via the gargle package.

You can install the released version of googlesheets4 from CRAN or the development version from GitHub.

From CRAN:

install.packages("googlesheets4")

From GitHub (using pak):

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

By default, googlesheets4 interacts with Sheets as an authenticated Google user. If you only need to read public sheets or do not intend to write to any sheets, you can bypass authentication using gs4_deauth() to avoid needing a token.

For more complex authentication workflows, refer to the googlesheets4 auth article.

When using googlesheets4, an HTTP 404 error typically indicates that the spreadsheetId provided does not exist or is inaccessible. The error response from the Google Sheets API will contain a specific structure identifying the failure:

• code: 404
• message: 'Requested entity was not found.'
• status: 'NOT_FOUND'

This error is often triggered when a spreadsheetId is malformed (e.g., incorrect characters at the end of the ID) or refers to a sheet that has been deleted.

# Example of how the error structure appears in the response content
# (Assuming raw_resp is the response from a failed request)
ct <- httr::content(raw_resp)
str(ct)

# Expected output structure:
# List of 1
#  $ error:List of 3
#   ..$ code   : int 404
#   ..$ message: chr "Requested entity was not found."
#   ..$ status : chr "NOT_FOUND"

The sheet_write() function overwrites an entire worksheet within a spreadsheet with a provided data frame.

Example:

# Assuming 'ss' is a spreadsheet object from gs4_create()
head(mtcars) %>%
  sheet_write(ss, sheet = "autos")

Other specialized writing functions include:

• sheet_append()
• range_write()
• range_flood()
• range_clear()

Use gs4_create() to create a brand new Google Sheet. You can optionally provide a named list to the sheets argument to populate the new spreadsheet with initial data immediately upon creation.

Example:

(ss <- gs4_create("fluffy-bunny", sheets = list(flowers = head(iris))))

This returns a <googlesheets4_spreadsheet> object which can be used for subsequent operations.

The primary function for reading data is read_sheet() (which is a synonym for range_read()). It is pipe-friendly and can accept several types of input to identify a spreadsheet:

• A URL: The full web address of the Google Sheet.
• A Sheet ID: The unique identifier string found in the Sheet's URL.
• A googledrive dribble: An object produced by the googledrive package, allowing lookup by file name.

read_sheet() is designed to be consistent with other tidyverse reading functions like readr::read_csv().

# URL
read_sheet("https://docs.google.com/spreadsheets/d/1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY/edit#gid=780868077")

# Sheet ID
read_sheet("1U6Cf_qEOhiR9AZqTqS3mbMF3zt2db48ZP5v3rkrAEJY")

# a googledrive "dribble"
googledrive::drive_get("gapminder") %>%
  read_sheet()