Manipulating data in sheets can be a tedious task, but if you have at least a bit of a programmer inside you, you can make your work with Google sheets much easier. How – you ask? By connecting R to Google sheets using the googlesheets4 package provided by tidyverse.

This article will show you how to easily connect to your Google account, download data from Google spreadsheets to the dataframe in R, create a new sheet, as well as add new records, overwrite data, and delete individual columns.

• Connect R to Google Sheets
• Read Google sheets with R
• Create Google sheets with R
• Write Google sheets with R
• Summary

Googlesheets4 is the tidyverse library that allows integrating R workspaces with Google Sheets to enable data manipulation (reading, creating, and writing) in the R environment. As the official googlesheets4 documentation states, it is a reboot of a previous package called googlesheets. The current package connects to the corresponding, most modern version of the Sheets API (v4), and improves on utilizing the shared components of some other tidyverse packages, e.g. authorization using gargle, or ‘whole file’ operations using googledrive.

Are you limiting your business with Excel? Switch from Excel to R Shiny and build better business intelligence. 

With googlesheets4 you can:

• read data from an existing Google spreadsheet
• create a new Google spreadsheet
• overwrite or add new data in an existing Google spreadsheet

Connect R to Google Sheets

To start with, let’s try connecting our brand new R workspace to googlesheets4:

The command above should prompt the installation process, but it can also throw errors if you have some outdated packages in your environment (e.g. the incompatible version of ‘rlang’). Update required packages, and rerun the installation process. You may need to restart the R session afterward.

You may be asked if it’s OK to cache OAuth access credentials in the folder

~/Library/Caches/gargle between R sessions. In that case, select ‘Yes‘.

Load the library into the environment:

Verify if there are any authorized users using gs4_auth(). You can either select a user from a list or create a new token for a different Google account by entering ‘0’. Press Esc/Ctrl + C to cancel. Tokens are valid in the current R session.

Let’s connect a new user by entering ‘0’ in the command line. It prompts a browser to open a list of your available Google accounts. 

Select an account, and agree for the app to impersonate you. This means seeing, editing, creating/deleting, uploading, downloading, organizing, sharing/unsharing your Google spreadsheets:

You should see the following message in your browser afterward: “Authentication complete. Please close this page and return to R”.

Excel is obsolete for enterprises. Take your business to the next level with these 2 excel alternatives. 

Note: If you do not need to access any private Google spreadsheets use gs4_deauth().

Read Google Spreadsheet in R

Below we have a Google spreadsheet built using the Microsoft AdventureWorks sample database. The goal is to transfer it to R.

You can connect to a particular Google spreadsheet by using read_sheet().

Example:

As you can see, R recognized the dataset together with its headings:

Tip: A Google spreadsheet can also be loaded using range_read().

Using R to Create Google Sheet

Creating a new Google spreadsheet requires using gs4.create(). If you do not pass the spreadsheet name as a parameter, the library is going to choose a random funny-sounding spreadsheet name for you:

You can also specify the name of the sheet that you want to create, e.g:

As you can see, all newly created sheets are visible among other spreadsheets that belong to the user:

There is an option to populate the spreadsheet with data while creating it. Let’s create a new Google spreadsheet ‘test_sheet2’ that is going to be a copy of  ‘Revenue by Country, State and Category’:

It is possible to define custom metadata if for some reason the default settings are not going to support the content properly:

This is how you can name your individual sheets:

Note: It’s not possible to delete the entire spreadsheets using gs4. In that case, you either need to manually delete or trash the files using googledrive library. An individual sheet can be removed using sheet_delete().

Better business decisions requires better BI tools. Compare these top 3 BI tools. 

Using R to Write in Google Sheet

The sheet_write() function is very similar to gs4_create(). It can also be used to copy data from one Google spreadsheet to another, e.g.:

The script created a new sheet, ‘matricidal-cuttlefish’, that contains the same data as ‘Revenue by Country, State, and Category’. 

We can now add new sheets and fill them with records. Let’s add another sheet ‘Products’ to ‘matricidal-cuttlefish’ and populate it with data imported to R from a .csv file.

Tip: You can trigger opening the file in the browser from the console using gs4_browse().

A new column that informs analysts about discontinued products was added to Products.csv. Let’s add this column to our Products page. We are going to start by creating a single column data frame that holds ‘SellEndDate’ data:

Then, we add this duplicated column to a spreadsheet:

We can clear the values in this column by using range_clear() or range_flood():

The difference between these two functions is that range_flood() may include additional context, e.g. the following request will overwrite all values:

This will get rid of an entire column:

Some new products are supposed to be added to the system, so the ‘Products’ page in the ‘matricidal-cuttlefish’ file has to be updated.

See the ProductsNew.csv file that contains information about new products:

Let’s add these new products to the list of already existing products:

As you can see, the new records were successfully added at the end of the file:

Does your team need more from Tableau? Discover how to merge  Tableau and R Shiny with Rstudio’s shinytableau R package.

Summary

To sum up, googlesheets4 is a pretty powerful library that allows a user to automate some of the data manipulation activities in Google spreadsheets, like creating new sheets or adding new columns/records. At the same time, it also enables reading data directly from Google spreadsheets to perform analyses in R. 

This package also has well-written documentation supported by examples of use. Although it’s sometimes difficult to understand the range of operations that the user can perform with googlesheets4, it’s a nice option for novice users who would like to expand their data analysis skills.

Article How to Connect R to Google Sheets Using googlesheets4 comes from Appsilon | Enterprise R Shiny Dashboards.