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

March 13, 2010
By

(This article was first published on R-statistics blog » R, and kindly contributed to R-bloggers)

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.

http://r-forge.r-project.org/projects/rgoogledata/

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

To leave a comment for the author, please follow the link and comment on his blog: R-statistics blog » R.

R-bloggers.com offers daily e-mail updates about R news and tutorials on topics such as: visualization (ggplot2, Boxplots, maps, animation), programming (RStudio, Sweave, LaTeX, SQL, Eclipse, git, hadoop, Web Scraping) statistics (regression, PCA, time series, trading) and more...



If you got this far, why not subscribe for updates from the site? Choose your flavor: e-mail, twitter, RSS, or facebook...

Tags: , , , , , , , , ,

Comments are closed.