How to Connect R to Google Sheet and Use

Connect R to Google sheet is one of the common requirements I have come across in recent days. There are times when I get the data in the Google sheet and need to make a certain transformation using R and save it back.

Or, there are times when I get data in the CSV file and after some data transformation, need to save it back to Google sheet for the further uses. In such cases, R has been quite helpful!

If you’ve a similar requirement where you need the interaction of R with Google sheet, this article is for you. Here we’ll see how to connect R to Google sheet and work on it.

In this article, I am going to touch the following-

• R Package for connecting R to Google Sheet
• Accessing Google sheet files in R
• Making changes to the existing sheets of Google sheet using R
• Saving an R dataset to Google sheet

All the above-mentioned operations are basic for R connection to Google sheet.

Steps to connect R to Google Sheet and use it

Just follow the below steps to connect R to Google sheet and get started with the data analytics.

Step 1: Install the “googlesheets” packages in R

Open your R interface and install the packages googlesheets. Once installed, initialize the packages to use. You can use the below code to install and initialize the package-

 
install.packages("googlesheets")
library("googlesheets")

Step 2: Authenticate your Google account

Once you have installed and loaded the googlesheets package, you need to authenticate your Google account from where you need to access and use Google sheet. To authenticate your Google account, you can use the below code-

 gs_auth(new_user = TRUE)

This will take you to the browser and ask you to choose the Google account from where you’ll use Google sheet.

Once you’ve selected your Google account, it will ask you to allow the access. This is required because R’s package tidyverse need to manage your Google sheet.

Just click on the allow button and you’ll receive a page showing the successful message. Now you’re safe to close the page and get back to R with your work.

Step 3: Check your Google sheets in R

If your Google account has already some Google sheets which you can see in the R. For this you can simply use the below code-

 gs_ls()

This says, I have 2 files on Google sheet and those are displayed above with the details.

Step 4: Read the Google sheet data in R

I have a Google sheet with the name “mtcars” on my Google account. I am going to read that Google sheet in R. This has the same mtcars dataset.

 for_gs <- gs_title("for googlesheets")

We can read the above-loaded file using the function gs_read()

 for_gs_sheet <- gs_read(for_gs)

Let’s check the structure of the loaded file “for_gs_sheet”

 str(for_gs_sheet)

Step 5: Let’s modify the “mtcars” Google sheet

We can make changes to the read Google sheet “mtcars” using the function gs_edit_cells().

Please follow the below section of my Google sheet-

Now I am going to change the value of A2 and B2 cell to 1 and 2 using the below code-

 gs_edit_cells(for_gs, ws = "mtcars_dataset", anchor = "A2", input = c(4,5), byrow = TRUE)

Here-

• gs_edit_cells: This is the function used to edit the Google sheet
• for_gs: This is the variable where we have stored the read file
• ws: It refers to the sheet name in the Google sheet. My sheet name is “mtcars_dataset”
• anchor: refers to the cell from which the modification will begin. Here I am starting from A2 cell
• input: the values which we want to update. As I have started from A2 and provided 2 values and so, this will update A2 and B2.
• Byrow: indicates that the modification will apply horizontally. If this is vertical, A2 and A3 cells will change

If we’ll make byrow= False, A2 and A3 cells will be updated as shown below-

Step 6: Create a Google Sheet using R and save data to Google sheet

We can create a Google sheet using R using function gs_new(). Let’ create a new Google sheet with the name “Demo Sheet”.

 gs_new(title = "Demo Sheet")

This has created a blank google sheet with the name “Demo Sheet”

Now let’s save some data to the worksheet and save it to Google drive-

 gs_new(title = "IRIS Dataset", ws_title = "first_sheet", input = iris)

Here I am creating a new sheet with the name “IRIS Dataset” with the data from IRIS dataset.

Please note, we can add several Google sheets with the same name. In that case, those sheets will be differentiated by the “key”.

Conclusion- connect R to Google sheet

This was the basic guide on how to connect R to Google sheet and do the basic operation on Google sheet using R.

If your work also demands, managing the data from the Google sheet, this guide will be helpful to you. Please try and reach to me for any query. You can check more about the package googlesheets here.

You may also check this video guide on the same-