13 Mar 2012

R-Function to Read Data from Google Docs Spreadsheets

I used this idea posted on Stack Overflow to plug together a function for reading data from Google Docs spreadsheets into R.










google_ss <- function(gid = NA, key = NA) 
    {
    if (is.na(gid)) {stop("\nWorksheetnumber (gid) is missing\n")}
    if (is.na(key)) {stop("\nDocumentkey (key) is missing\n")}
    require(RCurl)
    url <- getURL(paste("https://docs.google.com/spreadsheet/pub?key=", key,
                        "&single=true&gid=", gid, "&output=csv", sep = ""),
                  cainfo = system.file("CurlSSL", "cacert.pem", package = "RCurl"))
    read.csv(textConnection(url), header = T, sep = ",")
    }

## Example:
## Mind that the worksheets are numbered consecutively from 0 to n,
## irrespective of the actual worksheet-name.
## The key should be put in apostrophes.
## And, the URL works only for published spreadsheets!

(data <- google_ss(gid = 0,
                   key = "0AmwAunwURQNsdDNpZzJqTU90cmpTU0sza2xLTW9fenc"))

6 comments :

  1. This doesn't seem to be working anymore. Instead of the actual spreadsheet it now returns an HTML file with "Moved Temporarily" in the title attribute, a problem that other people seem to be experiencing as well but that I have as yet not found a solution for.

    ReplyDelete
    Replies
    1. Sry, can't tell why this is. For me it is working properly..
      I'll report if I find out what causes this issue.

      Delete
    2. This seems to be an authentication issue - I didn't find a solution yet..
      Please report if you know of one!!

      Delete
    3. It works for me. Thank you for the post.

      Delete
    4. Tobias, I think the reason why are you are seeing a html is because you have not published it as a csv. And this how you do it, you should click on the "publish to web" in the "File" drop down in the toolbar when you open the spreadsheet and publish it as csv, then you get a new URL which ends with "csv". Take the key in this URL and it should work fine.

      Also please change the share permissions of the spreadsheet to "anyone with link can read/ edit"

      Delete
  2. This is great, thanks. Can one do something similar with data from limesurvey?

    ReplyDelete