Fun with JSON (and Trello) in PowerShell

Posted on 3 Apr 2013 by Jamie

Like most people who’ve tried it, I love Trello. And like a lot of other developers, we use at Fusion for basic software project task board management. But… one thing that is really missing thus far is any capability remotely related to “reporting”. Even getting a list of list names or card names looks to be impossible (as of 4/3/2013, anyway).

Today I needed to copy a list of our cards over to a separate document to be reviewed by some project manger-ish folks and other stakeholders. Granted, we don’t have 100s of cards. But I really didn’t want to re-type all of their names manually; especially given that I will likely need to do this a few more times in the future. As a developer, and lover of the command line, I figured there must be a way to script out an alphabetical list of our card names.

Alas – with some Google-ing and PowerShell 3.0 (included in Windows 8; available for download on Windows 7),  I was able to get my names within a matter of a few minutes. Not to mention, it’s now repeatable – and I even learned how to mess with JSON in PowerShell, as well as download data directly from Trello.

Prerequisites

If you want to follow along in your own PowerShell console, make sure you are either running Windows 8, or have PowerShell 3.0 installed on Windows 7. I’m using some new commands not yet available in PowerShell 2.0.

To download data directly from Trello, I am going to use the new Invoke-RestMethod cmdlet in PowerShell 3.0. You could also use curl.exe, if you prefer. I quite regularly use the copy of curl that is included in the Git for Windows install: https://code.google.com/p/msysgit/downloads/list. Among many other Linux-based commands, curl.exe is included in the Git bin folder.

And, of course, if you want to download some Trello data, you need to have access to a Trello board. For this example, I’m going to use a public board created just for this blog post – which means you, too, should have read-only access to it for the purpose of the code below. If you want REST access to a private board, read the following for help on obtaining and using your Trello application key: https://trello.com/docs/gettingstarted/.

Let’s Start With Just JSON

Before we worry about directly downloading from Trello, let’s start by loading some JSON in PowerShell. Navigate to the public Trello board I created, and click on the Options button on the right-hand side. Near or at the bottom of the list of options, you should see one titled “Share, Print, and Export…” – click it. Then click on the Export JSON option – which will download a file called “powershell-json-access.json” to a local folder. I’m going to assume in the code below that the file was downloaded to your Downloads folder.

Now open a PowerShell console. At the prompt, run the following command:

PS> gc ~/downloads/powershell-json-access.json

That will simply pipe all of the board’s JSON data to the console as a big long string. To convert to a JSON object, run the following:

PS> gc ~/downloads/powershell-json-access.json | convertfrom-json

You should now see the same content, only represented as a full-fledged PowerShell object – including properties for the relevant data from the Trello board.

Now, to see a list of all cards, let’s try this:

PS>  (gc ~/downloads/powershell-json-access.json | convertfrom-json).cards

That should result in a list of card objects – i.e. we’re reading the cards collection from the converted JSON object.

To get a list of the actual card names, I’m going to use the alias for Select-Object – i.e. “select” (sans quotes):

PS> (gc ~/downloads/powershell-json-access.json | convertfrom-json).cards | select name

And finally, to alphabetize the names:

PS> (gc ~/downloads/powershell-json-access.json | convertfrom-json).cards | select name | sort name
Now let’s look at grabbing our board data directly from Trello’s REST API.
 

Direct Download from Trello

The only real difference from above is the source of the JSON we’re using. In the case of Trello, anyone can perform an HTTP GET against a public board. So, using the public board I created for this post, run the following command:
PS> invoke-restmethod "https://api.trello.com/1/boards/515cdb395543704e7f0004db"
The Invoke-RestMethod cmdlet automatically converts JSON content into a JSON object – so we no longer need to use the ConvertFrom-JSON cmdlet. Note that the returned data represents the basic board information – without any list or card data. So let’s now update our GET request to tell Trello to include this information:
PS> invoke-restmethod "https://api.trello.com/1/boards/515cdb395543704e7f0004db?lists=open&cards=open"
The returned JSON object should now include two additional collection properties: lists and cards. And, similar to the commands above, we can list out the card names as follows:
PS> (invoke-restmethod "https://api.trello.com/1/boards/515cdb395543704e7f0004db?lists=open&cards=open").cards | select name | sort name
 

Even More Fun

Now that we’ve established that we can play with JSON objects in PowerShell, and we can also use Invoke-RestMethod to download JSON-formatted data directly from Trello (or, for that matter, any other REST endpoint), let’s look at a few other PowerShell capabilities.

Run the following commands to get a table of cards and their associated statuses (where the status names are just the list names where the cards reside).

PS> $j = invoke-restmethod "https://api.trello.com/1/boards/515cdb395543704e7f0004db?lists=open&cards=open"
PS> $lists = @{}
PS> $j.lists | % {$lists.add($_.id, $_.name)}
PS> $j.cards | select Name, @{name="status"; expression={$lists[$_.idList]}}, due

With those commands, you should see a list of cards and their associates status names and due dates. The “%” alias is used as a for-each in PowerShell, which we’re leveraging to add our list IDs and names to an empty hash table called $lists. Then we use the select alias, along with a computed property, to create a table of cards, their status values, and their due dates.

To show one more little PowerShell trick related to a formatted table and some grouping, try these commands (assuming you ran the previous set of commands):

PS> $status = $j.cards | select Name, @{name="status"; expression={$lists[$_.idList]}}, due | sort status, due
PS> $status | ft -groupby status -property name, due
At this point, you could pipe the output to your clipboard like this:
PS> $status | ft -groupby status -property name, due | clip
And then simply paste the contents into an email to your boss!
 
 
In this post, I covered how to download JSON data from a REST service within PowerShell. If you would like to build a REST service with the ASP.NET Web API, and aren’t sure where to start, check out my latest book ASP.NET MVC 4 and the Web API: Building a REST Service from Start to Finish .