How to Easily Create a Prototype of a Convenient Dashboard in Your Trello Using Google Sheets and Google Apps Script
One of the popular tools for organizing work processes is Trello, but its basic functionality doesn't always meet all the needs of companies. We will explain how, using available tools like Google Sheets and Google Apps Script, you can create your own Trello Dashboard for convenient data tracking and analysis. Learn how our solutions allow you to customize the dashboard to fit specific business requirements, saving resources and increasing team productivity.
The Need to Expand Trello's Capabilities: Initial Tasks and Solutions
The task was to create an auxiliary tool for those who actively use Trello in their work. Although we had experience using the Planyway service, its functionality was still insufficient to meet all our needs. This led to the idea of developing our own Trello Dashboard based on Google Sheets, which would allow for efficient management of an unlimited number of boards, tasks, and projects.
Our solution was initially developed for a project manager who needed a tool to track tasks in real time. Eventually, this functionality was passed on to the team, who also appreciated its usefulness. We have plans to scale this tool and share it with other users.
Project Implementation Steps and Possible Alternatives
Several important steps were taken during the project’s implementation, each contributing to the successful creation of the Trello task management tool:
Regarding alternative solutions, we considered using other tools besides Google Sheets and Google Apps Script. For example, the idea of creating a web application was explored, but it turned out to be significantly more complex. Another alternative was the Make platform, which allows for automating workflows by connecting different apps and services. However, due to the need for substantial resources to process large volumes of data, we decided that Google Apps Script was the more optimal solution. This tool fully meets our needs, allowing not only for data retrieval and transmission but also for data storage, analysis, and dynamic real-time review.
Google Apps Script became the optimal choice for our project due to its free capabilities and flexibility in working with data. Using Google Sheets and Apps Script allowed us to create a tool that not only meets our needs but also significantly surpasses the capabilities of paid solutions like Make, thanks to its ability to provide dynamic review and effective management of large data volumes without additional costs.
How Does Our Solution Work?
Our ready-made solution for the Trello Dashboard, implemented using Google Sheets and Google Apps Script, allows users to easily retrieve and process data from Trello. The user starts by logging into their Trello account. After that, they select the necessary boards and users from a list and click a button to load the information.
This process allows the import of data about users, boards, and tasks directly into Google Sheets. The loading time depends on the number of selected boards, but once the import is complete, all data is automatically added to the database. Users do not have direct access to this database; instead, they see an interactive dashboard that displays up-to-date information.
The dashboard offers various filters that allow users to sort data by the number of cards and their creation date. One of the key features we successfully implemented, which sets our Trello Dashboard apart from other solutions like Planyway, is the ability to view comments within Trello cards. Users can see the number of comments, comment authors, card authors, and other related information. Additionally, the dashboard enables tracking how long a card has stayed at each stage of the project (in Trello columns).
We also implemented the ability to have a comprehensive overview of all data on the dashboard, providing users with a clear picture of projects and tasks. This approach significantly simplifies project management and enhances team efficiency.
Required Skills and Knowledge
To successfully implement our Trello Dashboard, the following skills and knowledge were necessary:
- Logical Thinking: It was crucial to understand how to effectively accomplish the task and develop an action plan.
- Understanding Trello API: Initially, we thoroughly reviewed the Trello API documentation, which helped us understand what data could be retrieved and how to integrate it.
- Testing with Postman: We used Postman for testing and debugging API requests, as it provides a convenient interface for sending and analyzing HTTP requests.
- Working with Google Apps Script: We utilized Google Apps Script, which allows for automating work with Trello data in Google Sheets. Apps Script, similar to JavaScript, offers flexible capabilities for creating custom scripts.
- Knowledge of Google Sheets Formulas: Using Google Sheets functions and formulas helped us create an effective and visually appealing dashboard for data visualization and analysis.
- Considering Alternative Solutions: We evaluated the possibility of using other tools, such as Make, for automating workflows, but chose Google Apps Script for its efficiency and free capabilities.
These skills enabled us to create a powerful tool for automating and managing Trello data, which significantly improved our productivity and work efficiency.
Problem and Solution in Creating the Trello Dashboard
During the project, we encountered a problem related to the execution time limit for functions in Google Apps Script. Since this tool is free, it has restrictions to prevent server overload. One such limitation is that a function can only run for up to 6 minutes. This restriction does not pose a challenge when processing small amounts of data, but it becomes an issue when working with large volumes of information.
To address the issue of time limitations in Google Apps Script, we employed a cyclic execution approach. Our solution involves having the function remember the information already retrieved and automatically re-execute the script within the 6-minute limit, gradually completing the data collection. This process continues until all the necessary information is fully processed.
We applied this method to gather all required data, using cyclic execution techniques to bypass the function execution time limit. This allowed us to successfully handle large volumes of data and integrate them into Google Sheets for further analysis and visualization.
Should Additional Tools or Applications Be Used Besides Trello, Google Sheets, and AppScript?
In the future, we plan to update information in real-time using Trello webhooks. However, we encountered an issue: Google Apps Script does not support HEAD requests from Trello, making it impossible to set up webhooks for automatic data retrieval in Google Apps Script due to conflicts between these tools.
After consulting with a Google Apps Script specialist, we decided to explore using a cloud service as an intermediary for data transmission. One potential solution is using Glitch or another cloud service—there are many options, both free and paid.
Glitch enables collaborative work on projects, real-time code editing, and communication with other team members. Additionally, Glitch has built-in tools for project management, application deployment, and monitoring, allowing quick creation and testing of web applications without the need for server or infrastructure setup.
We decided to use Glitch as the intermediary solution for data transmission in Google Apps Script and plan to transition to it in the second version of our dashboard. After evaluating various cloud services, we found that Glitch offers the optimal combination of functionality and ease of setup, perfectly meeting our needs. This approach will allow us to effectively resolve the conflict between Trello and Google Apps Script and improve real-time information updates on our dashboard.
How Was the Dashboard Tested?
Technical testing of the dashboard was conducted by the project developer, Vladyslav. Initially, all functions and scenarios of the dashboard were tested to ensure proper interaction with the Trello API and correct functioning of the Google Apps Script. After that, the project manager conducted initial user testing, provided feedback, and indicated necessary improvements.
After all necessary corrections were made, the dashboard was handed over to the team for testing. Since then, no critical errors have been identified, indicating the stability and reliability of this solution.
Client Feedback on Working with the Trello Dashboard
Working with multiple teams in the company, which deal with various assets (CRM, websites, etc.), we faced the need for rapid tracking of requirements and requests for the development or refinement of these assets. We process up to a hundred requests daily, not counting the comments within them.
To keep all this information organized, we needed a tool that would allow us to focus on specific boards (assets or teams) or on individual people (requesters or performers). This was extremely difficult to achieve using only Trello or Planyway.
So, we requested the development of a solution that allows us to see the status of requests on specific boards (including the quality of execution of each request—time on the board, comments, whether the request was forgotten) and by individuals (how many requests were completed, how many comments were written, whether there are any lost requests).
Overall, this tool allows us to track work patterns through understandable metrics and numbers in real time, helping us quickly notice deviations from the norm and prevent their consequences before they become serious problems for the product or process.
Redesigning the Interface for User Convenience
In the initial stages, users encountered difficulties when launching the dashboard, as they were not accustomed to using the top menu in Google Sheets, where extensions and other tools are located. The entire dashboard management functionality was initially configured there.
Additionally, this menu is not available in the mobile version of Google Sheets, which created extra inconvenience. We solved this issue by adding a duplicate menu directly within the spreadsheet. This proved to be so convenient that even desktop users started using it to configure the dashboard, opting for it over the traditional top menu.
Adapting the dashboard for other companies is straightforward task
If a company uses Trello, the adaptation process is very simple. With just one call, we create a project spreadsheet, providing all necessary access and instructions. The full adaptation of the solution "turnkey" may take only a few hours or days, depending on the scope of work.
If the company uses another service, such as Jira, the process may be slightly more complex. It is necessary to conduct a detailed study of Jira's documentation to understand its workflow logic. If it is similar to Trello, it will only be necessary to modify the requests to Jira. In such a case, the adaptation may take about a week.
Jira offers extensive customization options to tailor workflows to the specific needs of a project or team, and it integrates with other software development tools like GitHub, Bitbucket, Confluence, and more. Jira helps developers and teams efficiently manage projects, increase productivity, and implement best practices in software development.
Team and Workflow Organization in Creating the Dashboard
The project was handled by a team of two: a developer and a project manager (PM). The developer was responsible for all technical aspects related to coding and developing the dashboard, while the project manager focused on the visual part and overall coordination of the work.
We worked using the sprint methodology. At the beginning of each week, tasks to be completed were identified. After that, we conducted a sprint, evaluated the results, made necessary adjustments, and started a new cycle. This approach allowed us to gradually improve the project and quickly adapt to changes.
The dashboard was developed in two iterations. Initially, a basic version of the dashboard was created, which worked and provided the core functionalities. Then, we enhanced it to the final version, adding new features and improving existing ones. The overall development process took about one month.
Conclusions and Advice
From our experience, it is clear that dashboards are extremely useful tools for tracking and analyzing data. Don’t be afraid to add new features based on existing services like Trello—it can be a very productive and straightforward process. The development of tools like Google Sheets and Google Apps Script allows for the creation of additional capabilities to solve specific tasks without the need to build everything from scratch.
Three Tips from the Developer
What Else to Read to Build a Trello Dashboard
Creating a free dashboard based on Google Sheets and Apps Script allows for significant savings on paid Trello features and other services that charge a monthly fee for Trello card reports. By using available tools, you can effectively implement additional features without extra costs, ensuring full control over the process and optimizing the budget.