« Is the express line really faster? | Main | Analysis of airline performance »

September 28, 2009

TrackBack

TrackBack URL for this entry:
http://www.typepad.com/services/trackback/6a010534b1db25970b0120a5fbd39c970c

Listed below are links to weblogs that reference How to use a Google Spreadsheet as data in R:

Comments

Feed You can follow this conversation by subscribing to the comment feed for this post.

I just discovered this last night and have been using it continuously. Great post. The advantage (maybe difference is a better word) of RGoogleDocs is that the spreadsheet does not have to be made public to access it.

This is great. What we need is a R-specific URL shorter, similar tiny.url or tr.im. This service would take any gdocs spreadsheet or other web based spreadsheet or data source and make it into http://r.da.ta/smith_matrix_example_1, for example. Much easier to remember and share.

This is just way too cool. Aside from yet another reason to use google documents, it reminded me why google documents are worth using to begin with. I have two clouds: one I pay for, and one I get completely free. Guess which one is really the more useful. Duh. Thanks for the slap upside the head.

David

I've been using Google docs to make my R scripts and source data files available to interested readers for a few months now. Works great.

Here's a link to a post that shows the R generated chart and gives links to my R script and source files.

It's a good way to provide reproducible research resources. And the cost is free.

I am looking for Michael Rutter who is from Texas and attended Lon Morris. If he is out there, we are all looking fo him because there is a reunion in Feb. Mike, if this is you, please look on the LMC alum page or call Norm..............Sue Godsey

I export my spreadsheets from Google Docs this way using CSV files.

This service would take any gdocs spreadsheet or other web based spreadsheet or data source and source data files available to interested readers for a few months now. Works great.

This is very informative and it has worked for me in the past. I have recently gone back to some old code that uses this approach, created a new spreadsheet and... now I get this error:

Warning message:
In read.table(file = file, header = header, sep = sep, quote = quote, :
incomplete final line found by readTableHeader on 'http://spreadsheets.google.com/pub?hl=en&hl=en&key=0Ah58q5aXNtBOdEUwVm12SDlTTGJYQ0FsV3dBc255Z1E&single=true&gid=0&output=csv'

... I wonder if Google has changed something. Here is a spreadsheet that works by setting up the csv the way that's mentioned here:

https://spreadsheets.google.com/ccc?key=0Ah58q5aXNtBOdEpBLVhuTGJocUtsRFVNbjBlRWVXMGc&hl=en

... and here's the new one that doesn't work:

https://spreadsheets.google.com/ccc?key=0Ah58q5aXNtBOdEUwVm12SDlTTGJYQ0FsV3dBc255Z1E&hl=en&authkey=COrukusN

... any help would be greatly appreciated. I am baffled.

sorry... here is the one that works:

https://spreadsheets.google.com/ccc?key=0Ah58q5aXNtBOdEpBLVhuTGJocUtsRFVNbjBlRWVXMGc&hl=en&authkey=CIDHgd8D

... my apologies - the permissions had been changed

I can't get these to work anymore. The https:// protocol isn't supported with the read.csv function. I can't figure how to get google docs to give only an http:// link, instead of the https:// link. Can anyone else get this to work?

Hi,
I have the same problem - used to be able to use this fine (actually more than fine), but for the last year or so the google forms have been https
Paul

read.csv doesn't support HTTPS directly, but you can use the getURL function from the RCurl package to create a HTTPS-based connection. Updated the article accordingly -- thanks!

Thanks David, this helps a lot. But it does point out one of the risks of posting your data on Google (and perhaps, a risk of the "cloud" in general): they can change things at any time, without notice.

Here's another useful tool to link up R and Google products:

http://code.google.com/p/google-motion-charts-with-r/

It allows you to take data from R and graph it using the Google Motion charts API, without having to learn javascript!

It's a great tool, with a new version released just a few weeks ago (and available through CRAN, I believe).

Hello there, I tried you tutorial but it is not working. I keep receiving the "SSL Certificate Error" when I try the R comand. What could I be doing wrong?

Run this command before read.csv to get rid of SSL error:
options(RCurlOptions = list(capath = system.file("CurlSSL", "cacert.pem", package = "RCurl"), ssl.verifypeer = FALSE))

David,
Thanks a million for the reading https update.

You're welcome Paul, glad it was helpful.

The command setInternet2(use = TRUE)

makes it possible to open https connection like


one=read.csv("https://docs.google.com/spreadsheet/pub?hl=en_US&hl=en_US&key=xxxxxxxxxxxxxx....xxxxx")

Only problem with this is that there is an un-specified delay between when you make changes to a spreadsheet in google docs, and when the changes appear in the published CSV. The automatically publish option can sometimes take 5 minutes to 30 minutes. Really annoying to have to go manually republish every time a tiny change is made. There has got to be a way to get the actual doc rather than a secondary published version.

I also had issues with SSL and want to encourage the author or maintainer of this article to include something about the options that Joao shared:

options(RCurlOptions = list(capath = system.file("CurlSSL", "cacert.pem", package = "RCurl"), ssl.verifypeer = FALSE))

Thanks!

I suppose it probably should go without saying--but is still worth being said--that commas in the table cells will cause read.csv to choke.

#Aaryn , thanks, you definitely solved the problem. working perfectly now. as for the commas, that's one of the first things i look for when i get an import error, good tip

Instead of an Excel Spreadsheet, let's say I have a function or some code I want to read. How would you for example read all this code stored on google docs?

https://docs.google.com/document/edit?id=1f11rcVs9AtVcgOyiP0lV04yq9hshVDIPx93lA0ItFvQ

I've tried
source('https://docs.google.com/document/edit?id=1f11rcVs9AtVcgOyiP0lV04yq9hshVDIPx93lA0ItFvQ')
but it's not working

any ideas?

The comments to this entry are closed.


R for the Enterprise

Got comments or suggestions for the blog editor?
Email David Smith.
Follow revodavid on Twitter Follow David on Twitter: @revodavid

Search Revolutions Blog