My Trello Weekly Summary

I’m trying to get myself to better recognize work I’ve completed, so I wrote a process to look at my Trello board and send a Slack message to myself. This is why and how.

I used to write a lot about Trello.  The API, how I use it, how my day-job teams were using it.  It’s been five years since I’ve said anything new.

A big part of that is that I haven’t been doing anything new.  I did a ton of Trello-related work in about a one-year period and then was done.  But I still use it every day and recently found a gap that I decided to close.

My personal Trello board has three columns.  “Not Started,” “In Progress,” and “Done.”  Pretty simple.

The “Done” column is the one I decided needed some help.

The “Done” column is important to me.  It’d be easy to not have one, simply archiving cards as they were completed.  I think it’s easy to get lost in a never-ending “Not Started” list without seeing the things that have been completed.  Instead, I have a process that runs nightly to archive cards over two months old, after they’ve had time to pass out of my mind.

I’ve been finding of late, though, that that’s not working for me.  I see the “Done” column, I know it’s there and that there’s a lot of stuff in it, but that’s what I’ve boiled it down to.  “A lot of stuff” that’s done and easy to ignore.

So I decided to add another automated process.  This one runs weekly and sends me a message via Slack, detailing how many cards I’ve completed in the last week.

I’m not doing this for analytics or anything.  I don’t have any goals to accomplish a certain number of tasks (they’re not estimated or anything so they’re all different sizes, anyway).  I just want to get another reminder in front of my face.  This one is a little disruptive, so maybe it’ll stick differently.

For the record, the following is the code for this new process (with a little obfuscation):

This makes use of my TrelloApi and SlackApi helper classes.

Trello as an Interface via Webhooks

So I’m a bit particular about how I keep my finances in order.  To the point that I wrote my own web-based ledger software to help myself keep it all straight.  Yeah, there are third-party solutions out there, but I wanted something that worked exactly the way I wanted.

Every couple days I pull receipts out of my pocket and go through my email inbox and drop new entries in my ledger.  Every couple weeks I manually reconcile the ledger with my banking statements.  Manual processes – ew – but it’s important enough to me that I do it.

The issue I ran into was transactions that I didn’t have a receipt for.  Tim Horton’s drive-thru or gas station pumps with a broken receipt printer or the Flint Firebirds’ souvenir shop using SquareCash.  No receipt means no ledger entry means confusion when I go to reconcile.

I could have made a mobile version of the ledger entry form but I really didn’t want to. As such I decided I could fix the issue by keeping faux-receipts electronically in Trello.  A single list.  Card title is the place I spent the money, description is the amount, a label to represent the account.  Once there’s a ledger entry, archive the card.

And that would have been enough.  I decided to take it a step further and automate things.

I use a webhook subscribed to that single list to look for new cards, get the data from them, automatically add the record to my ledger, then archive the card. I’m essentially using Trello’s app as an interface for my own so that I don’t have to make a mobile interface.

It’s a bit hacky but I figured I’d throw some of the code out here since I feel like there’s not a lot of Trello webhook documentation out there.  Unlike my usual, I’m going to redact some of the code as it deals with my financial system and I’d prefer not to put that out there.

As I said, I’ve replaced some of the actual code with obscured-away psudo-functions. I’ll point those out those spots as necessary while going through this piece-by-piece.

Right off the bat we pull in my Trello API wrapper class (which really could use some love, maybe I’ll get to that sooner or later) and instantiate an object that we’ll use later. Then we pull in the data Trello posted to us via the input stream.

Webhooks subscribed to a list don’t give us all the details of cards on the list, so if we detect a card creation and we’re certain we’re getting data from the webhook attached to the list, we add a webhook to the new card. This is done with a POST to /1/webhook passing in a description (which is optional and doesn’t really matter), callbackURL (same as the URL of this script, though obscured here), and idModel (the ID of the card). For future reference, we then post the ID of the newly-created webhook to a comment on the card via POST to /1/card/<card_id>/actions/comments with text set as needed.

