I use Trello to keep track of tasks related to my business. This includes things like admin tasks, development of my services, training, and creating new website content. Of course, the analyst in me wanted to track it, and initially, this was born out of curiosity. Is this possible? How easy is it to do? Would it be of any use?
Trello is a great project management tool, but once tasks disappear into done they just get forgotten, and there doesn't seem to be a way to see an overview of how active a board is, or how much progress is being made on tasks.
I was determined to find a free solution, I didn't want to pay a monthly fee for something that I thought was little more than a bit of fun. And although the method I found is free, I now think that the end result is pretty valuable in terms of understanding what I spend time on. It also enables me to see how many tasks are completed over time, which helps me to be accountable to myself.
So I started looking around, I tried https://ifttt.com/, which does allow you to access the Trello API but the functionality was limited. I looked for a Google Sheets add-on but couldn't find anything that fit my needs. Eventually, I found a solution using Zapier, a tool similar to IFTTT, which allows you to connect different tools via their APIs. I used it to connect Trello with Google Sheets, and create a new spreadsheet row every time I complete a card. I then used Google Looker Studio to visualise this data.
Here's how to set it up.
1 - Create a Google Sheet
Create a new Google Sheet, call it Zapier Trello Tracking or something.
2 - Create sheet headers
You'll need to name the columns in your Google Sheet for the info that you'd like to know about your Trello cards.
I use only Date, Card Name, and Label, but as you'll see later the list of fields you can pull from Trello is endless. You might also want to add the card creator or the Trello people who were attached to the card.
3 - Connect Trello to Zapier
First, sign up for an account at https://zapier.com/ if you don't already have one. Navigate to your dashboard and enter the following options to find the 'zap' that will do what we need.
Now we need to give Zapier access to Trello and Google Sheets.
4 - Set up the trigger for completed cards in Trello
Once you've granted Zapier access, let's finish setting up the trigger. Choose the board you want to track, and select the 'Done' column (or whatever you've named it) so that we can track completed tasks.
Once set up, you can test that this is pulling data. Zapier will attempt to find a card in the board/column you've previously selected. If you see data similar to the screenshot below, you know it's working.
5 - Connect Google Sheets to Zapier
Just as we did with Trello, we have to give Zapier access to Google Sheets. Connect and select the Google Sheet that we created in step 1.
6 - Match Trello fields with Google Sheets headers
The final step to get data flowing is to tell Zapier what to populate the Google Sheet with. Under each column of the sheet, select the following:
Again, you can test this to see what the result would be.
7 - Create a Looker Studio Dashboard
At this point if you're comfortable with making your own dashboards in Looker Studio you can do so, but you can also use this simple template I created to sit on top of the Google Sheet.
To create your own copy and connect to your Google Sheet: Click here to access my dashboard and select the 'Make a copy' option via the burger menu in the top right.
Create a new data source, choose Google Sheets, and find and connect the sheet that you created earlier in order to create your own copy of the dashboard that's connected to the sheet with your Trello data.
Like I said earlier, I'm only using a tiny amount of what's available via the Trello API. You could expand this to capture who is completing tasks, who is creating them. You could even pull in data from multiple boards - perhaps this could be an easy was to identify and clean up boards that are no longer needed.
Happy tracking!
Comments