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.
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.
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.
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.
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.
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.
Related articles
- Trellomania (restreaming.wordpress.com)
- Burning Down the Sprint (restreaming.wordpress.com)
- How to use Product-Burndown-Charts and Sprint-Burndown-Charts (not only) in SCRUM Projects? (dzone.com)
- Scrum in 5 Minutes (dzone.com)
- “How Do You Use Trello?” Contest Winners! (trello.com)
echo behind; 