How to upload data from R into Google Sheets

Welcome back! R is a very powerful statistical programming language, you can bring data in, process it and export it out in many data formats (.CSV, .XLSX, etc.), some of you may not know this but you can actually export an R data frame straight into Google Sheets! This is how you do it!

Installation

First off, we want to install the Google Sheets 4 package, to do this, use the following installation line inside of your R console:

install.packages("googlesheets4")

Developing The Code

Awesome, next up we want to import that library and bring in a dataset into our R environment, you can bring in any data frame but in this case we’ll just make a sample one in R:

#IMPORTING THE LIBRARY
library(googlesheets4)
#CREATING THE DATA FRAME
df = data.frame(matrix(rnorm(20), nrow=10))

Next up, we want to pretty much store this data into a new Google Sheet, to do this we want to use the following line of code:

(ss <- gs4_create("Sample R", sheets = df))

Let me explain this whole line of code. The ss is just a standard function within this package, the gs4_create function indicates that we’re going to be creating a new Google Sheet page, the “Sample R” is just the name of the Google Sheet, we can rename this to whatever you want later and the sheets = df indicates what data we want in our Google Sheets.

Running The Code

Alright, let’s run this code! Let’s start off by importing the googlesheets4 package, bring in our data frame and run the Google Sheets line above, you will essentially get a little prompt in our R console which allows you to give access from your Google Account to the API:

A little hard to read, but just follow the prompts and you’ll be good to go. Next up you want to make your way over to your R console and watch some magic happen, you will eventually see this output in your R console:

Awesome! We can now make our way over to our Google Sheets account and see that a new Google Sheet has just been added with the same name we indicated before:

When we open up that file we will see the data we had within our R environment:

Congrats! This package has a lot more functionality than this (which i’ll talk about in later articles), but this is a great introduction to dynamically storing your data inside of a Google Sheet!

As Always

As always, if you have any suggestions, thoughts or just want to connect, feel free to contact / follow me on Twitter! Also, below is a link to some of my favorite resources for learning programming, Python, R, Data Science, etc.

Data Scientist / Engineer

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store