Friday, June 7, 2013

Exporting a Google Spreadsheet as JSON

⚠️ Update (Apr 2023): I do not actively maintain this script, so it may not work. See my recent post on using Neptyne as another approach.

I often use Google Spreadsheets as a lightweight database, by setting up 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. It's also possible to use the jsonp output of a published spreadsheet for dynamically updated JSON, but when I'm worried about performance or the information getting mis-updated, I use the export-and-update approach.

In order to export it as JSON, 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. 
    Screenshot of spreadsheet with reading list
  2. Go to Tools → Script Editor, and it will open up a code editing interface in another window. 
    Screenshot of Google spreadsheets UI, Tools menu and Script editor option
  3. Paste the JavaScript from this gist into the code editor. 
    Screenshot of Google Apps Script editor with code inside it
  4. Reload the spreadsheet and notice a new menu shows up called "Export JSON". Click on that, and you'll see two options:
    1. Export JSON for this sheet (with default configuration)
    2. Export JSON for all sheets (with default configuration)
      Screenshot of Google spreadsheet with Export JSON menu
  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!
    Screenshot of exported JSON for Google spreadsheet

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.

1 comment:

Isaac Tait said...

The New Stack hijacked your post https://thenewstack.io/how-to-convert-google-spreadsheet-to-json-formatted-text/