Reading in Google Spreadsheets to R

It's helpful to be able to collaborate in collecting data. Google spreadsheets is a useful tool for this.

For those implementations of R that support the web protocol used by Google, there is a simple way to read Google spreadsheets into R via the fetchGoogle function in the mosaic package. Otherwise, there is a multi-step process that is not too difficult.

How Will I Know What to Do?

Check whether the RCurl package is installed on your system. If it is, then you're good to go with the fetchGoogle method. The server at Macalester does have RCurl installed. The server does not (as of the time of this writing).)

If you are running R on your own desktop rather than using a server, try installing RCurl. On many systems, it is very easy; on others it is not possible unless you have some detailed knowledge of how to install command-line software.

Using fetchGoogle

(See below for how to read Google spreadsheets without fetchGoogle.)

You will have to set up your spreadsheet on Google to talk with R. This is a one-time operation that will continue to work even if you add more data to the spreadsheet.

1. In Google spreadsheets, choose File/Publish to the web
2. Start publishing, checking “Automatically republish when changes are made”
3. Under “Get a link to the published data”
4. Select “CSV” Sheet 1 All cells
5. Copy the link. You'll need it in a bit.

Now, to R …
1. Make sure you have the mosaic package running: require(mosaic)
1. Paste the link from step (5) into R as a quoted argument to fetchGoogle() command, like this

mydat = fetchGoogle("https://docs.google.com/spreadsheet/pub?key=0Am13enSalO74dEtyWGxpWWFsN3Z0OUlZNG5xYmRVWWc&single=true&gid=0&output=csv")

You can email this command to your collaborators. You may find it convenient to use the history command (searching on “fetchGoogle”) to find the command again when you need it.

Without RCurl

Unfortunately, the particular web protocol used by Google spreadsheets is not yet available in all implementations of R. Until it is, here are the several steps to read a Google spreadsheet into R. (If you're running a desktop version of R, check out the section “If you can install RCurl…”)

1. In Google spreadsheets, choose File/Download As/Comma Separated Values (CSV). This will save a file to your computer. Make note of its name.
2. If you are running R on your own computer, e.g. through the desktop version of RStudio, use fetchData() to read in the spreadsheet you have just saved. When given an empty argument, fetchData() will bring up a file browser which you can use to navigate to the file you just saved. BUT …
3. If you are running R remotely, on an RStudio server through a web browser, you will need to upload the CSV file to the RStudio server. Here's how
   1. Go to the Files tab in RStudio and press the “Upload” button. (Look for the “Files” tab, not the “File” menu item at the top of the screen.)
   2. In the dialog window that appears, navigate to the CSV file you have just downloaded from Google spreadsheets. Upload this to the RStudio server.
   3. Going back to the RStudio console, use fetchData() with an empty argument and navigate to the file you have just uploaded to the RStudio server.

This is a pain, I know.