Using cURL and jq to work with Trello data

As mentioned in this blog post we are using Trello to handle our data at Speak Easy instead of the google sheets that we used to do. There are a lot of pros for this, but one con is that the data is just not as accessible as when you could sort lists etc in google sheets. I decided to make that better by creating a few scripts in python leveraging just a basic cURL command and the jq library.

Todo list:

1. Access the Trello API from the command line
2. Identify first use case for API call from command line
3. Create script for first use case

1. Access the Trello API from the command line

First things first, I want to make sure I can access the API with a cURL command with the right auth etc. To do this I leveraged my old postman scripts and stole the cURL command from there. If you haven’t seen it before postman has a bunch of easy to export formats if you click the code link just under the save button.

How to open the code snippet window in Postman and select the cURL export type

Once I copied this to my clipboard I was able to run this in my command line and get the same results as in postman. But realistically this isn’t super helpful because if anything it is harder to read. But the power is really going to come from when I start using the jq library. So now I need to install jq which I am able to do easily through brew install jq.  This means that I can now “pipe” the response into jq and see the json formatted as well as start manipulating. So first things first, below you can see the difference in the cURL response before jq, and after being piped to jq.

curl -X GET ‘https://api.trello.com/1/board/######/lists?key=######&token=######’ | jq ‘.’

2. Identify first use case for API call from command line

So why do I want to do this anyways? The first thing I thought about is how do we create a mailing list from our mentors? Previously it was a column in our google sheets doc. Now it is spread out across cards. I want to collect all the email addresses in each of the cards into a single list.

So let’s get to work!

I needed to actually improve the API call more than the jq filtering. In this case I was able to ask the Trello API for only the fields I cared about (name and desc which includes email).

curl -X GET ‘https://api.trello.com/1/board/######/cards?lists=open&checklists=all&fields=name,desc&key=######&token=######’ | jq ‘.’

 

So here is where I bet you have already spotted my mistake and I have to fully admit that I am writing this live as I go and have just realised a show stopper! The whole point of this exercise was to access the email addresses easier. The kicker though is that while we have email addresses in the cards, they are in the body of description and therefore I would need to not only do all this funky fun with jq, but also use some funky regex to parse them out. And that my friends is where I will be drawing the line.

So instead of working harder, I decided to work smarter and figure out how to add more useful data fields to the cards. Turns out there is a power up for that called “Custom Fields” which has allowed me to make an email text field for all cards. This comes with some docs for the API as well.

Picture of my Mentor Trello card with the custom email field.

So now, when I ask the Trello API for fields, I can ask for the ‘customFieldItems’ instead of the ‘desc’ field.

curl -X GET ‘https://api.trello.com/1/board/######/cards?lists=open&fields=name&customFieldItems=true&key=######&token=######’ | jq ‘.[] | select(.name == “MENTOR: Abby Bangser (EU)”).customFieldItems[].value[]‘

Wrap up

So given I have just created myself a new todo list for this activity:

1. Access the Trello API from the command line
2. Identify first use case for API call from command line
3. Introduce email fields for all Mentors
4. Create script for first use case

I am going to call it quits for the day. the next blog will need to dive into moving this proof of concept into a python script 🙂

In the mean time…is jq interesting? Want more details in a blog or is it just a utility I should show usages of?