Tutorial

Reading Google Sheets In R [the Easy Way]

Published on August 4, 2022
author

Prajwal CN

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.

Account Sign In  - Reading Google Sheets Into R
Account Sign In
  • 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.
Access - Reading Google Sheets Into R
Access
  • In the below picture, you will be shown the permissions you are giving to the Tidyverse API. Click “Allow” and you are done.
Access Authorization - Reading Google Sheets Into R
Access Authorization
  • After the successful authorization, you can see the completion message.
Authorization Success
Authorization Success
  • After this, you will see a successful authorization message in the R studio as shown below.
Rstudio
Rstudio

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.

Credit Data - Reading Google Sheets Into R
Credit Data
  • 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

Thanks for learning with the DigitalOcean Community. Check out our offerings for compute, storage, networking, and managed databases.

Learn more about our products

About the authors
Default avatar
Prajwal CN

author

While we believe that this content benefits our community, we have not yet thoroughly reviewed it. If you have any suggestions for improvements, please let us know by clicking the “report an issue“ button at the bottom of the tutorial.

Still looking for an answer?

Ask a questionSearch for more help

Was this helpful?
 

Try DigitalOcean for free

Click below to sign up and get $200 of credit to try our products over 60 days!

Sign up

Join the Tech Talk
Success! Thank you! Please check your email for further details.

Please complete your information!

Become a contributor for community

Get paid to write technical tutorials and select a tech-focused charity to receive a matching donation.

DigitalOcean Documentation

Full documentation for every DigitalOcean product.

Resources for startups and SMBs

The Wave has everything you need to know about building a business, from raising funding to marketing your product.

Get our newsletter

Stay up to date by signing up for DigitalOcean’s Infrastructure as a Newsletter.

New accounts only. By submitting your email you agree to our Privacy Policy

The developer cloud

Scale up as you grow — whether you're running one virtual machine or ten thousand.

Get started for free

Sign up and get $200 in credit for your first 60 days with DigitalOcean.*

*This promotional offer applies to new accounts only.