The rest of the code only fires if we’re receiving from the new webhook attached to the card, and if it’s triggered by either a card creation (which should never happen since the webhook won’t have been created yet), card update (which happens when I set the card’s description), or label addition (which is how I define what account the transaction is for). We only care about those actions because they match the ones I take on the front-end.

From there, we use attributes of the card to build the transaction. The transaction date is whatever the action is taking place. The label is the name of the card. The amount comes from the card description. The category is determined based on the label. The account_id is determined based on the first label selected. A single-item, multidimensional array (the actual interface handles more complex data) is assembled from this data.

If we got an account ID and a category and the amount is numeric, we know we got good data and we’re ready to try to add the record. First, though, we make a GET call to /1/cards/<card_id>/actions with filter set to commentCard and fields set to data, so that we can get all of the comments posted to the card. We loop through them until we find the one where we stored the card webhook ID and then we save that ID off. There’s probably a better way to do this.

If we found a webhook ID (and we always should), we delete the webhook, archive the card, and create the ledger entry. The first is accomplished with a DELETE request to /1/webhook/<webhook_id>. The second is a PUT to /1/card/<card_id>/closed with value set to true.

If we didn’t get a webhook ID, something is seriously broken so we log that finding to the card as a comment via a POST to /1/card/<card_id>/actions/comments with text set to “No webhook found.”

Lastly, if we don’t have all of the things we need to make a ledger entry we log that to the card as a comment. Again, that’s done with a POST to /1/card/<card_id>/actions/comments.

As I said, it’s hacky. It gave me a chance to play with Trello webhooks a bit, though, and was a lot of fun. As I use it more, we’ll see what I did wrong.

Batch Requests with the Trello API

I write about the Trello API a lot.  So much that I’ve already noted that I write about it a lot. I keep coming across new stuff, though, so I’m going to keep writing about it.

My latest experiment was a look into using their batch call, which I don’t see a lot of documentation about so I figured was worth writing up.

Batch functionality lets you fire off a series of GET requests (and they have to be GET requests) as one request.  Depending on your code and what requests you’re making (and what data you’re getting back), this should speed things up a bit.  My test script went from running in 37 seconds to 20, for example.

In an extremely simple (and pretty much useless) case, you could replace GET calls to /1/members/me and /1/boards/511e8c0101d3982d05000d5b with a single batch call, /1/batch?urls=/members/me,/boards/511e8c0101d3982d05000d5b.

As shown, /1/batch takes the URLs parameter, a comma-separated list of the calls you want to make, minus their version number prefix.

Of course, this means you get only a single response back, and it looks a little different from a normal response.  The response is an array of objects – but not of the normal response objects you might expect.  Instead, it’s an object with a single property, with a name set to the HTTP response code of the request.

So if your first request was to /1/boards/511e8c0101d3982d05000d5b, a normal response would start as follows:

The batch version of that response would look like this:

Obviously that’s simplified, I just don’t think it’s necessary to show the whole response.

One nice little gotcha with that response and working in PHP is handling a numeric property name, which is done by putting curly braces around the number, as seen in the code to follow.

Lets say you want to get the names and IDs of all the boards you’re assigned to and the names and IDs of all of the lists on each of those boards.  Without batching, you could do the following:

With batching, that becomes this:

This assumes that the API will respond with a 200, of course.

As I said, I didn’t see a ton of documentation about batch calls in the Trello API.  This is a stupid simple example but I thought it was worth putting out there.

Update, 8/6/2019: It’s been nearly five years since I wrote this post but a question came up yesterday in the Trello Community Slack and I wanted to add to this to specifically call out something I glossed over.

As noted above, the /batch call expects a set of API endpoint URLs to be provided as values of its urls parameter.  When those URLs are things like /members/me and /boards/511e8c0101d3982d05000d5b, as in my example, simply comma-separating them works just fine.

But what if your API endpoint URL has parameters of its own, such as /cards/560bf4dd7139286471dc009c?fields=badges,closed,desc?

Adding that as is to your urls parameter won’t work because both urls and fields are comma-separated and it’s not smart enough to deal with that.

