Update: Google is deprecating Google Spreadsheets gadgets, as they announced in this post, so I am no longer updating or supporting them. If you're familiar with Google Apps Script, you can use this Apps script and run the
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. ="
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:
- 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:
- Click on the "Insert" menu and then select "Gadget...":
- 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:
- 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.
- 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.