My tinkering with kanban

This post briefly describes kanban in terms of personal planning and management, and then explains how I combined a kanban tool (Trello) with Google Sheets to give me an easy way to record the things I get done so I could review them and reflect on how I’ve been doing.

Not nearly enough about kanban

Kanban (“sign” or “billboard” in Japanese) began as a system for improving efficiency in manufacturing by making tasks and progress visible. Here’s a simplified kanban board:

A kanban board showing tasks in three groups: to do, in progress, and done.
A simplified kanban board from Wikimedia Commons

The “to do” column, sometimes called the backlog, shows all the tasks that need to be done. The next column is “in progress.” One rule of kanban is to limit the work-in-progress, because you can only do so many things in, say, a week. At the beginning of a work cycle, then, you move cards from “to do” to “in progress” based on your capacity. As tasks are done, you move them to the “done” column, and you’re now able to move another task from “to do” to “in progress.”

There’s a lot more to this, but since I’m not a manufacturing plant, for me it was extremely helpful to read Personal Kanban by Benson and Barry.

The following video explains kanban in terms of software development. You can cheat and watch just the segment from about the 1:00 mark to about the 2:15 mark; that’ll be plenty for this post’s purposes.

A kanban board to plan

I’d seen coworkers using Trello on projects at my last job, so I got myself a free account. Trello has three levels: a board (think of this as a high-level category), a list (a stage in progress), and a card (a specific task). The following image is a board I set up for a trip I’d like to take.

A kanban board made in Trello, with three lists labelled backlog, in progress, and done.Here’s the same board with some cards added.

A Trello board showing three tasks under backlong, and one task under in progress.At the beginning of this imaginary week, I looked at the backlog — the pool of all cards — and dragged the “check visa requirements” card to the “in progress” list.

In real life, I might set a work-in-progress limit of four cards, forcing myself to prioritize items from the backlog.

When I finish a task, I drag it to the “done” list. Over time, that’ll be a big list, and it’ll be hard for me to see what I did when. One recommendation from Personal Kanban is periodically to review your accomplishments and reflect on your process, which is harder with a constantly growing list.

Laziness, the road to efficiency

I knew I could create a spreadsheet to record those completed tasks somehow, but I didn’t want to be copying and pasting all the time.

So I poked around in IFTTT (“IF This Then That”) to see if I could create a little app to do the grunt work for me.

Of course I could. The start of an IFTTT app is the trigger, the thing that kicks off an action. In IFTTT, when Trello’s part of the trigger, you spell out which group of Trello boards, which individual board, and which list on that board you’re talking about, as you can see in the example on the right.

In other words, the trigger is “if I drop a card here…”

That’s the IF THIS. The THEN THAT is an action. Trello’s already set up to take action with Google sheets. In fact there were two or three, and one was “add a row to a spreadsheet.”

Details from IFTTT for adding a row to a spreadsheetClick the image on the right to see the default setup in IFTTT for adding a row to a spreadsheet based on a Trello card.

The format confused me quite a bit, and I had to play with it for a while to figure out what the options were, and which ones I needed. All those labels in the formatted row section, for example, are the names of fields used by Trello; you can pick from them to make column headings in Google Sheets.

What the action section of IFTTT is really asking is:

  • What sheet you want to add rows to?
  • What do you want to put into each row?
  • What’s the path to the sheet?
The action portion of an IFTTT app, which will create a new row in a spreadsheet when a Trello card is moved to the DONE list.
The actual “add this stuff in the new row” action I use.

I made a lot of practice cards, dragged them around, checked the results, moved columns in the sheet, and so forth.

What did I end up with? A spreadsheet cleverly named COMPLETED. In the “formatted row” section, you specify the names of the columns, and those names come from the field names in Trello.

I was interested in when a card moved to “done,” what board it was on, the card’s title (the short description on the Trello card), and the URL in case I ever wanted to see it again.

Strictly speaking I didn’t need to specify the done list, since the whole app is about cards that got moved to a done list, but I put it in in case I ever want to add other lists as well.

The results

If you don’t have a spreadsheet named COMPLETED, IFTTT will create one the first time the app gets fired. I made the sheet ahead of time and practiced with dummy data. That seemed a lot easier.

What does the record look like?

A Google sheet showing data imported from Trello via an app in IFTTTThe first five columns were spelled out in IFTTT. Because Trello’s timestamp (date) is so clumsy, with help from an online colleague I learned the formula you can see in the formula part, which parses the long-form date and turns it into a more searchable, sortable one.

Details on making it work

At the beginning of the month, I (try to) go to the spreadsheet and create my own monthly archive. I copy the completed items and paste them onto a new, month-labeled sheet. On the main sheet, I then hide the rows I’ve just copied, so I’m ready to start recording the new month’s completions.

Usually, looking at a month at a time is better for me. If I wanted a longer slice of time, I can unhide rows on the main page and look at whichever I want.

I haven’t figured out how to automatically insert my date-simplification formula mentioned earlier, so every now and again I visit the sheet and just copy the formula to rows that don’t yet have it.

I have half a dozen kanban boards — one for professional concerns, one for personal development, one for household items, and so on. It’s a way to compartmentalize things; it works for me. That’s why, in the BOARD column of my spreadsheet, I have conditional formatting that changes the color of a cell based on the name of the board it came from. It’s a little stunt that I like, and I can use those labels to filter the display as well.


Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.