In that case, we need to switch to providing the values of fields individually and also URL encode the resulting value for use in the urls parameter.

The URL /cards/560bf4dd7139286471dc009c?fields=badges,closed,desc would become /cards/560bf4dd7139286471dc009c?fields=badges&fields=closed&fields=desc, which URL encodes to %2Fcards%2F560bf4dd7139286471dc009c%3Ffields%3Dbadges%26fields%3Dclosed%26fields%3Ddesc.

It’s worth noting that not every URL being provided to batch needs to be URL encoded, just the ones that cause an issue like this.

If we added this request to the batch call from above, it would look as follows: /1/batch?urls=/members/me,/boards/511e8c0101d3982d05000d5b,%2Fcards%2F560bf4dd7139286471dc009c%3Ffields%3Dbadges%26fields%3Dclosed%26fields%3Ddesc.

There appear to be cases where even this won’t return exactly what you might expect.  For example, a batched request to /boards/511e8c0101d3982d05000d5b/actions?fields=date&fields=type&limit=1 will return the id and the memberCreator in addition to the requested date and type.  I don’t know why this is.

Update, 8/13/2019: After I worked out the above solution, the Trello API docs were updated with an official solution: Manually URL encode the commas in each call and then URL encode the entire urls parameter.

Implementing Advanced Trello Functionality via the Trello API

I’ve written pretty extensively on my work with the Trello API and yesterday I got to present the code side of it at TechSmith’s internal development conference, ReCon.

Last year I presented for the first time (when the event was called DevCon) and I think I learned a lot from it that helped me this time around.  I’m pretty sure there were fewer “ums” and “uhs” but it was also a shorter presentation with less time for that.

The shorter presentation meant I had to absolutely blow through the information I wanted to put out there, so I’m worried I didn’t give enough time for it to set in.  It was supposed to be part of a larger presentation, an idea that fell apart late in the process, so I’m not sure what I could have done about that.

I think the biggest adjustment was the most important one.  In my presentation last year, my code slides weren’t very legible.  This time I made sure to focus on that, so there’s less code on each slide and the text is larger.  I couldn’t find a better way to get syntax-highlighted, properly-spaced code on to a Powerpoint slide than with screenshots, though, which seems stupid.

Unfortunately, I made one of the same mistakes as last year, not checking the audio beforehand.  I used a mic that was piped into the room speakers and recorded off an omni-directional mic that picked up everything in the room.  This makes the recording sound like I was in a car wash.

I enjoyed it significantly more than my presentation last year, though, and hope I have as good of a topic to speak on next year.

I referenced “Jenny’s presentation” and she’s posted it.  It covers a lot of the context I left out of mine.

Moving Cards to a Different Board via the Trello API

I’ve been writing a ton of late about the Trello API because I’ve basically been head-down in it for the last week.  Today I discovered my new biggest annoyance with an otherwise awesome tool.

Using the Trello UI, it’s really simple to move a card from one column/list to another on the same board.  Click the card, drag it over, drop.  Done.

Want to move it to a different board?  That’s not so difficult, either.  Open up the card, click the “Move” option, pick your new board and your new list and you’re good.

Moving a card through the API is also pretty easy.  Fire off a PUT request to /1/cards/xxxxxx (where xxxxxx is the card’s ID) with the idList parameter set to the ID of the list you want to move it to.  The documentation for the Trello API says that the idList parameter is optional and the reason is that you can update a card without moving it.

List IDs are globally unique.  You can request information about any list, regardless of what board it’s on, with a GET request to /1/lists/yyyyyy (where yyyyyy is the list ID).

So if I want to move a card to a list, I just need the list ID, right?  Right.  Except when you need more.

It turns out that there’s another optional parameter, idBoard.  If you’re moving a card to a list on a different board, you are required to specify the optional board ID even though the list IDs are unique, so by defining a list ID you automatically know what board that list belongs to.

I got hung up on this for about 30 minutes today and it annoys me.  The list ID is the most specific identifier you can get.  Why require a more general one on top of it?

