So you've imported your precious data into Trello and are now merrily moving cards, ticking checklists, and adding comments 'til the cows come home. But what if something were to happen to your Trello data? How much work and knowledge would be lost?

This was a question I asked myself when I initially decided to use Trello. Having lived through a disaster (The 2011 Christchurch Earthquake) I tend to think a lot about disaster recovery, and the thought of having data sitting in the cloud, especially in a free service makes me nervous. Don't get me wrong, I think that the guys at Fog Creek Software do their utmost to keep our data safe and sound, but I would sleep a lot easier knowing that there was another copy of my data somewhere, should I need it.

Trello offer an option to do a JSON export for a board, which I did use for a while, but I had to keep remembering to do it, so it wasn't the perfect solution.

Then it hit me, what better way to back up your free-to-use-cloud-service data, than to import it into another free-to-use-cloud-service! So I set about using the Trello Api to import the data into a Google Spreadsheet using Google Apps Script. The script enabled me to do a few great things:

Schedule the import on a regular basis
Convert the data into a spreadsheet format that can be filtered and sliced and diced to your hearts content
Use another script to generate User Story Cards.

The Spreadsheet

I've made the spreadsheet available in Google Docs for anyone to use - here's how it works:

The setup

There are instructions in the "Control" sheet to set up your Trello Api Key and Token. Once entered, you'll be able to use the menu option to back up all the open boards that you are a member of.

The Dump

The first part of the import for each board is to put a JSON dump of the card and checklist data into a "Dump" sheet. This data is unformatted, and not really useful other than as a data source for the next step of the import. Note: It does not contain every single piece of data required to rebuild an exact representation of the board, but it should be close enough for most people.

The description, Id, and time of the last backup is recorded for each board.

Dump Sheet

The Board Sheets

The next part of the import process is to parse the JSON data, and put it into a worksheet. The name of the sheet that is created is the board description prefixed with a number indicating it's sequence in the Dump sheet.

The headings added to each board sheet are stored in the Backlog Template sheet, meaning that they can be easily changed if required.

The data used to populate the columns are as follows:

ID - The card ID
Title - The text on the front of the card
User Story - The description of the card
Acceptance - The checklist items for a checklist called "Acceptance Criteria".
Story Points - The story points allocated to the card when using the Scrum For Trello Chrome extension
Card Status - Displays whether the card is open or closed (archived)
List - The name of the list on which the card resides.
Lists Status - The status of that list (Open or Closed)
Checklists - Any other checklists on the card that aren't named "Acceptance Criteria".
Labels - The label colour and description of any labels assigned to the card
Attachments - The links to any attachments added to the card
Actions - Any comments added to the card. Note: The import is limited to the most recent 1000 comments made on the board.
Members - The members currently assigned to the card.
Trello URL - The URL to the card in Trello

The Extras

If you're using Trello to store user story data, you can use the Story Cards menu option to generate cards that can be printed for use during planning sessions.

Automating The Backup

You automate the backup process by selecting Tools->Script Editor to open the Google Apps Script window, and then select Resources->Current script's triggers.

You then add a new timed trigger to do your backup - I set mine to 4 hours which is often enough for me.

Scheduling Backup

The Wrap Up

And here you have it.

The spreadsheet import has worked really well so far: The Google Apps Script has only timed out twice in a couple of months, plus you get a nice email from Google even if it does, which is reassuring.

As well as constantly backing up the boards, I use the spreadsheet regularly to create the story cards we use during sprint planning:

We set up the board in Trello with the stories that we're going to be working on
I open the spreadsheet and select the "Backup open boards" from the menu to refresh the data.
Once the script completes I go to the required board sheet, filter on the list that we're working on, and then generate the story cards for just those entries.

Feel free to copy and make any improvements to the spreadsheet - if you're keen to share, let me know and I'll look at merging any changes into this copy.

** Update 01/11/12 ** For anyone wondering whether this is truly necessary, I can safely say that the backup spreadsheet justified it's existence this week.

** Update 23/04/13 ** Google must have made a change that meant that the headers for each sheet were not being created. This has now been fixed, so you'll just have to make a new copy of the original spreadsheet to pick up the changes.

*** Update 7th November 2013 - There is now another script available to Pimp Your Trello Card and automatically add Google Docs to your cards, Checklists, Descriptions, or even assign costs to your cards. ***

*** Update 11/11/2013 Trello changed something on their API today, and it has resulted in the script failing with the following error:

TypeError: Cannot read property "length" from null. (line 210, file "Code")

I've fixed the error in the script, but to fix your version you will need to copy the script source from the new version of the spreadsheet into your copy, and save the update. Note:You will need to copy my spreadsheet to be able to view the script source, which you can then view by selecting Tools->Script Editor from the menu of the spreadsheet **

*** Update 22/03/2014 Version 2.0 of the script is now available. Details here:

http://www.littlebluemonkey.com/blog/flexible-trello-backup-and-story-card-printing

*** Update 23/11/2014 Version 3.0 now available. Details here:

http://www.littlebluemonkey.com/blog/trello-backup-30