Hello everyone!

I think each of us at least once had to deal with work in Google Sheets. They are usually appreciated for their ability to quickly change information in tables and to work with data together. More, the results from the survey in Google Forms are also stored in Google Sheets.

Nevertheless, downloading a table and reloading it into R after each editing takes a certain amount of nerves and time. Therefore, today I suggest you take a look at a simple package gsheet that will help you quickly load data from Google Sheets. All the credits come to Max Convay

First, let’s install the package and run it.

#install.packages('gsheet')
library(gsheet)

Let’s imagine that we want to study for what purpose cats usually do meow. My research suggests it’s to request either for food or for play. I assume that the data will be entered by different users on their own, so I created a dataset in Google Sheets.

Now let’s see how we can quickly access the data in R. To do this, we should create a variable that will store a link to our data in Google Sheets. Please note that your sheet must have ‘share by link’ turned on.

url <- 'https://docs.google.com/spreadsheets/d/1Zk9BuHEM2-tEVnVOyahysgHhdHXbkIFpDB77syc15rg/edit?usp=sharing'

The next step is to save the data from the specified link to the dataframe. The simple way is to use gsheet2tbl function.

meow_data <- gsheet2tbl(url)

Done! Let’s see whehter it works.

summary(meow_data)
##    cat_name           food_meow      play_meow    
##  Length:6           Min.   :2.00   Min.   :1.000  
##  Class :character   1st Qu.:2.25   1st Qu.:2.750  
##  Mode  :character   Median :3.50   Median :5.500  
##                     Mean   :4.00   Mean   :5.167  
##                     3rd Qu.:5.50   3rd Qu.:7.500  
##                     Max.   :7.00   Max.   :9.000

For those who want to increase the speed of data loading, I suggest looking at this package works with read.csv function. To do this, you need to specify a variable inside read.csv with a link to the data using the gsheet2text function.

read.csv(text=gsheet2text(url, format='csv'), stringsAsFactors=FALSE)
##   cat_name food_meow play_meow
## 1    Salem         3         1
## 2     Coco         4         2
## 3    Simba         6         6
## 4    Misty         7         5
## 5    Oscar         2         8
## 6  Zigmund         2         9

Done! Looks simple! To make sure that the data will be updated in R when you change the Google Sheet, add a name of your cat to the table in Google Sheets and provide approximate values.

P.S. I have already added mine with the sixth value :)