So, yeah, if you’re moving a card with the API, you have to use idList.  If you’re moving to another board, you have to use idBoard as well.  Hopefully me mentioning it helps someone out.

Linking Child and Parent Cards on Trello – Part 2

Last week I published a bit of code that uses the Trello API to keep parent and child cards synced across a set of boards.  It was a little piece of research that has absolutely taken off around the office so I’ve been expanding on it and demoing it and talking about it and generally losing my mind.

The thing I expanded on most is a flaw that appeared in my original script whereby a user could create a child card outside of the normal workflow and it would never be linked to the parent card.  Obviously “outside of the normal workflow” means it’s already an edge case but that doesn’t mean it’s as uncommon as we’d like, so I came up with a way to handle it.  It does rely on the child card being tagged with the same card tag as the parent but it’s better than nothing.

As with my previous post, this uses my Trello API wrapper class and pulls in the $GLOBALS[‘config’] array of configuration values from another file.  Also as with my previous post I think it’s commented pretty well but we’re going through the code piece-by-piece anyway.

We loop through all of the cards on our Work in Progress (“WIP”) board and use a regular expression to see if they have a card tag as a prefix (appearing in the pattern of “[TEST4] Test Project 4”). If the card does, we save an array of data about the parent to an array of parent cards for reference by card tag later.

Then we loop through our list of child board names and get every card on that board using a GET request to /1/board/xxxxxx/cards (where xxxxxx is the board ID).  If the card’s description doesn’t match our convention for linking back to a parent, we know we’ve found a rogue card.

We check to see if the card has a tag in its name, using the same regular expression as we did earlier.  If it does, we can use it to move forward. If we know the parent that tag belongs to, we can do even more.

The first thing we do is fire off a PUT request to /1/cards/yyyyyy (where yyyyyy is the ID of the rogue card) with desc set to the current description with our parent link prepended to it.  This gives our child card the necessary link to the parent.

On the off chance that the parent card doesn’t have a label, we use the fact that we already know what board the child card is on to set one.  That involves a PUT request to /1/cards/zzzzzz/labels (where zzzzzz is the ID of the parent card) with value set to the color name of the label that corresponds to the board.

Then we get ID of the parent card’s “Slices” checklist, as that’s where the parent card links to each of it’s children.  We make a GET request to /1/cards/zzzzzz/checklists and loop through each one until we find the one with the right name, then save that ID off for later.

What if we didn’t get a checklist ID?  Then we make one.  We fire off a POST request to /1/cards/zzzzzz/checklists with name set to “Slices” and that gives us back a bunch of data about a newly-created checklist.  We save off the new checklist ID so we can move forward.

And our last step of the loop is to link the parent card to the rogue child.  We fire off a POST request to /1/cards/zzzzzz/checklist/cccccc/checkItem (where cccccc is the “Slices” checklist ID) with name set to the URL of the rogue child card.  Trello’s interface will convert that to the name of the child card when the parent card is viewed.

As I mentioned in my previous post, this is my first pass and I’m sure there’s a better way to do this.  This fixes a gap in that earlier implementation, though, so obviously iterating on it is working.

Linking Child and Parent Cards on Trello via the Trello API

One of the things my team at work always seems to have a problem with is tracking our tasks and how they fit into the bigger picture of our team’s goals.  We had these problems when we were using a bulletin board, we have these problems now that we’re using a single Trello board.

We have a subset of the team currently researching new tools due to the perceived limitations of Trello. Being a developer and having never met a problem that couldn’t be solved by throwing code at it, I decided to see what could be done with the Trello API.

I’ve written about using the Trello API a couple times before.  It’s a powerful tool and it’s something I’ve had a lot of fun with.  This builds off of that work.

One thing that was the lynch-pin for figuring all of this out is the difference between a card’s ID, shortID, and shortLink.  Because shortLink isn’t returned by default when you make a request for a card’s information, I thought that it was just another term for shortID, much as how archived and closed are synonyms within the Trello environment.  It turns out that that’s not true, and since shortLink is exposed in every Trello URL, it opens up a lot of options.

