Send automated Trello productivity reports using Retool Workflows and Google Sheets

I wanted to streamline the process of sending out weekly progress reports to our stakeholders, summarizing the work completed the previous week. My goal was to make this as effortless as possible by utilizing low-code tools. After exploring Trello's API, I discovered that setting up a webhook to trigger notifications whenever a card is moved to the "Done" column would be easy to do. Next, I need to record these events in a simple and accessible way - using a Google Sheet seemed like the perfect solution. To tie all the pieces together I used Retool Workflows; to send out these updates via email periodically, ensuring our stakeholders are always informed with minimal manual effort on our part. 

This is a step by step guide on how I implemented this solution and I provide the templates for my Retool Workflows at the end of this article!

How it works

Whenever a card moves from "In Validation" to "Done" a webhook from our Retool Workflow gets triggered. The workflow checks the card's status, fetches its description, and uses an AI step to summarize it before saving the summary and card title to a Google Sheet.

Every Friday at 2 PM EST, another Retool Workflow runs via a cron job, sending a styled email report of the week's completed tasks, sourced from the Google Sheet. It then clears the sheet to prepare for the next week's entries. This setup not only automates our updates but ensures stakeholders receive timely and organized reports.

Initial setup

Before we start building our workflow we need to set up our Google Sheet, which will act as our data store, and get an API key for our workflow to be able to interact with Trello.

Set up a Google Sheet

Fairly self explanatory - just set up a Google Sheet called Reports. Inside the Google Sheet create one tab for each of your Trello Boards that you want to track. Make sure the naming of the tab is the exact same as the name of the Board in Trello. We also need to set up Google Sheets as a resource in Retool. Doing this is easy and requires no explanation!

Accessing the Trello API

Firstly, create a new Retool Workflow and make the start trigger a webhook. We will need the webhook URL later.

After signing in to Trello go to https://trello.com/power-ups/admin/new where you will need to create a new Integration (it says Power-Up but that's something else.)

After creating your Integration, go to the API Keys section and generate a new API key.

Press the Token link to manually generate a token, since this project won't need to have users authenticate to the integration. The personal token will suffice.

Next step is to authenticate with your Trello account.

Then, you should be presented with the generated token!

Successfully generated the Trello API token!

This token can be used when making calls to the Trello API. The first call we will make to the API is to register the Retool Workflow webhook with Trello. We provide the webhook URL (from previous step) as callbackURL and pass the id of the model (the Board) that you want the webhook to run for and the API key and API token. Repeat this step for each board that you want a report for.

curl --request POST \

 --url 'https\://api.trello.com/1/webhooks/?callbackURL={callbackURL}\&idModel={id}\&key={APIKey}\&token={APIToken}' \

 --header 'Accept: application/json'

Now that the webhook is registered with Trello we can continue to build out the Retool Workflow.

Workflows

Retool Workflows is a really powerful tool which allows you to build and run tasks in the cloud using a low-code interface. We will create two separate Workflows:

1. Summarize the tickets from the Trello API using the Retool AI step and then saving to Google Sheets.
2. Send out an email for each project of that weeks saved tickets in Google Sheets, then clearing the sheets.

You can download both of these workflow templates at the end of this article so that you can simply upload them into Retool and configure for your own use!

Summarize completed tickets

Whenever a card on a board that was registered in the webhook step is updated, it triggers our webhook and initiates the Retool Workflow.

The workflow specifically checks if the card's update was the card being moved, and if so, checks if the status has changed from "In Validation" to "Done." If this is not the case, then a successful response is sent back to the webhook, stating that no update was made.

If the condition is met, the workflow retrieves the card using its ID and passes it to the AI step summarizeCardDescription.In this step, GPT-4 is used to summarize the card's description, which is then appended to the appropriate tab in the Google Sheets.

Description summarized using GPT-4, then appended to the correct Google Sheet tab

Send email reports

Retool Workflows start trigger lets you set a a schedule of when you want to run the workflow. We will use this to schedule our workflow to run every Friday at a set time when we want to send out our reports.

This workflow begins by gathering all the project names from the tabs in our Google Sheet, utilizing the getProjectNames script to extract these names. It then employs a loop step to iterate over each project name, fetching the data from the corresponding Google Sheet tab. This data is filtered to identify which projects received updates.

Email step inside a loop, sends out reports for each project with updates, then all project tabs get cleared

For those that did, we use the Retool email step inside a loop to send out reports, incorporating the summarized descriptions into each. To prepare for the following week, the workflow concludes by clearing all entries from every tab in the Google Sheet, again using a loop.

Email template

Conclusion

Below are the two Retool Workflows I built for this integration. Feel free to use them for your own reports - it should be as simple as importing these .json files into Retool Workflows and updating the steps with your own Google Sheets, Trello Boards and email addresses to send the reports to. 

Need help with setting this up? Looking to simplify your workflows with automation? Let’s chat about how we can create efficient, tailored solutions for your needs!