How to Read, Write, Append Google Sheet Data using R
Would you like to learn how to connect R to your Google Drive and automate the data processing of your Google Sheets?
In this tutorial you will learn how to:
- Connect R to Google Drive and list your existing Sheets;
- Write and read Google Sheets from R
- Append data to a Google Sheet;
- Rename a worksheet;
- Change file access permission (from private to public)
- Delete programmatically Google Sheets
You can also watch this tutorial on YouTube:
Download the R code of this tutorial by joining my newsletter on www.felixanalytix.com. You will receive an automatic email from me to access the R script.
How to Connect R to Google Drive
The first thing you want to do is to install and attach the necessary R packages for this project.
Using the Google Drive package we can run the drive_auth() function to authentificate to our Google drive.
This function will allows you to authenticate via your own web browser.
You can accept and allow Google drive to access your own private Google Sheets.
How to List Existing Google Sheets using R
Once you’re connected you can simply run this gs4_find R function to list all your existing Google Sheets.
How to Create an new Google Sheet with specific data in R
Let’s take some random data and create a new Google Sheet.
We will use the Texas housing dataset, which is accessible from the ggplot2 R package (loaded when you attach the tidyverse R package). We call our dataset “df”.
This data contains housing sales in Texas by area on by date.
To write the Google sheet we will use a gs4_create R function. What you should do is to give it a name using the “name” argument, here we will call it just “txhousing”. And then under the “sheets” argument we will call our “df” object. If we create a list we can choose the name of the worksheet so here we call it “Sheet1”. You can give the worksheet the name that you want using this list() function.
We also have saved the output of this function in a “ss_txhousing” object. This object contains information about the spreadsheet: its name, its “id”, and also the local time zone as well as information about the dataset: 9 variables and 8603 rows (we have 1 more row because this is the name of the variable that are here in the row number 1).
Using the gs4_browse function you can also open the new Google sheet you just created in your own web browser.
How to Read Google Sheets in R
Now let’s see how to read this Google sheet we just created into R.
The first thing we want to do is to find the worksheet. By calling the gs4_file R function we can easily find our Google Sheet and save its metadata in an object called “meta”.
There are 3 ways to read this Google sheet into R.
- Just to take the URL which is so so (here with the paste0 function I just create the URL).
- Use the read_sheet function (it is easier than option 1. as you don’t have to get the full URL but just the ID).
- Use the drive_get function with the name of your worksheet as an argument and then pipe (i.e. %>%) the read_sheet function to it. It is even easier than option 2. However if you have multiple worksheets with the same name it can cause an error.
You can also use a range specification just to access specific columns or rows of your dataset. Below I called the range A1 to C6 of the Google Sheet.
How to Append Data to a Google Sheet using R
Now we’ll show you how to append and create new Google worksheets from R.
We need to create multiple datasets by city in R so we have data to append.
First we will create a new Google sheet with the function gs4_create using the new dataset “df_city_1”. We named the worksheet “sheet1”. Then we append (or bind) by row this first city using the data from the second city, i.e. “df_city_2”. We reuse the “ss_cities” metadata that we got when creating the Google sheet “cities”.
So just to be sure let’s read this new worksheet we just created with the two city names. We can count by city just to be sure we have the two cities.
In the R console you should see that indeed we have the two first cities.
How to Create a New Worksheet with R
Now I want to show you how to create a new worksheet.
Once again we will take the metadata about this specific Google sheet and then we will use the sheet_add function with a new name. We’ll put it after our first worksheet that we called “Sheet1”. This operation creates a new empty worksheet. Next step is to push to this new worksheet our dataset “df_city_3”, which we name “City3”.
We can call the sheet_names function on our metadata to double check that we have indeed two different worksheets.
How to Rename a Worksheet using R programming
Now let’s say we want to rename the worksheet (we named it above “City3”).
To do that once again we will get the metadata about the Google sheet we want to work on (“cities”). But this time we will use the sheet_renames function with a the name of the worksheet we want to rename with the “new_name” argument. We will can give it a new name: “Arlington”.
How to Change Sheet Permission Access from R
If you want to give access to this new Google Sheet to everyone (for now it is only private), you can use the drive_share function from the {googledrive} R package.
You can change the role argument as “reader” (the users can just read but not edit) and you can also use a type here to “anyone” (anyone that has this specific URL can access it).
How to Delete programmatically Google Sheets in R
We can easily remove this Google sheet using the Google Drive function drive_trash to remove the “txhousing” Google sheet. We can do the same for the “cities” Google Sheet.
Much more can be done with the {googlesheet4r} R package but I hope this little tutorial helps you to use the main function of this package.
You can get all the code of this tutorial by joining my newsletter on felixanalytix.com. Once you subscribed you will receive an automatic email with the URL of my GitHub account.
See you in another tutorial, bye!
