Sunday, May 17, 2009

How to Convert a Google Spreadsheet into JSON, XML, and MySQL

Update: Google is deprecating Google Spreadsheets gadgets, as they announced in this post, so I am no longer updating or supporting them. I've written a post on a new technique here.

As some people know, I have a huge fetish for Google Spreadsheets - maybe because I'm always dealing with small datasets, and Spreadsheets is perfect for tinkering with them. Often, I start with my data in Spreadsheets, and later want to move it into a different static format - like a JSON file, MySQL data, or XML file. I originally did migration by concatenating column values together (e.g. ="" & A1 & ""), but I decided to make the process easier by creating a generic converter gadget. Using my gadget, you can easily convert any spreadsheet into those formats.

Note: I could get JSON and XML using the Spreadsheets data API, but then I would have to deal with alot of ATOM cruft when I'm only interested in the bare data.

Here are the steps for using the gadget:

  1. Create a new spreadsheet, and put your data in columns. Give each column a name, and choose carefully. Since this name will be used for MySQL attribute names, JSON keys, and XML tag names, the best names are lowercase, whitespace-less, and descriptive. The image below shows a spreadsheet of geocoded pizza locations:

  2. Click on the "Insert" menu and then select "Gadget...":

  3. This presents you with various categories of gadgets to choose from (similar to the iGoogle directory). My gadget isn't yet in the gallery, so you'll need to select "Custom" and then type in the URL to the gadget:

  4. The gadget will appear embedded in the current worksheet, and it will prompt you to select a range of data to send to the gadget. Select all the rows for the column with your address data, and you should see the Range text field update with the range. If it doesn't work, you can always manually type it in.

  5. You can now select either "JSON", "XML", or "SQL" from the dropdown, click "Apply", and see the generated output. To get the output in your clipboard, just double-click inside the frame, select all and copy.



You can see the published example spreadsheet, and see the XML output of the gadget in the second sheet. I generally move gadgets to their own sheet to make my workspace cleaner.

9 comments:

happyt said...

Wow, thanks for this. I didn't know (didn't look) that we get those formats. I tried setting up a spreadsheet in Google docs for use with the US Election data. General idea was for the data to then be available on an rss feed, but it didn’t quite work out like that. The problem with the rss feeds were that any commas in the spreadsheet were put into a comma separated item in the feed. Also when reading XML into Flex, it seemed to pick up words as tag names...confused... We used a semi manual version in the end, via XML.

Do we still need to manually republish the document after changes? This was another annoyance as we couldn't depend on the journo to export it correctly.

Thanks for the post. I will definitely have another go, this time with JSON into Flex...oops, sorry, Flash Builder.

Pamela Fox said...

I should make one distinction clear: By using the API, you can get dynamically updated versions of the data in JSON/RSS/ATOM. By using this gadget, you get a one-time export of the data in JSON/XML/SQL. So I use this when I want to use Spreadsheets for initial data collection, and then I'm ready to move to a completely different format.

For the API feeds, you should use the /public/values/ feed - then, you get each column as a separate XML node, and it's easier to parse.
Unfortunately, the API feeds aren't available from a domain with crossdomain.xml, so you'd have to proxy them in Flash. I'll remind the team that it'd be nice if that restriction didn't exist.

Goebel said...

Anyway to upload a .json format and convert it to .xml or .xls?

Sebastian said...

Nice

fan said...

this is great, thank you. However I have a question regarding the lat/long. Is it possible to keep the numbers as integers rather than "13.8890"? thanks.

Z said...

Totally awesome. Worked great first shot.

(Thanks)*10^6

Brian said...

Pamela,

Thanks! This is an awesome gadget... I'd like to request you make it more awesomer. JSON is what I want... but the JSON produced is kind of bloated in my case.

What I would like for it to produce is a nested JSON structure. My spreadsheet has col1 'category', col2 'username'. For purposes of putting users in categories, in the spreadsheet I have unique user names, but my rows have repeating category values, e.g.

catetory username
blue john
blue joe
blue kim
pink joy
pink andy
green brian

i would like for it to allow me to specify my upper level structure some way and produce a nested structure. For this example, I would like to have JSON produced like this:

[ {category: "blue"
,users : [
{username: "john"}
,{username: "joe"}
,{username: "kim"}
]
}
,{category: "pink"
,users : [
{username: "joy"}
,{username: "andy"}
]
}
,{category: "green"
,users : [
{username: "brian"}
]
}
]

instead, it produces a flat list, just like the rows and columns in the spreadsheet:

[
{category: "blue", username: "john"}
,{category: "blue", username: "joe"}
,{category: "blue", username: "kim"}
,{category: "pink", username: "joy"}
,{category: "pink", username: "andy"}
,{category: "green", username: "brian"}
]

is this possible? I guess I would need to indicate what I want my array names to be... (i.e. "categories":[] and "users": [])

Or maybe there is a way to do this if I structure my cell data properly?

Thanks!
Brian

Joe K. said...

Is anyone having trouble with this gadget? I was using it this weekend with no problem, and when I tried to convert a spread to JSON, I got a message saying "This gadget has no settings" and I get the AJAX spinning wheel.

Pamela Fox said...

@Joe - Are you using the old version of Spreadsheets? Gadgets don't work in the new version yet, so you have to click "old version" in the top right.

It seems to work fine for me in old version. Feel free to add me to sheet if that's not the problem (pamela.fox).

.