Create your own “Burndown chart” using Trello API and Google Apps Script

If you or your team are into Scrum and if you’re using Trello for managing your tickets, but still didn’t get the best of Trello – then you’re on a right place. If you don’t have a clue what Scrum is, but you’re interested in that – maybe you should do a little bit of research if you want to get the best of this post-tutorial.

The beginning of Trello

Ok, so let’s begin the story…while I was working in Zemanta I first met Scrum method in practice. When I came there, we were using TicketTracker (custom made Zemanta’s employee Pančo), which was cool at the beginning, but somehow, folks didn’t get too used to it – at dailyies, there was always reminders about “Don’t forget to move your ticket in TicketTracker”.
Few months later, someone introduced Trello as an “awesome tool”, then someone else started to use Trello for managing backlogs, smaller teams tickets etc – at the end, the word about how “Trello is awesome” came to our “project leader” who immidiately fell in love with Trello. People were really excited about Trello, they were moving tickets like crazy, you should just see that. But still, we missed something, and that was a “Burndown chart“, which was introduced by TicketTracker and which was always at the main screen in the living room. So this is the part where I come in – since Trello didn’t have that feature and didn’t have any plugins for creating “burndown chart”, my job was to research Trello API (which is really detailed and well documented) and to create a burndown chart – so if you have find yourself in the similar position, I can somehow help you with that.

What will you need for that project?

  • Trello account and Trello API – get it here
  • Sprint board on Trello – create test one if you want to
  • Chrome (yes, you should use Google Chrome) extension name “Scrum for Trello” – for adding points to your ticket
  • Google account (for Google Docs)
  •  a little bit of JavaScript/Google Apps Script knowledge

Let’s start and setup a few things

First thing, get into your Trello account and request for your Trello API key.
Second, create a testing board inside your Trello dashboard – and make sure it has the same lists as one in the image below – add any tickets you want, add them as much as you want and make sure that you add all to “Planned” list at the beginning.

Testing sprint board in Trello

Third thing you need to do is to download “Scrum for Trello” (if you didn’t already) and to assign points to your tickets. Just head to your board, click to each of your cards, then click on a card title and then you’ll see Scrum points that you need to assign to your card. Do it for each of your tickets and after that you should get something like this.

Board with points assigned to cards

And fourth thing you need to do is to open your Google Documents and to create a new spreadsheet. Also, be sure you rename your “sheet” into something like “Sprint #active”. So, be sure you have #active tag inside your sheet name. After that, go to “Tools > Script Editor” where we’ll continue.

Let’s do some easy coding

I’ll introduce you functions in some running order and not in “priority” order – so don’t worry if you come almost to an end and you still don’t see a function that makes connection with Trello. First thing we need to do at the beginning of the script is to create something like this:

var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets(),
    active_sheet = "",
    sprint_info_column = 10;

for (var i=0; i<sheets.length; i++) {
  if (sheets[i].getName().indexOf("#active") != -1)
    active_sheet = sheets[i].getName();
}

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(active_sheet);

What exactly we do here is that we get all Sheets from the current Spreadsheet, we create variable active_sheet where we’ll store active sheet and we create sprint_info_column where we store a number of info column. Info column will be a column (inside sheet) where we’ll put Board information – so we can grab all our data. In our example this is 10th column, which is under J letter, so we’ll just add info in this order:

Sprint # // Sprint number – whatever you want
Name of sprint // Sprint name - whatever you want
21.6.2012 // Sprint start date – in that format
5.7.2012 // Sprint end date - in that format
board_id // board_id. Get it from your board URL format which looks like this https://trello.com/board/sprint-june-2012-21-6-5-7/board_id

Next thing we need to do is to create a function that will prepare as a table and few other things – we’ll call this function sprintStart() and it’ll look something like that (if you run it currently it won’t work, so wait until we finish few other things):

