Automated Trello Reporting Using Google Spreadsheets

I recently did some work for a client who wanted some reports that outlined changes to their Trello boards week to week, and whilst I can't share the resulting code, I thought it would be useful to share how I approached the problem. Initially I considered 2 possible solutions:

  1. Read through the activity log of every card, and try to establish if that activity was something that needed to be reported.
  2. Use the Trello Backup Script that I wrote last year to save a dump of the boards on a weekly basis, and then compare the data to see what needed to be reported on.

It soon became apparent from the reporting requirements that I could not rely solely on the card's activity log, because of the following issues:

  • The card could be deleted (so there would be no activity to interrogate)
  • It could be potentially complex to rewind back to a point of time solely using the activity log.
  • Some changes (e.g. due date or checklist items) are not recorded in the activity section of the card.

So with option 2 looking like a clear winner, I set about writing a couple of  scripts to manage the process.

The Data Dump

I modified the original Trello Backup Script so that it would output the JSON data into a new spreadsheet every time it was run, and keep a register of all of the backup spreadsheets that it had, and when they were extracted.

This process was scheduled to run at a set time every week, and was executed separately to the actual report generation script to make it easier to clean up if either script failed or timed out.

The Report Generator

This script was scheduled to run an hour after the data extract, and would only proceed if a successful data dump had occurred.

It produced each of the required reports as a separate sheet, and emailed the resulting spreadsheet to all the interested parties.

In the majority of cases, the reports were generated by opening the last 2 data extract spreadsheets listed in the register, and using the JSON objects stored within them to retrieve the frozen state of all the boards, which could then be compared to identify any changes that needed to be reported.

And that's pretty much it - admitedly it was a bit more complicated than that, but you get the gist of how this method provides you with a solid platform from which you can write your own reports about changes to your Trello boards.