Skip to content

Category: R

Milking Google Sheets with R

Google Sheets is a wonderful thing.  I use it to collate information from my students through Google Form (another wonderful invention). In short,  free Excel sheet on the cloud is readily available!

Since it is on the cloud, can I load it in R and use it directly there? Apparently we can.  I just had to ask the correct question.

There were multiple solutions in the stackoverflow site. I like the solution provided by Max Conway the best. Just three lines of code to accomplish it.

So. what’s the keeidea here?

First install the gsheet package. Then load the library. Finally, use the function gsheet2tbl to get the data in google sheet. The URL of the Google Sheet is kept within the brackets after the gsheet2tbl.

#install the gsheet package
install.packages('gsheet')

#load the gsheet library
library(gsheet)

#load data 
data<-gsheet2tbl('docs.google.com/spreadsheets/d/1I9mJsS5QnXF2TNNntTy-HrcdHmIF9wJ8ONYvEJTXSNo')

This way, you would have loaded the target Google Sheet as “data”.

Just three lines of codes to milk the Google Sheet using R! We have just supercharged the usability of Google Sheet!

Cleaner code: Using a main.R and multiple component R files.

As a true blue rookie coder, I am also good at making spaghetti… codes!

The sheer fun of coding sometimes makes me lose myself in time, as well as in the codes.

Until it reached a tipping point, which I think is today.

Lately I have several types of experimental data files (i.e. postural balance data, heart rate data, etc..) to process, and processing them all in a single R file is pretty daunting. It is easy to lost myself in the loops and if functions. Plus its not exactly easy to count the opening and closure of brackets.

I have been toying with the idea of running multiple codes from a main code, so that my main code looks more manageable. I am sure it is the kind of things the professional does, but it just didn’t occur to me soon enough.

Lo and behold, I found something on stackoverflow that does exactly what I wanted to do.

I am surprised it’s pretty easy.

The keeidea is:

1. Create a main R file. You can name it main.R or whatever that sounds kingly and important. This is the file that you will personally execute for it to call out the individual R files to run the sub-processes.
2. Within the main R file, include the function source to point it to the files that run the sub-processes:  source (“subProcess1.R”)
3. Create the various part R files that run your sub-processes.
4. In the main R file, you can organise your loops and if functions.

Example:

#Remember to put all the files in the same directory, for simplicity sake.
setwd("~/myDirectory/")

#Supposed there are 20 data files to parse through, a <em>for</em> loop is used.
for (i in 1:20){

#This is the sub-process that all 20 files will go through. The i index can be used in this sub-process  
source("subProcess1.R")

#Suppose there is a need to run sub-process 2a if n is greater than 1, and run sub-process 2b if otherwise. Use the if loop.
if (n > 1) {
  source("subProcess2a.R")
 } 
else 
{
  source("subProcess2b.R")
}

}

Pay careful attention to the allocation of variable names when running multiple processes. It can get messy!

Skip to toolbar