I have been using Trello for several months and I routinely post activity on cards as I work on them and then move them left to right to the 'Complete' list. I don't leverage due dates. Is there a mechanism to list the activity text on all cards for a given range. I'm trying to pull some raw data to feed a status report for a given period.

Example: If present day is May 15th, 2013. I want to produce a list of all activity text on each card that had 'activity' entries for the period April 29th - May 3rd.

  • This can be done, but only by using the Trello API. Are you comfortable doing some light programming to get the data you want? – Ian Henry May 22 '13 at 19:45
  • @IanHenry Yes I am comfortable. – Brian May 24 '13 at 16:18

Yes, it is, using the Trello API and a few other tools.

This answer relies on HTTPie and jq, two freely available tools that can be installed via pip and Homebrew if you're using a Mac:

$ pip install httpie
$ brew install jq

Using HTTPie we can query the Trello API to get the raw action feed for a board, then we can use jq to turn that data into something useful.

Let's start with something "simple." The following command will give us all the comments made on the Trello Dev Board in the month of April 2013. I'll explain this in a second:

http GET "https://api.trello.com/1/boards/4d5ea62fd76aa1136000000c/actions" "since==Apr 1 2013 EDT" "before==May 1 2013 EDT" "limit==1000" "filter==commentCard" |  jq 'group_by(.data.card.id) | map({key: (.[0].data.card | "\(.name) (\(.id))"), value: map({date, member: .memberCreator.fullName, comment: .data.text}) }) | from_entries'

If everything went according to plan, we should see something like this:

  "Embed All The Things (516fcff9b998572923008fb2)": [
      "comment": "Embed.ly now supports https better, so maybe we can upgrade to that version. http://embed.ly/embed/security/ssl",
      "member": "Brett Kiefer",
      "date": "2013-04-26T16:15:21.408Z"
      "comment": "Embedding a Google Map would be nice.",
      "member": "Michael Warkentin",
      "date": "2013-04-24T18:39:12.155Z"
      "comment": "Github issues / pull requests",
      "member": "Michael Warkentin",

Cool. That's a single, reasonable JSON object that we can easily parse into any number of other formats. Now let's step through that so we can understand it well enough to modify it to suit our needs.

http GET "https://api.trello.com/1/boards/4d5ea62fd76aa1136000000c/actions" "since==Apr 1 2013 EDT" "before==May 1 2013 EDT" "limit==1000" "filter==commentCard"

This is the only part that depends on Trello. We make a request against the public API for the board with id 4d5ea62fd76aa1136000000c -- I got that by going to https://trello.com/dev and looking at the id Trello appends to the full URL (https://trello.com/board/trello-development/4d5ea62fd76aa1136000000c).

The since and before fields are self-explanatory. I specify a limit of 1000 because that's the largest response Trello will allow. If your board has more than a thousand comments in the relevant date range, a more complicated paging solution would be required here. I specify a filter of commentCard because I'm only interested in those for the purposes of this answer. If you want more action types, specify a comma-separated list like filter==commentCard,updateCard:idList,createCard. Valid action types can be found in the Trello API reference.

If we run that by itself, we'll get a lot of info that's relatively hard to make sense of. So we pipe it into jq to massage it into something a little more useful.

jq 'group_by(.data.card.id) | map({key: (.[0].data.card | "\(.name) (\(.id))"), value: map({date, member: .memberCreator.fullName, comment: .data.text}) }) | from_entries'

Piece-by-piece, our jq script is doing the following transformations, each one piping its result to the next operator:

  • group_by(.data.card.id)
    • Trello just gives us an array of actions. We turn that into an array of arrays, where each sub array contains only actions for a given card. Basically [[card1_action1, card1_action2...], [card2_action1, card2_action2...], ...]
  • map({key: KEY_EXPRESSION, value: VALUE_EXPRESSION}) | from_entries
    • We don't want an array of arrays; we just want an object of the form { card1: [action1, action2, ...], card2: [action1, action2, ...], ...}. By turning the array into an array of key-value pairs, we can use from_entries to turn that into an object. Cool.
  • Now let's look at what I called KEY_EXPRESSION above: (.[0].data.card | "\(.name) (\(.id))")
    • This is fairly simple. We take the card entry from the first action (since it should be the same for all actions, we could have picked any, but the first seems like the sane choice). Then we use string interpolation (\(...)) to construct something that looks like "name (id)".
  • VALUE_EXPRESSION is map({date, member: .memberCreator.fullName, comment: .data.text})
    • We could have just used . to get the array of all actions unchanged. But since actions are kinda ugly, we're massaging them into something useful by mapping over the array and applying {date, member: .memberCreator.fullName, comment: .data.text} to each individual action object.
      • {date} is the same as {date: date} to jq.
      • Everything else is pretty self-explanatory. We now have the date, member (just their name, but it would be easy to get more), and the text of the comment.

So there you have it. Er, hopefully. We could've done this data massaging in any scripting language, but this is exactly what jq was built for, so it's a good excuse to learn a cool new tool. Check out the jq manual for more.

Now, this works because the Trello Dev Board is public. But what if we want private data?

The right way to do this is to generate an API token. The Trello API Getting Started Guide has a detailed runthrough of how to do this. But we're in a hurry, so we're gonna do it the lazy man's way...

Log in to http://trello.com in Chrome and open the console (View > Developer > JavaScript Console). Type $.cookie('token') into the window. This will spit back something like "uniquememberid/somegarbledstring". Copy the part between the quotes, and modify the request to be something like this:

http GET "https://api.trello.com/1/boards/THE_ID_OF_THE_PRIVATE_BOARD_YOU_WANT/actions" "Cookie:token=uniquememberid/somegarbledstring" "since==Apr 1 2013 EDT" "before==May 1 2013 EDT" "limit==1000" "filter==commentCard" | jq ...

The only thing we've change is the adding the "Cookie:token=uniquememberid/somegarbledstring" header. This will make Trello use the token. NOTE that that token is very private...if you give it to someone else, they can basically log in as you until you revoke it at the Trello Account Page. So, you know, be careful. Or go through the API key/token generation step.

Now modify away to get the precise data that you need in the format you want.

| improve this answer | |

I created a tool called reportsfortrello.com that will show you how long a card was in a list during a range of time.

It is free and allows you to look 1000 actions back per board. It can also track card memberships.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.