Reading Google Sheets In R [the Easy Way]

If I ask you a question - “Do you use spreadsheets”?, I will immediately get a straight YES. It shows how popular and useful spreadsheets are in day-to-day business operations. If you are an analyst, then spreadsheets are invaluable. You might have used both Excel and Google sheets for your works. There is a good chance that you have read excel sheets into R many a time. But, did you ever thought of reading Google Sheets into R?. If not yet, it’s worth a try.

Getting started with Google Sheets

You have heard of Google sheets. It is like Excel. It will allow you to organize, edit and analyze the different types of data. But, unlike Excel, google sheets is a web-based spreadsheet program, which encourages collaboration.

This will automatically be synced with your Google account, Google drive, and its fellow services such as google docs and slides. In google sheets, you need not save every time. It offers an autosave feature, which will update the sheets after each activity. Isn’t it cool?

If we talk about the interface, google sheets will follow Excel with reasonable changes. You are free to share the sheets for any collaboration. Most of the time, it will make our lives easy as multiple people can work on the sheets in real-time.

I think it’s enough information about google sheets, let’s dive into something exciting!

Reading Google Sheets in R

You can read google sheets data in R using the package ‘googlesheets4’. This package will allow you to get into sheets using R.

First you need to install the ‘googlesheets4’ package in R and then you have to load the library to proceed further.

#Install the required package
install.packages('googlesheets4')

#Load the required library 
library(googlesheets4)

That’s good. Our ‘googlesheets4’ library is now ready to pull the data from google sheets.

1. Setup the Authorization

You cannot read the data from google sheets right away. As Gsheets are web-based spreadsheets, they will be associated with your google mail. So, you have to allow R to access the Google sheets.

You would have used functions like read.csv or read.table to read data into R. But, here you don’t need to mention the file type. All you need is to copy the google Sheets link from the browser and paste it here and run the code.

Once you run the below code, you can see an interface for the further process.

#Read google sheets data into R
x <- read_sheet('https://docs.google.com/spreadsheets/d/1J9-ZpmQT_oxLZ4kfe5gRvBs7vZhEGhSCIpNS78XOQUE/edit?usp=sharing')

Is it OK to cache OAuth access credentials in the folder


1: Yes
2: No

You have to select option 1: YES to continue to the authorization process.

As a first step, if you are having multiple G accounts logged in, it will ask you to continue with your account as shown below.

• You have to select your account to authorise R to access the G sheets. This process is followed by multiple authorizations. You have to allow R to in all those steps.

• In the below picture, you will be shown the permissions you are giving to the Tidyverse API. Click “Allow” and you are done.

• After the successful authorization, you can see the completion message.

• After this, you will see a successful authorization message in the R studio as shown below.

2. Reading the Data into R

It’s great that you have completed the authorization process and it went successfully. Now let’s see how we can read the data into R from Google sheets.

#Reads data into R
df <- read_sheet('https://docs.google.com/spreadsheets/d/1J9-ZpmQT_oxLZ4kfe5gRvBs7vZhEGhSCIpNS78XOQUE/edit?usp=sharing')

#Prints the data
df

# A tibble: 1,000 x 20
   months_loan_dura~ credit_history purpose amount savings_balance employment_leng~
   <chr>                      <dbl> <chr>   <chr>            <dbl> <chr>           
 1 < 0 DM                         6 critic~ radio~            1169 unknown         
 2 1 - 200 DM                    48 repaid  radio~            5951 < 100 DM        
 3 unknown                       12 critic~ educa~            2096 < 100 DM        
 4 < 0 DM                        42 repaid  furni~            7882 < 100 DM        
 5 < 0 DM                        24 delayed car (~            4870 < 100 DM        
 6 unknown                       36 repaid  educa~            9055 unknown         
 7 unknown                       24 repaid  furni~            2835 501 - 1000 DM   
 8 1 - 200 DM                    36 repaid  car (~            6948 < 100 DM        
 9 unknown                       12 repaid  radio~            3059 > 1000 DM       
10 1 - 200 DM                    30 critic~ car (~            5234 < 100 DM        
# ... with 990 more rows, and 14 more variables: installment_rate <chr>,
#   personal_status <dbl>, other_debtors <chr>, residence_history <chr>,
#   property <dbl>, age <chr>, installment_plan <dbl>, housing <chr>,
#   existing_credits <chr>, default <dbl>, dependents <dbl>, telephone <dbl>,
#   foreign_worker <chr>, job <chr>

Here you can see, how R can read the data from Google sheets using the function ‘read_sheet’ function.

I am also adding the dataframe here for your reference / understanding.

• Once you setup the account, it will be a very easy game.

3. Reading Google sheets into R using Sheet ID

You don’t need to copy the sheet link to read the data. You can only copy the sheet ID and can use that with the read_sheet function. It will read the data as usual.

If you are not aware of sheet ID, I have added a sheet link and I have highlighted the Sheet ID with color. You can copy this ID can follow the same process.

https://docs.google.com/spreadsheets/d/**1J9-ZpmQT\_oxLZ4kfe5gRvBs7vZhEGhSCIpNS78XOQUE**/edit#gid=0

You can find the discussed code below.

#Reads the data with Sheet ID into R
df <- read_sheet('1J9-ZpmQT_oxLZ4kfe5gRvBs7vZhEGhSCIpNS78XOQUE')

#Prints the data
df

This code will give the same output i.e. data. I have used credit data for the whole illustration. You can use any data for this purpose. I hope from now, reading google sheets into R is not an issue for you.

Ending note

Almost all organizations use Google sheets for business operations and data works. As an analyst or an R user, it will be good if you know how to work with Google Sheets and R. It is a very simple method can you can practice this on your data and sheets ID/link. I hope you learned something which will save your time in your work. That’s all for now and Happy R!

More read: R documentation