Online Scrum Tools – Part 4 – Trello Backup

Posted on by

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.

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:

  1. ID – The card ID
  2. Title – The text on the front of the card
  3. User Story – The description of the card
  4. Acceptance – The checklist items for a checklist called “Acceptance Criteria”.
  5. Story Points – The story points allocated to the card when using the Scrum For Trello Chrome extension
  6. Card Status – Displays whether the card is open or closed (archived)
  7. List – The name of the list on which the card resides.
  8. Lists Status – The status of that list (Open or Closed)
  9. Checklists – Any other checklists on the card that aren’t named “Acceptance Criteria”.
  10. Labels – The label colour and description of any labels assigned to the card
  11. Attachments – The links to any attachments added to the card
  12. Actions – Any comments added to the card. Note: The import is limited to the most recent 1000 comments made on the board.
  13. Members – The members currently assigned to the card.
  14. 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.

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:

  1. We set up the board in Trello with the stories that we’re going to be working on
  2. I open the spreadsheet and select the “Backup open boards” from the menu to refresh the data.
  3. 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.

 

Posted in: Scrum, Trello

22 thoughts on “Online Scrum Tools – Part 4 – Trello Backup”

      • Hey thanks for the reply.

        In trello each card can be assigned a ‘due date’

        I was wondering how to get the due dates of each card through to a column.

        Thanks,

        Will

        • Hi Will,

          It’s pretty easy, just add the Due Date column to the end of the Backlog Template sheet, and then replace the existing populateBoardSheet() function in the App Script with the following:

          function populateBoardSheet(row, boardID, boardName, cards, lists, checklists, actions, members) {

          var board = createBoardBackupSheet(row, boardName);

          var listIds = new Array();
          var checklistIds = new Array();
          var actionCardIds = new Array();

          // Configure lookup arrays;
          for (var i=0; i<lists.length; i++) {listIds.push( lists[i].id);}
          for (i=0;i<checklists.length;i++) {checklistIds.push( checklists[i].id);}
          for (i=0;i<actions.length;i++) {actionCardIds.push( actions[i].data.card.id);}

          var row = 1;
          for (i = 0; i < cards.length;i++) {

          var card = cards[i];
          var name = "";
          var storyPoints = "";
          var acceptanceCriteria = "";
          var otherChecklists = "";
          var dueDate = "";
          if (card.due != null) {
          dueDate = card.due;
          }

          nap = parseCardName(card.name);
          cls = parseCardChecklists(card, checklists, checklistIds);
          comments = parseActionComments(card, actions, actionCardIds);
          var list = parseCardList(card, lists, listIds);
          if (card.closed) {var cardStatus = "closed";} else {cardStatus = "open"};

          row++;
          var rowData = [[card.idShort
          ,nap.name
          ,card.desc
          ,cls.acceptance
          ,nap.points
          ,cardStatus
          ,list.name
          ,list.status
          ,cls.other
          ,parseLabels( card.labels)
          ,parseAttachments( card.attachments)
          ,parseActionComments( card, actions, actionCardIds)
          ,parseMembers(card,members)
          ,"https://trello.com/card/" + "nobodyCaresWhatThisBitIs/"+ boardID + "/" + card.idShort
          ,dueDate
          ]];

          board.getRange(row, 1, 1, 15).setValues(rowData);

          }
          }

          I\'ll try to update the template when I get some spare time.

  2. Hey I gave it it a try but getting and errors around these lines:

    ——

    // Configure lookup arrays;
    for (var i=0; i for (i=0; i for (i=0;i

    ——

    I tried hacking a wee bit to no avail… maybe something to do with a parse function for the date,
    I dunno ? – I’m no expert…

    I must also say what a great job you did writing this, well done! and thanks for your ‘support’, I really appreciate it =)

    • Hey Will,

      I’ve updated the script (http://bit.ly/SQIWdk) to include the due date – it seems to work, although I did get a message about having to refresh the page halfway through, but it didn’t seem to stop the script from working.

  3. Hey Iain, your a legend, and I didnt realise we are on living on the same island!

    I got a pretty good strike rate (like 1 error to 5 attempts – otherwise it worked perfectly)

    I have been adapting your script, I’m using the story points as a numerical data, and the acceptance criteria as an additional means of storing and separating values, in the aim of generating burndowns of multiple time values.

    Everything works great except the lil square check box symbol that comes in as part of the checklist information.

    Do you know of anyway to remove it before it hits the sheet?

    I really appreciate your help, your scripts are the closest thing I have found to doing what I need and I’d like to try and support your cause so let me know if i can donate or something.

    • Hi Will,

      The checkboxes are set in this function:

      function getCheckListItemState(completed, id) {

      if (completed.length > 0 && completed.indexOf(id) != -1) {
      return “u2611 “;
      }
      else {
      return “u2610 “;
      }

      }

      It’s either an empty box, or a box with a tick in it if the item has been ticked off.

      You could return “” in both cases if you don’t care what the state of the checklist item is.

  4. Hi Iain – thanks for the resource; i’m really excited to take ‘er for a spin. Sorry if I’m being dense, but one part of the instructions in particular trips me up:

    ” Select “Backup open boards” from the Trello menu in this spreadsheet.”

    I see no such menu. I went to the script manager in the Tools menu and tried to run backupAll to no avail. What am I doing wrong? To confirm, yes i have:
    – made my own copy of the sheet
    – generated a unique token for the spreadsheet
    – filled in my key and token in the appropriate fields

    Thanks again for putting this together!

  5. Bah – on close and re-open, it’s now appeared. Still struggling to get sheet to work – says “ERROR:Values in the Control sheet have not been set. Please fix the following error: App Key not found” which is just factually inaccurate! I’ll keep wrestling with it – sorry for the false alarm.

    • Hi David,

      There was a problem looking for the App key, as I added a new row in the control sheet. It should now be fixed. Do you want to take a new copy of the spreadsheet and try again.

      Cheers,

      Iain

  7. Hi Iain, thanks so much this is really great. Just one question – is it possible to just backup one board onto the spreadsheet? I have a “Work” board which I wish to share with my team, and don’t wish to include my “Personal” board.

    Thanks!

    • Hi Neil,

      There’s lots of ways of achieving this:

      1. Create a new account in Trello that only has access to your work board, and use it’s credentials to run the backup.

      2. Amend the script to only process a particular board ID:

      This is done by wrapping an if statement around the call to backup() function in scheduledBackup().

      Here’s the new version of the function in it’s entirety – just change the id to be the id of the board you want to back up – you’ll find it in column A of the Dump worksheet.

      function scheduledBackup() {

      var boards = listCurrentUserBoards();
      var sheet = SpreadsheetApp. getActiveSpreadsheet(). getSheetByName(\"Dump\");
      var lr = sheet.getLastRow();
      if (lr > 1) {
      var ids = sheet.getRange(1,1,lr,1).getValues();
      }
      else {
      ids = [];
      }

      for (index=0;index<boards.length;index++) {
      var row = 0;
      for (var j=0;j<ids.length;j++) {
      if (ids[j][0] == boards[index].id) {
      row = j+1;
      break;
      }
      }

      if (row <= 0) {
      row = lr+1;
      lr ++;
      }

      if (boards[index].id == \"4f8d6f95642dc5625b1efda6\") {
      backup(boards[index].name, boards[index].id, row);
      }

      }

  9. Good to see NZers i’m in Wellington! Very nice script! In my case I just wanted a simple copy of the JSON file for all boards, so I could always easily get the data in case of problem.
    so I wrote a small script that downloads .json files automatically for all your boards: https://github.com/mattab/trello-backup

    I setup the script to run as a daily task and all my valuable trello boards/cards/lists are automatically saved on my computer. But certainly your use case of having in spreadsheet is the next step!

  10. thanks!!

    have been using zapier + gdocs as a backup but this is better

    question: can you include card comments in the backup?

    cheers

    justin

  12. Thanks Iain, just what I’ve been looking for. I’d like to be able to print checklists on the generated cards for use on a physical board (which can be limited to the first checklist and a maximum of 6 or 7 lines), and print 4 cards per A4 page. Is that possible?

    • Hi Hart,

      What you’re suggesting is possible, but it’s quite specific to what you’re doing, and would be difficult to code for without seeing your data or printer setup.

      If you’ve got someone slightly technical, I’d get them to look at the cards.gs part of the script, and have a play around – it’s fairly straight forward to follow, even if you don’t know any javascript.

      Cheers,

      Iain

Leave a Reply

Your email address will not be published. Required fields are marked *

3,639 Spam Comments Blocked so far by Spam Free Wordpress

HTML tags are not allowed.

Featured App

Tooth Camp: Helping Customers Last 2 Minutes Since 2010

Picture of Tooth Camp app