Our Startup provides services that involve handling a large number of customer needs and orders and putting them in a system that can help us efficiently handle each one of them. This has been a tedious process because we spend a lot of time putting together requests, categorizing them into categories, and finally placing them in Trello, a task-planning tool created by a company that makes things easy to do.
We resolved this by developing an API, which takes advantage of widely available public resources in a creative way. Therefore, we can now easily monitor all customer orders and tasks in one place, and stay well organized within the company. This is an excellent tool that provides solutions to a wide range of problems relating to business, industry, and personal aspects of life.
How does it work?
After a customer fills out a form from our website making a request or ordering something, their responses are automatically saved to a 'Google Sheet' and organized into a Trello board.After that, we use the Trello board to quickly address customer requests by assigning them to the right team, by specifying deadlines and delegating tasks to them, which enables us to save time.
This can be used to organize spreadsheet data, maintain lists based on keywords, and a myriad of other tasks dealing with giving tasks to others or organizing Google form responses and spreadsheet data. This hack does not require a database for collecting orders and does not need complicated scripts to put the orders into an organized place which is a very significant achievement for our business models.
How did we build this?
Before reading the following section, make sure you become familiar with the terms used by the Trello web app, such as lists, cards, and more. Click here to take a Trello tour and learn more about these terms
We wrote the script that calls APIs in Python. We made sure that it uses IFTTT, a web-based service that allows users to create chains of conditional statements, to trigger a new row in Google Sheets and sends an email with the number of the new row in the subject line. The script extracts the row number from the email and using the Google Sheets API, it will check the responses to certain cells to determine which list and which cells to pull data from.
After the data is fetched, we made sure that it is split into variables which can then be sent to Trello. Usually, it takes a lot of time and work to actually create the cards correctly every time. The program starts off by requesting a new card from Trello via the API. The JSON response is then split into a list, then into variables to get the Card ID and the List ID. With this information, checklists can be created on the card to determine the exact needs of the customer. Therefore, we make another POST request and create a checklist and then split the JSON response to get the checklist ID.
Then we make more POST requests to populate the checklist with the customer's contact information, as well as the information about the order or the request. The information that populates the checklist comes from the variables collected earlier using the Google spreadsheet.
After testing it out on google forms, we built an HTML form and connected it the Google back-end, so that the responses from the custom HTML form could be saved into the spreadsheet along with the google forms equivalent responses.
Below are short instructions on how to use our Postman Workspace.
Postman is an easy to use IDE for Web Services and APIs. Postman will take the API request that you make in one language and turn it into all the other languages via its API. You can use Postman to connect many web services together using their API. Software developers will find this feature useful for adding API functionality to different aspects of what they develop. More recently, Postman added some really useful features that allow developers to work together in one location. In Postman, you can not only use APIs, but you can also build your own! This IDE provided us everything we needed to build, test, and deploy our API.
These are the steps to set up Trello so that it can be used with our scripts. You will need to acquire Trello API credentials and add them to Postman as variables before you start this.
Use the create board request in our “Create Board” collection to start off. The parameters for the board are API boards.
Create a list using the BoardID acquired from a JSON response after sending a POST request to make a board. Creating a list is easy, and the parameters can be found API lists.
Using the ListId Acquired from the JSON response, make a card, and specify which list by creating a parameter called “idList” and adding the list id acquired from the JSON response. For a full list of the parameters used to create a list refer to API cards.
After creating the card, the checklist needs to be made, and the Card ID you receive in the JSON request can now be used to create a checklist on the card you just created. For a full list of parameters to add a card refer to Checklists.
After the checklist is made, copy the checklist ID from the JSON response, and then send a response to add fields to the checklist. The format you should follow is Field Name Field Value. For example if your name is John, you use the name parameter and write ‘Name: John Doe’. If you want to use an email address, you use the name parameter and write: ‘Email: Johndoe@x.com’. For a full list of params, refer to this list.
In the Postman workspace, we also added Stripe API requests to show that a business can use these to collect payments from customers using the Stripe API. This system can then be used to move money around or automatically send payments, arrange customers on Trello lists, or even arrange sales. Theerfore, the Stripe API gives this workspace high business value.
Challenges we ran into
This was the first time using an API for the team and we had to use three APIs to complete the task. The Google API was straightforward and easy to set up than the Trello API. Postman website helped us to figure it out and we were able to eventually build the system completely.
The amount of work to figure out how to split the JSON responses into variables, extract the right variables, and organize them based on keywords was also a challenge and took a lot of trial and error. The sheer amount of code that had to be written to deal with the responses and use the API correctly every time took about 400 lines of code and was challenging, but fun in the end! IFTTT also has a feature to collect the cells from a google sheet, but only for A-K, so big systems won't work.
Accomplishments that we're proud of
We were able to create a working system that can help teams of online vendors and freelancers to get organized, save time, and allocate it to other useful activities. The sheer amount that it took a person to create Trello lists and organize them depending on their google form or google sheet data is nonexistent now because it is an automated process.
We are proud of the system that we have made which can be implemented to work with more systems other than google forms. We learned a lot on how to build APIs by using Postman effectively. This was a big win for the startup team and this will help us to expand our business and stay organized at the same time.
What we learned specifically
- How to build APIs
- How to make JSON requests.
- How to use Postman effectively.
- How different APIs use different formats.
- How to collect google sheets data.
- How to split JSON responses into variables.
- How to pass variables in the JSON payload.
- How to use IMAP.
- How to remotely control Trello via their API.
- How to use a Google API.
- How to make API calls effectively.
- How to build a reliable system.
- How to make a POST request.
- How to make a GET request.
- How to handle customers and payments remotely
- How to automate the movement of monetary assets
- Teamwork is important.
- Hard work pays off.
What's next for Google Forms/Sheets To Trello
We hope to win this hackathon and use the financial means to expand our Digital Product/Software Business. This is a very big idea and this API system is just one of the major groundwork required in our upcoming giant business structure which leans to automation and assigning human capital to other activities which matter. We hope to offer jobs to developers all around the world and help businesses, companies, entrepreneurs, artists, and many more people to reach success by equipping them with the tools they need to realize their dreams of changing the world.
Where can this be applied?
- Medical Field
- Engineering Field
- Data Science
- Education Systems
- Companies (Large, Medium, and Small)
- Online Event Booking
- Custom Order Systems
- When Combined with a payment, this could be useful in front-end systems of a consulting Business.
- Organizing Information about organizations, events, and more.
- Storing information without having to build or set up a database.
- Sales: For an example click Here
- And more
The Demo for you
We have prepared a demo in which the same system is used. This is good because you don't have to fill out a long-form to see how it works and how reliable it is. If You Choose to do the Demo, your data will may be made public on Trello (we encourage you to avoid using your sensible personal data because this is a demo), this may include but not be limited to: Your Name, Email, and some opinionated data such as your favorite color, animal, or food.
After submitting the form, you will be provided with a link to the Trello board, and after 2 minutes, you should be able to see your responses from the form turned into a card on the corresponding list on the Trello board.
Because we use IFTTT there is nearly a 3-minute time slot from the time you make a request to the time it sends the email. So while using the demo, your patience is a necessity to get accurate results. However, the Trello API definitely makes up for this time because it is super fast. Therefore, there is no need to reload the Trello board, watch it automatically updating itself :).
Documentation Is Provided In The Links Below
If you would like to keep up with us and our progress, Please review the information below.
- Email: Splurket@gmail.com
- Instagram: https://Instagram.com/splurket_store
- Twitter: https://twitter.com/Splurket_Store
- Discord Community: https://discord.gg/ae6ADnFPHc
We hope you enjoy our precious system and if you have any suggestions or would like to contribute, don't hesitate to reach out, we would love to hear what you have to say-and improve!