Before getting into the code too much, though, a look at the problem we’re trying to solve…

Right now my team has one “Work in Progress” (or “WIP”) board with ten functional columns.  Requests Not Started, Requests In Progress, Slices Not Started, Slices In Progress, Ready for QA on DEV, QA on Dev, Need to Publish to Stage, QA on Stage, Ready for Publish, and Published.

Requests are large-scale projects.  Slices are parts of a request, which are supposed to be individually-shippable.

When you pick up a request that hasn’t started yet, your first task is to break it into slices.  Those slices then march across the board and when they’re all published the request card jumps over to join them (currently a manual process).  The idea is that the slices show the team a little bit more of what’s in progress while the requests are geared towards external stakeholders.

The problem with that is that it makes for a messy board.  If only we could have a WIP board just for requests, and have the slices flowing along some other path, but still tied back to the request card.  So that’s what I did.

Here’s my informal demo video, with code to follow.

Okay, now on to the code.

This makes use of my Trello API wrapper class and has several values (all part of the $GLOBALS[‘config’] array) dumped off to an included configuration file.  I think the comments are pretty good, but let’s go through this a little bit of code at a time.

The whole thing is wrapped in a loop through the cards on the Requests in Progress column of the main WIP board.  We get that by doing a GET request to /1/lists/xxxxxx/cards (where xxxxxx us the list’s ID) and I won’t bother detailing it any further.

We figure out which board the child cards should go to by looking at the labels and our configured array that matches label colors to child boards.  In this example, the green label belongs to the ecommerce board and the red one to the marketing board.  If the first label on the card matches the color of one of those boards, we’ve got our board.

Then we take a look at the parent card name and look for anything inside square brackets using a regular expression.  That’s the tag that should be applied to child cards, something we use for filtering.

We set the number of children on this card and the number of completed children to zero, values we’ll update as we go.  Then we loop through the checklist IDs we got when we looked up the card data.

We use a GET request to /1/checklists/yyyyyy (where yyyyyy is the ID of our checklist) to get more info about that checklist.  If the name of the checklist is “Slices” then we iterate through each item because we know those represent our child cards.

If the text of the checklist item matches the pattern of a Trello card URL, we know it’s an already-linked child.  We get the shortLink from that URL using our regular expression and then make a GET request to /1/cards/zzzzzz, where zzzzzz is that shortLink.  If the card has an ID it means it still exists, so we can do some work with it.

By default, we set the card’s status to incomplete, which is one of the states of a checkItem in Trello. If the card is closed or is in the Published list on our QA board, we know work has been completed and the state can be set to complete. We can also increment our counter of completed child cards by one.  If the state we’ve determined doesn’t match that of the checkItem, we fire off a PUT request to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/state (wherre cccccc is the card ID, bbbbbb is the checklist ID and aaaaaa is the ID of the specific checklist item), passing in a value set to whatever we determined above.  This updates our checklist item to match the state of the card itself.  Then we increment the counter of children by one.

If the card’s ID wasn’t found, we know the card has been deleted.  In that case we fire a DELETE request to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa to completely remove the related checklist item.

Now we’re back to if we didn’t find a URL in the checklist item’s name.  In that case, we know the child card hasn’t been created yet so we go on to do that, assuming we actually have a list ID to work with (just in case there’s no label on the parent card or something).

We build the child card’s name by combining the text of the list item with the card tag we defined earlier, then we build the description by just linking to the parent card.  Then we fire that data (along with the list ID) off via a POST request to /1/cards.

That request returns data about the newly-created card.  If the card was successfully created (determined by whether or not we got an ID back), we go back to the checklist item.  We update the name to be a link to the new card, which displays in Trello as that card’s name, and we make sure to set the status to incomplete just in case someone did something stupid.  That’s done with a pair of PUT requests, one to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/name with value set to the new card’s shortUrl, the other to /1/cards/cccccc/checklist/bbbbbb/checkItem/aaaaaa/state with value set to incomplete.

At this point we’ve looped through every item on the “Slices” checklist for a single card.

