Friday, June 7, 2013

Exporting a Google Spreadsheet as JSON

I frequently use Google Spreadsheets as a lightweight database, by setting up some columns, encouraging my colleagues to update it, and subscribing to notifications of changes. Then I export the spreadsheet as JSON and update a json file in our codebase. Sometimes I also just use the jsonp output of a published spreadsheet, but if I'm worried about performance or the information getting mis-updated, then I'll use the export-and-update approach. In order to export it as JSON, I used to use a Google Spreadsheets Gadget but now that those are deprecated, I use a Google Apps Script.

Here are the steps for using the script:

  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 JSON keys, the best names are lowercase, whitespace-less, and descriptive. Freeze the first row with the column names. The image below shows a spreadsheet of books on my reading list:

  2. Go to Tools -> Script Editor, and it will open up a code editing interface in another window.

  3. Paste the JavaScript from this gist into the code editor.

  4. Reload the spreadsheet and notice a new menu shows up called "Export JSON". Click on that, and you'll see three options:
    • Export JSON for this sheet (with default configuration)
    • Export JSON for all sheets (with default configuration)
    • Configure export (where you can change the default configuration and what you want to export.)

  5. Once you click one of the export options, it will process for a few seconds and popup a textbox with the exported JSON. Now you can do whatever you'd like with that!

So there you have it. If you make any improvements to the script to make it more flexible or easier to use or better in any way, please let me know in the comments.

No comments: