How to read and write to Google Sheets from R!

This is incredibly simple. Why would someone want to achieve this? Well, I wanted to create a shinyapp hosted on a server and required an accessible database for the app to read data from whenever app was in use. Using Google Sheets as a database can get you set up very quickly. Advantages include speed of setup and easy manipulation of data.

Read data from Sheets

1. Install and load the required packages first.

install.packages("googlesheets4")
library(googlesheets4)

2. Get the URL of your Google sheets and make the file publicly accessible.

3. Use function “read_sheet” to access data from desired sheet. In order to read data, you’re not required to have an access token. Just run “gs4_deauth” first.

gs4_deauth() ##authentication 
df <- read_sheet("URL of your google sheet") ##insert data

Writing Data to Sheets

1. Run gs4_auth() code. This will prompt a window to grant access to your google sheet files. This method, if running R locally, does not require you to obtain an access token.

gs4_auth() #Run to authorize google sheets access. 

Authentication Window

2. Grab the sheet ID of your google sheet. In my case, the ID is 46 characters in character length. Please see example screenshot.

##assign the sheet ID to an object
ss = “YOUR SHEET ID”

Sheet ID example

3. Now just write to the sheets file! I’ve used functions from the “dplyr” package to make the changes to the sheet file. In the first argument of the function sheet_write, add in the sheet ID. And finally, in the second argument, call the title of the sheet itself.

##now write to sheets file
df %>% sheet_write(ss, sheet = “sheet_title”)

And that is it. That’s how you can read and write data to Google sheets from R. If you’re using R via a virtual machine and require a non-interactive authentication process, then check this “how to” post out. (Coming soon)