The last thing we do before moving on to the next card is compare our counts of children and completed children. To account for the fact that a card may have been moved to Requests in Progress before the “Slices” checklist was completed, we require at least one child to exist.  If there are children and they’ve all been completed, we move the parent card over to the Published column of our WIP board with a PUT request to /1/cards/dddddd, where dddddd is the parent card ID, with the pos defined as top and the idList defined as the ID of the published column.

As I mentioned in the video, I’ve got this running once a minute as a cron job.  I’m sure there’s a better way to do it but this is my first pass so I’m not worried about it.

Trello API and dateLastActivity

I came across something interesting today, working on a project that is supposed to automatically archive all cards on a Trello board that are in the “Completed” column and are more than two weeks old.

This should have been really easy.  Use the /1/lists/<list_id>/cards GET request to get all of the cards on the list then check to see if the dateLastActivity of the card was more than two weeks ago.  If so, make a /1/cards/<card_id>/closed PUT reqest with the value set to true to close the card (touching on one of my favorite annoyances with the Trello API, that the interface uses the term “archived” but the API uses “closed” with no explanation).

What I was seeing, though, was that every card in that list was showing a dateLastActivity of the exact same second (milliseconds apart), at a time when it was highly unlikely that any of the cards (let alone all of them) were actually being touched.

My conclusion is that there’s something that Trello itself does that can cause dateLastActivity to be updated.  I don’t have anything to back that up but it seems most logical.

What that means, though, is that dateLastActivity is unreliable.

The option I’m going with instead is to make an extra request for each card, using GET /1/cards/<card_id>/actions with filter set to all and limit set to 1 to get the most recent action for each card, then taking the date of that as a true indicator of last activity.  It means an extra call for each card, which is awful but seemingly can’t be helped.

Taking that a step further for this specific case, the date comparison can be handled at the API level rather than in the script.  Instead of requesting the just the latest action I can request the latest action after my date cutoff using the since parameter.  If I get any actions back at all, the card has been touched and shouldn’t be archived.

I have no idea why Trello would make dateLastActivity unreliable but it’s extra calls to their infrastructure to make up for it so maybe they’ll do something about it in the future.

PHP and the Trello API

Note: This post was updated on March 4, 2021, to replace references to outdated code.

A couple weeks ago I wrote up a bit about a PHP wrapper object I’d written for the Twitter API v1.1.  Since then I’ve been playing with the Trello API a bit, so I figured I’d write that up as well.

The code for the Trello API class is going to look really familiar because I actually wrote my Trello API wrapper object first, so the Twitter one is based on it.

Say you wanted to use this to get the name of your board’s red label. That would look like this:

We use the request method make a GET call to /1/boards/xxxxxx, where xxxxxx is the ID of the board we want the data for. We include the optional fields=labelNames query because the names of the labels are all the data we want to get back in this case. The request gives us back an object where each label’s name is available.

For the record, I kind of hate that Trello uses the color of each label as an identifier. I’m sure they can back the decision up but it reminds me of the old CSS “rule” about not naming your classes after what they look like, because what happens if you change what they look like?  If you have a class that converts everything to uppercase and bolds it, and you call it uppercase_bold, that works great until you change it to small caps and italics.  That’s a whole other post, I suppose.

Now that we have the text for the red label on our board, say we want to change it. That looks like this:

This time it’s a PUT request to /1/boards/xxxxxx/labelNames/red (where, once again, xxxxxx is the board’s ID).  We use the optional $args argument of the request method to pass in a value of $text, where $text is the new label text.

What if you want to use that newly-renamed red label and apply it to a new card? That’s just a POST request to /1/cards.

We use the optional $args argument again to pass in our array of arguments. We set the name to whatever the value of $card_name is,  idList is set to $list_id (the ID of the list the card will be added to), and the optional labels specifies that we’re applying just the red label to this new card.

Again, I could complain about using the color as an identifier but whatever.

Like my work with the Twitter API, this is hardly groundbreaking stuff.  Just another thing I thought might be useful for people other than myself so I wrote it up.