Google spreadsheets + google forms + R = Easily collecting and importing data for analysis

Someone on the R mailing list (link) asked: how can you easily (daily) collect data from many people into a spreadsheet and then analyse it using R.

The answer people gave to it where on various ways of using excel.  But excel files (at least for now),  are not “on the cloud”.  A better answer might be to create a google form that will update a google spreadsheet that will then be read by R.

If my last sentence wasn’t clear to you, then this post is for you.

First view the following video (2:50 min) which explains what google spreadsheets are

Then view the next video (2 min), which explains what using google forms with google spreadsheet is all about

The next step for you is to access the spreadsheet without having to make it public is to go thorough this tutorial on how to use the R package RGoogleDocs.

If you feel comfortable to publish the google spreadsheet, there is a simpler tutorial for you here.  And you could see examples of using this method here and here.

Update. Farrel Buchinsky wrote (in the mailing list) the following:

There is also a package called RGoogleData that can import data from Google
Spreadsheets (even when they are not public). It has one benefit over
RGoogleDocs; RGoogleData can download a spreadsheet as a csv file to your
harddrive. You can then read the csv file into R – read.csv(). Those steps can
easily be written into a script so that you do not have to manually futz with
them each time. The benefit of the csv download is that it is very fast. Both
the RGoogleDocs and the RGoogleData are slow in reading the spreadsheet into R.
I understand, but may be wrong, that Google Stores all the data as HTML and its
API spits it out as xml which these r packages then need to parse out line by
line. The csv treatment by comparison is instantaneous.

Only one big problem. As of a few months ago, RGoogleData has stopped working
for me. I think it is still working for its writer, Adrian Dragulescu. Adrian
has been very generous with his time in trying to help me. He predicts that he
will go back to exploring his package once Google updates their API.

Try it out. I would love to hear what happens.

Do you have any good example for using R with google spreadsheets? please share with us in the comments…

0 thoughts on “Google spreadsheets + google forms + R = Easily collecting and importing data for analysis”

  1. I’m having trouble. I got the RGoogleDocs package installed. Using the getGoogleAuth() , getGoogleDocsConnection() , and getDocs() commands I am able to see the titles of all of my Google Documents. However, when I try to get the content of a document using getDocContent(), I get a long character string of html code that does not appear to contain the data I was expecting.

    Is it working for others? Any pointers on what I might be doing incorrectly?

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.