Data Read/Write with {googlesheets4}

The googlesheets4 is a package designed to facilitate the integration of Google Sheets with the R programming language. This package provides an R interface to Google Sheets via the Sheets API v4, which allows users to programmatically access, manipulate, and interact with Google Sheets data from R.

The package is an updated and improved version of the earlier package called googlesheets, which was developed to provide similar functionality but using the now-deprecated Sheets API v3. googlesheets4 offers a range of features, including the ability to create, read, update, and delete Sheets, as well as to manage Sheets metadata, share Sheets with other users, and perform batch operations on Sheets data. Overall, googlesheets4 is a powerful and flexible tool for working with Google Sheets data in R.

Check and Install Required R Packages

Code

packages <- c(
          'googlesheets4',
          'googledrive',
          'tidyverse'
)

#| warning: false
#| error: false

# Install missing packages
new_packages <- packages[!(packages %in% installed.packages()[,"Package"])]
if(length(new_packages)) install.packages(new_packages)

Verify Installation

Code

# Verify installation
cat("Installed packages:\n")

Installed packages:

Code

print(sapply(packages, requireNamespace, quietly = TRUE))

googlesheets4   googledrive     tidyverse 
         TRUE          TRUE          TRUE

Load R-packages

Code

# Load packages with suppressed messages
invisible(lapply(packages, function(pkg) {
  suppressPackageStartupMessages(library(pkg, character.only = TRUE))
}))

Check Loaded Packages

Code

# Check loaded packages
cat("Successfully loaded packages:\n")

Successfully loaded packages:

Code

print(search()[grepl("package:", search())])

 [1] "package:lubridate"     "package:forcats"       "package:stringr"      
 [4] "package:dplyr"         "package:purrr"         "package:readr"        
 [7] "package:tidyr"         "package:tibble"        "package:ggplot2"      
[10] "package:tidyverse"     "package:googledrive"   "package:googlesheets4"
[13] "package:stats"         "package:graphics"      "package:grDevices"    
[16] "package:utils"         "package:datasets"      "package:methods"      
[19] "package:base"

Authenticate with Google Sheets

By default, googlesheets4 will help you interact with Sheets, provided you are authenticated as a Google user. The chunk below asks for both Sheets and Drive access in one OAuth flow, then passes that token into googlesheets4. If {googledrive} is used separately with a cached token that lacks the Sheets scope, read_sheet() can fail with ACCESS_TOKEN_SCOPE_INSUFFICIENT.

If OAuth is not possible (for example Quarto renders without a browser), the fallback uses gs4_deauth() so a world-readable spreadsheet can still be read via the Sheets API without a token. sheet_write() / gs4_create() require a signed-in user, so those steps are skipped when OAuth does not succeed. For auth details see Google Sheets4 Auth in the googlesheets4 docs.

Code

can_write_gs <- tryCatch({
  googledrive::drive_auth(
    email = "zia207@gmail.com",
    scopes = c(
      "https://www.googleapis.com/auth/spreadsheets",
      "https://www.googleapis.com/auth/drive"
    )
  )
  googlesheets4::gs4_auth(token = googledrive::drive_token())
  TRUE
}, error = function(e) {
  googlesheets4::gs4_deauth()
  FALSE
})

Data

All data set use in this exercise can be downloaded from my from my Github accounts.

Reading a Google sheet file

read_sheet() is the main “read” function and should evoke readr::read_csv() and readxl::read_excel(). It is designed to “just work”, for most purposes, most of the time. It can read straight from a Sheets browser URL.

14E1oFFSiQ19Qpij-oa4vuDqwGWU80LLaYmSJSPZNfFM is the ID of test_data.gsheet in my GoogleDrive

You can load data directly from my GoogleDrive data folder using following code with ID:

Code

df.sheet <- read_sheet("https://docs.google.com/spreadsheets/d/14E1oFFSiQ19Qpij-oa4vuDqwGWU80LLaYmSJSPZNfFM/edit?usp=sharing") |> 
  glimpse()

Rows: 42
Columns: 13
$ ID    <dbl> 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 1…
$ treat <chr> "Low As", "Low As", "Low As", "Low As", "Low As", "Low As", "Low…
$ var   <chr> "BR01", "BR01", "BR01", "BR06", "BR06", "BR06", "BR28", "BR28", …
$ rep   <dbl> 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1, 2, 3, 1…
$ PH    <dbl> 84.0, 111.7, 102.3, 118.0, 115.3, 111.0, 114.3, 124.0, 120.3, 13…
$ TN    <dbl> 28.3, 34.0, 27.7, 23.3, 16.7, 19.0, 21.7, 25.3, 23.0, 19.7, 21.0…
$ PN    <dbl> 27.7, 30.0, 24.0, 19.7, 12.3, 15.3, 19.3, 21.0, 19.0, 14.7, 16.3…
$ GW    <dbl> 35.7, 58.1, 44.6, 46.4, 19.9, 35.9, 56.2, 49.2, 48.6, 36.6, 39.9…
$ ster  <dbl> 20.5, 14.8, 5.8, 20.3, 32.3, 14.9, 6.1, 9.2, 4.2, 12.1, 11.5, 8.…
$ DTM   <dbl> 126.0, 119.0, 119.7, 119.0, 120.0, 116.3, 123.7, 114.3, 113.3, 1…
$ SW    <dbl> 28.4, 36.7, 32.9, 40.0, 28.2, 42.3, 35.4, 60.6, 69.8, 57.3, 53.0…
$ GAs   <dbl> 0.762, 0.722, 0.858, 1.053, 1.130, 1.011, 0.965, 0.969, 0.893, 1…
$ STAs  <dbl> 14.60, 10.77, 12.69, 18.23, 13.72, 15.97, 14.49, 16.02, 15.25, 2…

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.

Code

if (can_write_gs) {
  sheet <- sheet_write(df.sheet)
} else {
  message("Skipping sheet_write(): requires OAuth (run in an interactive R session and complete drive_auth).")
}

When you create a new Sheet using sheet_write() function, you might have noticed that it comes with a randomly generated name. If you find this inconvenient, you can opt for using gs4_create() instead. This function provides you with more control over various aspects of the new Sheet, such as its name, formatting, and other settings. With gs4_create(), you can customize your Sheet to better suit your needs and preferences.

Code

if (can_write_gs) {
  gs.sheet <- gs4_create("MySheet", sheets = df.sheet)
} else {
  message("Skipping gs4_create(): requires OAuth.")
}

Code

if (can_write_gs) {
  gs4_get(gs.sheet)
}

Remember to replace MySheet with the name of your actual Google Sheet. Also, ensure that your Google Sheet has been shared appropriately if you’re accessing it from a different account.

Summary and Conclusion

This tutorial provides simple examples to help you get started with the googlesheets4 package for exporting and importing data. The package offers many more features for working with Google Sheets, including reading, writing, and managing permissions. For more advanced usage, please refer to the package documentation.

Resources

Get started with googlesheets4