Reading Google Sheets In R

In this article, we are going to see how to read google sheets in R Programming Language.

Method 1: Using googlesheets4

Before moving forward we need to Installing the googlesheets4 package into the working space.

The googlesheets4 package in R is used to read and access the contents of a google sheet. It can be used to read as well as write the meta data into the data cells of the sheet. The package can be downloaded and installed into the working space using the following command : 

install.packages("googlesheets4")

Upon successful invocation of the package into the working space, the google sheet can be read into the local directory. There is an inbuilt method in this package read_sheet which is used to read the contents of the sheet. It can be used to access all the data elements and then modify or use it for further usage. The method has the following syntax :

Syntax: read_sheet(url)

Arguments : 

• url – The url of the google sheet

 

This interface is encountered and upon further typing the “YES” option, the working directory confirms whether to proceed further or to exit. The following snippet illustrates it.

The account for authentication is asked to choose from. The account is then selected and then its password is entered. The R working space directory is granted access to all the phases in order to access the google sheets content :

 

After the successful authorization of the tidyverse package, which is a dependency for the googlesheets4 package, the following message is displayed on the browser screen :

 

After this, the R studio console can be opened in order to clearly display the contents of the sheet stored in a variable object.

 

 

The data is displayed in terms of a tibble which contains data in a well organised tabular structure. The data type of each of the columns is displayed. It shows the total number of rows contained. The column headers of the sheet columns become the column headers for the tibble as well.

Method 2: Using gsheet package 

The gsheet package in R can be used to download and access the contents of the google sheet in the working space. It simply uses the URL for accessing the contents of the file. The gsheet2tbl() method in this package is used to convert the google sheet data into a table directly. The google sheet that is supplied as the url must have ‘sharing’ feature active. The method has the following syntax :

Syntax: gsheet2tbl(url)

Arguments : 

• url – The url of the google sheet