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.
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!
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.
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.
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.
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.
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.
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.