Code
packages <- c(
'googlesheets4',
'googledrive',
'tidyverse'
) 
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
#| 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. However, if you don’t intend to write Sheets or read private Sheets, you can use gs4_deauth() to indicate no need for a token. For more information, refer to the article ‘Google Sheets4 Auth’. Please note that we have logged into Google as a specific user in a hidden chunk for this overview.
Code
# Authenticate with Google Sheets
# This will open a browser window for authentication
gs4_auth(email= "zia207@gmail.com")Data
All data set use in this exercise can be downloaded from my Dropbox or 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-oa4vuDqwGWU80LLaYmSJSPZNfFMis the ID oftest_data.gsheetin 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
sheet<-sheet_write(df.sheet) ✔ Creating new Sheet: "lowbred-inchworm".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
gs.sheet <- gs4_create("MySheet", sheets = df.sheet)✔ Creating new Sheet: "MySheet".Code
gs4_get(gs.sheet)
── <googlesheets4_spreadsheet> ─────────────────────────────────────────────────
Spreadsheet name: "MySheet"
ID: 1Zvdipz3_YmsLqbn44DiaZjG5FmuAye5rIh7pmA--G08
Locale: en_US
Time zone: Etc/GMT
# of sheets: 1
── <sheets> ────────────────────────────────────────────────────────────────────
(Sheet name): (Nominal extent in rows x columns)
'df.sheet': 43 x 13Remember 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.