Making sense of Trello's JSON export

Trello's  export tools offer the option to export your data as JavaScript Object Notation (JSON). JSON is primarily intended as a data format that machines can interpret and use. As a result although a JSON file is somewhat human readable, it's not as easy to parse as what you might expect from an Excel or CSV file. For example, information about a card might be stored in JSON as:

"card": {
	"_id": "5446843ef9fea260ab93b704",
	"shortLink": "LrrmgFyd",
	"idShort": 19,
	"name": "Get milk from the.",
	"id": "5446843ef9fea260ab93b704"
},

In this example, information about the card (its ID, the shortlink parameter used for its URL, and its name) is stored as nested information within the card key, as a set of names ("id") and values ("5446843ef9fea260ab93b704").

Viewing JSON in the browser

While JSON isn't the easiest thing to parse, it's much easier to parse if you have the right extension. If you're looking to simply browse the data on a board, this may be the way to go. Several browsers offer ways to make the JSON export that Trello offers more human-readable, by automatically indenting text and including line breaks. That way, the text appears more like:

JSON with the Chrome JSONView extension enabled.

than:

JSON without the Chrome JSONView extension.

For Chrome, we recommend the  JSONView extension. There's also a JSONView add-on for Firefox.

One-to-many data structures

One of the difficulties with making Trello's JSON human-readable is that in many cases, there are multiple pieces of information nested within a single structure. For example, for a single card, you can have information about what's currently on the card, a history of what was previously on the card, information about changes that have been made to the card (moves between lists), and information about structures that are themselves nested (like checklist items within a checklist on a single card). 

That information is hard to represent e.g. in a single row in Excel, since some of it contains information about actions over time (movement) and some contains information about static information (the content of new checklist items, for example). That nesting and one-to-many nature of the data is why some of this information isn't included on Trello's CSV export.

Converting JSON to CSV

If you're not working with a Business Class team, or want to pull information from the JSON that isn't included in the Business Class CSV export, the first thing we'd suggest is checking out this Trello and Google Sheets integration with Zapier:  https://zapier.com/zapbook/trello/google-sheets.

Here are some Zaps you can use to create new rows on a Google spreadsheet from different Trello activity:

You can then download a Google Sheet as a CSV if you like!

You may run into a situation where you need to use Trello's JSON export to locate a piece of information. To do this, we suggest reaching out to a developer—JSON, particularly when it's covering a whole board, can be complicated to parse out correctly. If a developer isn't available, you may want to try using an online JSON-to-CSV converter, such as:

Because of the one-to-many nature of Trello data (one card can map to several pieces of information—multiple comments, checklist items, etc.), the converted CSV usually isn't usable as a document to present to others. However, if you're trying to extract a single piece of information from the JSON these converters can make that task a little easier. We recommend knowing what information you're looking for going in, and using the "find" feature in your spreadsheet viewer to find it—that will typically be easier than looking for it yourself.

Still have questions?

Totally understandable! If you still have questions, reach out to  Trello Support. Provide information about what you're looking for on your export, and upload your JSON file if possible. We can help you out.