Programming Tech

wparser – PhantomJS based website parser

At the moment, I’m working on final exam and my topic is called “Modeling relation between technical parameters of web page design and its aesthetics“.

For one part of that project, I’ve needed a parser which will automatically pull some data from received list of urls. For this purpose, I’ve wrote PhantomJS based website parser called simply wparser. I’ve published that parser on Github since I thought that someone else can also find it useful and that maybe I can also get some experience from the community.

Anyway, feel free to go check it, report me about bugs, send me pull requests, give me some tips or whatever else you want.

Programming Tech

Get countries – World `countries` in MySQL or XML format

After my post from yesterday evening, WordPress analytics are telling me that almost 13,000 views (mostly from Hackernews) have seen that post in less then 24 hours. I don’t know about you, but for me, this is pretty amazing, and I’m very thankful.

To prove you that, I’ve made an upgrade to my script, and now you can get world countries in MySQL or XML format – hope you’ll like it.

I would also like to mention that you can suggest (in comments) any additional ideas, or that you can even implement it right away, push it to Github and send me a pull request.

Get countries program | @Github repository

Programming Tech

Countries database table for MySQL

So today, I was buzzing around and searching for a `countries` table script that I could use in MySQL database. As you can imagine, it wouldn’t make any sense to add all world countries into database table one-by-one.

I’ve found few interesting things around there, but all of those solutions were not flexible and were created for individual projects – and when I say that, I mean that some of those tables have only two columns (like country code and country name), some of those have 3 columns, and some of those have bunch of columns (that are probably not useful to many developers).

Anyway, after checking some of those solutions I came to idea that I could make something on my own – something that will be flexible and customizable. And the result was GetCountries “program” where you can select which columns do you want, click “Get it!” and the program will fetch “fresh” results from Geonames API and will generate you a SQL script. After that you can copy/paste that script and eventually change column names.

Hope someone will find that useful.

GetCountries app | code @ Github

Music Programming Tech

What the heck are they listening to?

Few months ago I’ve build a hack for a school project. It was made really quickly and I have never polish it up – until now.  I’ve wanted to mess around with Last.Fm API and Google Charts, and the result is called “What the heck are they listening to?“.

It’s works simple – when you click on any world’s country, it gives you top music tags (and a nice graph) for selected country.

Programming Tech

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

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);
  sheet.getRange(baserow, basecolumn, 1, 1).setValue(current_date.getDate() + "." + (current_date.getMonth()+1) + "." + current_date.getYear());

  // create 5min Trigger

  // create end Trigger
  var end_date_trigger = new Date(end_date_array[2], end_date_array[1] - 1, end_date_array[0], 10);

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)

  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,
  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;

    /* 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 = "",
      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++) {

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 @]

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
Programming Tech

Worshiping hack day

It’s been a few months since I’ve started to work at Zemanta as a part-time (I’m still studying, though) worker and all I can say it’s that I never thought job could be so interesting and that you can find such a nice people in one place.

Before I’ve even thought about working for Zemanta, Sam and Rok were talking me about all cool things there. One of those is hack day, and I’ve already heard about it before cause I know that companies like Dropbox, Twitter etc are doing it too. Anyway, I’ve never been involved in anything similar and I was totally thrilled with it.

Yesterday, I’ve been a part of Zemanta’s hack day and I’ve enjoyed it. Why? Bunch of people working on different projects (everyone in the same room), a lot of great ideas, cooperation’s, helping each others, joking, learning and at the end…presenting your hacks. I was working on a image search engine with Ron (based on Rok’s idea) and I had a lot of fun. Other projects were awesome and I’ve enjoyed watching everyone’s “final product”. I suppose there will be a hack day report at Zemanta’s blog so I’ll probably share it around.

Hack days are really something special and makes you feel totally alive. Only negative thing is that it’s hard to go back and carry on with learning for college exams, because you just want it more, and more :)