14 Importing Google Sheets

In addition to CSV and Excel files, you may often need to import data directly from Google Sheets. Google Sheets is a popular cloud-based spreadsheet application that allows you to share and collaborate on data. Using the googlesheets4 package, you can import data directly from a Google Sheet into R.

14.1 Install the `googlesheets4` Package

If you haven’t already installed the googlesheets4 package, you can do so by running the following command:

install.packages("googlesheets4")

Then, load the package:

library(googlesheets4)

14.2 There Is No Option to Import through RStudio-Generated Code

As we saw earlier, using the RStudio IDE to import data can be a helpful way to quickly set up an import. Unfortunately, RStudio has no option to import data from a Google Sheet using the graphical interface of the IDE. We must use AI-generated code or user-generated code to import data from a Google sheet.

14.3 Import Google Sheets Data through AI-Generated Code

To import a Google Sheet through AI, you need to provide the AI with the URL of your Google Sheet and specify the name you want to assign the dataset in R. Additionally, you’ll need to ensure the googlesheets4 package is installed and loaded.

AI Prompt:

Import Google Sheets data:

Please provide the tidyverse code to import a Google Sheet from the following URL: https://docs.google.com/spreadsheets/d/your_sheet_id/edit with the following requirements:

Use the googlesheets4 package to read the data.
Ensure that any necessary authentication is handled (e.g., prompt for authorization).
Import the default sheet from the Google Sheet.
Name the resulting tibble sheet_data.

14.4 Import Google Sheets Data through User-Generated Code

Authorize Access to a Google Sheet

When you first attempt to read from a Google Sheet, googlesheets4 will prompt you to authorize access to your Google account. This step allows R to read the data from your Google Sheets. You’ll be guided through a browser window where you’ll log into your Google account and grant permission.

Importing Data from Google Sheets

To read data from a Google Sheet, you will need the URL of the Google Sheet. Here’s an example of how to import data:

## Import data from a Google Sheet
sheet_url <- "https://docs.google.com/spreadsheets/d/your_sheet_id/edit"
data <- read_sheet(sheet_url)
data

Replace "https://docs.google.com/spreadsheets/d/your_sheet_id/edit" with the actual URL of your Google Sheet.

Selecting specific sheets

If the Google Sheet contains multiple tabs, you can specify which sheet to read using the sheet argument:

data <- read_sheet(sheet_url, sheet = "Sheet2")

The default name of the first Google worksheet is Sheet1 in contrast to the default name of the first Excel worksheet Sheet0.

Specifying a range

You can also specify a particular range of cells to import:

data <- read_sheet(sheet_url, range = "A1:D10")

This is particularly useful if you only need a subset of the data from the sheet.

14.5 Advantages of Using Google Sheets

Real-time collaboration: Google Sheets allows you to collaborate with others in real-time, making it ideal for shared data collection and updates.
Cloud-based: Since Google Sheets is cloud-based, you can access the latest version of your data without needing to manually download and upload files.
Simple integration with R: With googlesheets4, importing data from Google Sheets is as straightforward as reading a CSV file.

By using Google Sheets in combination with R, you can work with dynamic datasets that are updated live, streamlining collaboration and analysis.