In a previous post I explained how to extract JSON data from a Google Spreadsheet via an API call.
However, when you actually get the data, the JSON isn’t really in the kind of structure you would imagine. Instead of a matrix of rows and columns, Google returns an RSS-style linear feed of “entries” for all of the cells!
So how to convert that into something that you can use in D3.js or R?
We need to iterate over each entry in the feed, and push the values into an array, moving to a new “line” in the array each time we get to a cell that is at the beginning of a row in the spreadsheet. I’ve written a JavaScript function to do the work necessary; you can get the code on Github.
Running this function we can then get the values from the resulting array using something like:
data[1][5]
Note that the function doesn’t differentiate the labels from a header row (which is something you’d commonly see, and which R would usually expect) so there is definitely room for improvement in the function.