/* generate dates from start to end of a sprint */
function sprintStart() {
  var baserow = 2,
      basecolumn = 1;
  var start_date_array = sheet.getRange(3, sprint_info_column).getValue().split("."),
      end_date_array = sheet.getRange(4, sprint_info_column).getValue().split(".");
  var start_date = new Date(start_date_array[2], parseInt(start_date_array[1])-1, parseInt(start_date_array[0])),
      end_date = new Date(end_date_array[2], parseInt(end_date_array[1])-1, parseInt(end_date_array[0]));
  var current_date = start_date;

  // generate headers
  sheet.getRange(baserow - 1, basecolumn, 1, 6).setValues([['Date', 'Score', 'Fires', 'Score done', 'Fires done', 'Total score']]);

  // generate dates
  while (current_date.getTime() != end_date.getTime()) {
    sheet.getRange(baserow, basecolumn, 1, 1).setValue(current_date.getDate() + "." + (current_date.getMonth()+1) + "." + current_date.getYear());
    current_date.setDate(current_date.getDate() + 1);
    baserow++;
  }
  sheet.getRange(baserow, basecolumn, 1, 1).setValue(current_date.getDate() + "." + (current_date.getMonth()+1) + "." + current_date.getYear());

  // create 5min Trigger
  ScriptApp.newTrigger("fetchData").timeBased().everyMinutes(5).create();

  // create end Trigger
  var end_date_trigger = new Date(end_date_array[2], end_date_array[1] - 1, end_date_array[0], 10);
  ScriptApp.newTrigger("sprintEnd").timeBased().at(end_date_trigger).create();
}

What we do here is – we get the sprint start_date and sprint end_date from info column, then we create header titles inside our spreadsheet, then we create rows for each date from start_date to end_date and at the end we create some triggers that we’ll mention a little bit after. Since we still miss some 2 other function that we need those triggers for, you can just put last three statements (for creating triggers) under comment and try to run that script.
If you’re not familiar with triggers inside Google Apps Script, you should check this.

You can run your script by selecting it and then by clicking “Run”. After that, in your spreadsheet you should get something like this.

Spreadsheet look after running sprintStart() function

Now, we must create that function fetchData() that will grab all we need from our board. It should look like this:

/* fetch data from Trello and append it to Spreadsheet */
function fetchData() {
  var data,
      baserow = 2,
      basecolumn = 1,
      datenow = new Date(),
      today = datenow.getDate() + "." + (datenow.getMonth()+1) + "." + datenow.getYear(),
      active_sprint_id = sheet.getRange(5, 10).getValue();

  while (sheet.getRange(baserow, basecolumn).getValue() != "") {
    if (sheet.getRange(baserow, basecolumn).getValue() == today)
      break;
    baserow++;
  }

  data = getActiveSprintData(active_sprint_id);
  sheet.getRange(baserow, basecolumn + 1, 1, 5).setValues(data);
}

What we do here is that we grab data for currently active sprint – and we do that by calling a function getActiveSprintData() which will be introduced right after. After we grab the data inside data variable, then we just add it to current baserow and basecolumn. As you can see, before that, we’re settling our baserow so we make sure that we don’t add data about today’s statistics into wrong row – this will be easier to understand later, after I show you all together.

Next thing is getActiveSprintData() that will look like this:

function getActiveSprintData(active_sprint_id) {
  var active_sprint_lists,
      active_sprint_cards,
      done_list_id,
      fires_done_list_id;
  var points = [],
      number_of_stories = 0,
      points_all = 0,
      points_left = 0,
      points_fires = 0,
      points_fires_left = 0,
      points_fires_done = 0,
      points_done = 0;

  active_sprint_lists = trelloFetch("/boards/" + active_sprint_id + "/lists/");
  active_sprint_cards = trelloFetch("/boards/" + active_sprint_id + "/cards/");

  for (var i=0; i<active_sprint_lists.length; i++) {
    if (active_sprint_lists[i].name == "Done")
      done_list_id = active_sprint_lists[i].id;
    if (active_sprint_lists[i].name.indexOf("Fires") != -1)
      fires_done_list_id = active_sprint_lists[i].id;
  }

  for (var i=0; i<active_sprint_cards.length; i++) {
    var regex = /\((\d+)\)/;
    var story_point = active_sprint_cards[i].name.match(regex);

    story_point = story_point ? parseInt(story_point[1]) : 0;
    points_all += story_point;
    number_of_stories++;

    /* count fires separately and remove them from points_all */
    if (active_sprint_cards[i].labels[0]) {
      for (var j=0; j<active_sprint_cards[i].labels.length; j++) {
        if (active_sprint_cards[i].labels[j]['name'] == "Fires") {
          points_fires += story_point;
          points_all -= story_point;
        }
      }
    }

    /* count finished stories */
    if (active_sprint_cards[i].idList == done_list_id) {
      points_done += story_point;
    }

    /* count finished fires */
    if (active_sprint_cards[i].idList == fires_done_list_id) {
      points_fires_done += story_point;
    }
  }

  points_left = points_all - points_done;
  //points_fires_left = points_fires - points_fires_done;
  points[0] = [points_left, points_fires, points_done, points_fires_done, points_all];

  return points;
}

