Extracting CSV Data From Trello

javascript

Trello is a great product and I use it to track our backlog, current items and completed tasks. For a free product, I can’t complain at all, but recently, I have wanted to take some of my done items and use them in some custom reporting. You know the kind of thing - stats for completed items, comparisons of categories of item, who did what etc. - but getting data out in the free plan is limited to JSON, when what I really wanted was CSV so that I could manipulate it more easily.

JSON is fine (and all the information is there) but to give you an idea of how much quantity I am dealing with, this image shows the JSON file in VS Code.

Clearly I wont be doing anything by hand and whilst there is a paid option, I want to stick to free for the moment, so I need another plan. This is why I knocked up this simple tool to extract the parts that I want, leaving the rest, presenting it in a CSV form. Let’s start with the HTML side of things. For this, I am going to need a box to paste the JSON, a button to invoke the magic, and another to present the decoded data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
<html>
    <head>
        <title>Trello JSON Decoder</title>
    </head>
    <body>
        <h1>Trello JSON Decoder</h1>
        <p>Enter JSON data:</p>
        <textarea id="json" cols="80" rows="15"></textarea>
        <br/>
        <br/>
        <button>Decode</button>
        <br/>
        <br/>
        <p>List of cards and their details:</p>
        <textarea id="output" cols="120" rows="15"></textarea>
    </body>
</html>

For the boxes, I have used text areas. You can see that the second one is slightly wider than the first; that’s deliberate because I am more interested in seeing that in a nice columnar fashion than the garbled JSON. In case you’re wondering, the size of the rows isn’t that important either - it will store whatever I put there. Lastly, notice the id fields - my first box is named “json“ and the output is “output“, so full-marks for originality :-)

OK, what next? How about making that button do something?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
function DecodeJson()
{
  // Extract the data from the text area
  var jsonText = document.getElementById("json").value.trim();
  if (jsonText === "") 
  {
    console.log("No data to parse.");
    return;
  }

  // Parse JSON data
  try
  {
    var json = JSON.parse(jsonText);
  }
  catch (e)
  {
    var error = "Oops. Problem parsing JSON data.";
    console.log (error);
    document.getElementById("output").value = error;
    return;
  }

  // These are our headers
  var output = "name, list, assigned-to, categories\\n";

  // Iterate over each card. They are stored as one large array, irrespective
  // of which list they are held under
  for (i = 0; i < json.cards.length; ++i)
  {
    // Name of card
    output = output + "\\"" + json.cards[i].name + "\\", " + json.cards[i].idList;

    // Person card assigned to - assuming there is only one because for me, that
    // is all there will be
    if (json.cards\[i\].idMembers[0] == undefined)
    {
        output = output + ", not-set";
    }
    else
    {
        output = output + ", " + json.cards[i].idMembers[0];
    }
    
    // Each card can have 0..n labels repesenting the type of card.
    for (j = 0; j < json.cards[i].labels.length; ++j)
    {
        output = output + ", " + json.cards[i].labels[j].name;
    }
    output = output + "\\n";

    // Place results into output area at the bottom
    document.getElementById("output").value = output;
  }
  console.log(json);
}

The above is fairly straight forward, but let’s pick on a few items to explain what they mean and the trade-offs I have chosen to make.

1
output = output + "\\"" + json.cards[i].name + "\\", " + json.cards[i].idList;

The JSON stores each card in an array, which is why I am looping over them. For each one, the card has a title (the thing at the top) so I take that by referencing the name field. idList is some kind of GUID and refers to the list within the Trello boards. In my case, I have lots and whilst this isn’t that useful, it does make it easier to see where one list end and another starts.

1
if (json.cards[i].idMembers[0] == undefined)

We only ever assign one person to a card so I am taking a slight liberty here and relying on that in so much as if there were two, I would miss the second person. That’s OK, though, because I am checking there is somebody attached. This again is a kind of GUID but if you look at your Trello board, you can work out who is who.

1
output = output + ", " + json.cards[i].labels[j].name;

Here I am after our labels. We are able to give something multiple labels, but generally don’t. So what does the output look like, generally?

name, list, assigned-to, categories
"#14471 - SP - Intranet", 59d7334676072906af60fe0d9, not-set, Work Request
"#10559 - SP - Staff Directory", 59d7314g45643d6af60fe0d9, not-set, Work Request
"#69577 - Images", 59d775a74f906af60fe0d9, 59db76072906a7ec972fde20, Support

Fancy a go? You can access my version here, but of course, if you modify my code, you can create your own. Specifically, there are many more fields that didn’t matter for my purposes and ways to convert some of those magic numbers into readable text (like list names), but this was only a hack for me. If you would like to traverse the JSON object, I have purposely sent it to the console, so press F12 in Chrome (or the equivalent) and take a look. Oh - don’t forget to extract the JSON - you can do that by opening Trello, then Show Menu (top right link) > More > Print and Export > Export JSON.

Hi! Did you find this useful or interesting? I have an email list coming soon, but in the meantime, if you ready anything you fancy chatting about, I would love to hear from you. You can contact me here or at stephen ‘at’ logicalmoon.com