So we get all lists from our board and we get all the cards. For each of those, we check where is particular card belong to. If it’s “regular”, if it’s a fire (tickets that were not planned), if it’s in Done etc. Depending on that, we’re summing up points which will be presented in our spreadsheet.

Now we need to more functions and one of them is trelloFetch():

function trelloFetch(url) {
  var key = "your_trello_API_key",
      api_endpoint = "https://api.trello.com/1",
      member_token = "your_member_TOKEN";
  var completeUrl = api_endpoint + url + "?key=" + key + "&token=" + member_token;

  var jsonData = UrlFetchApp.fetch(completeUrl);
  var object = Utilities.jsonParse(jsonData.getContentText());

  return object;
}

This one will be used just to fetch data from Trello. You need trello API key and member token (mentioned at the beginning). After that we make completeUrl and we get data via UrlFetchApp.fetch() – yeah, in Google Apps Script is easy as that.

For the end, there’s one more function that will kill everything after sprint ends (so the script doesn’t continue to run):

/* remove all triggers when it comes to the end of a Sprint */
function sprintEnd() {
  var triggers = ScriptApp.getScriptTriggers();
  for (var i = 0; i < triggers.length; i++) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
}

And that’s all about coding! If you’ve done everything as it was said, you can now run your sprintStart() function (be sure to remove comments from statements where you create triggers) which will create other triggers and your script will be running and working as it should. fetchData() will run every 5 minutes and after the first execution it will look something like example in the image below. Since today is 28th of June I’ve added some test data for all previous days, and I’ve moved one ticket into “Done” list.

Your spreadsheet, after everything is done

There’s one problem though – at that is. If you finish any story at the first day of the Sprint, then chart won’t look good – it’ll start from “current” number of points and not from total number of points.

If you feel you didn’t understand everything very well or that you’ve made some mistakes, here’s complete code available for download.

[complete code @ pastie.org]

Finish up your burndown chart

And to finish up your burndown chart, inside your spreadsheet, you do next:

  • select “Date”, “Score” and “Fires” columns
  • click Insert > Chart
  • choose your chart style (my suggestion is line chart) and that’s it

At the end, you’ll get burndown chart like this, which can be published to it’s own sheet and customized however you want.

the Burndown Chart

About these ads

Tags: , , , , , , , ,

14 responses to “Create your own “Burndown chart” using Trello API and Google Apps Script”

  1. Swen says :

    Nice post Dražen!

  2. Recover Twitter Account says :

    Nice answers in return of this difficulty with solid arguments
    and telling the whole thing concerning that.

  3. Sean Colombo says :

    Another option that might make it easier for you, is that now there’s a third-party app for Burndown charts in Trello.

    You don’t _have_ to use “Scrum for Trello” but if you have the Pro version of “Burndown for Trello”, you can automatically pull in the estimates from SfT.

    If you get a chance to check it out, please let me know what you think :)
    https://BurndownForTrello.com

  4. usb battery chargers says :

    Hey, I need to spend some time learning much more about topics like this.
    Thanks for fantastic info I was looking for this information for one of my
    school assigments. I will com back to read more. Cheers.
    Very much appreciated information!

  5. Angela says :

    Having been just browsing for helpful blogs intended
    for the project research when I happened to find yours. Many thanks for this useful material!
    Cheers!

  6. 12v solar battery charger reviews says :

    When I initially commented I clicked the “Notify me when new comments are added” checkbox and now each time a comment is
    added I get three emails with the same comment.
    Is there any way you can remove people from that service?
    Cheers!

  7. Noel Rocha says :

    What does it means “Fires no more”?

    • dperitch says :

      Since we were handling “Fires” different from other tickets, this is where we’ve put them after they were done. Just to not mix them with other tickets.

  8. Thiago Lara says :

    Hey, my code is in infinite loop after the first “run” :/ … what is ? do you know?

  9. Will says :

    Just chimming in to say that this a fantastic little script and very well explained!

    Quick suggestion (as I have just added a little sheets formula, that I’m sure I
    could serve with an amendment to the script): what is really useful is having a target line on the burndown chart (what velocity would burn the effort down to zero).

    This is simply – total effort at start-cumulative effort/total number of days in the sprint.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: