A database management system entirely accessed through a third party platform.
This project was originally set up in my Attendance Tracker project. The administration team who uses this database requested a Discord bot to assist in managing this, but entirely through the chat software! I had always wanted to develop a discord bot (especially one that uses Discord’s slash commands), so this was my opportunity to do so.
Previously, I worked on an attendance tracker through Google Sheets called the Attendance Tracker. It was designed to be accessed directly through their site for all inputs. Everything was done manually through Sheets, including adjusting member activity trends and adding/removing new members.
However, my goal was to make the entire system automated and easily accessed through Discord, which is where most of the current users are gathered. Fortunately, Discord offers many solutions for accessing data. There are two primary types of backend communication that Discord supports: One-way and Two-way communication.
Webhooks allow data to be sent to discord in any particular format you choose. This is done through a regular post request. In Google Sheets, this is done through their servers, so no server hosting is required. However, all sent information is non-interactable, so it is only suitable for notifying an admin team of updates, errors, and trends in the database. For instance, we have a notification system created which immediately notifies the team if a user changes their name.
Webhooks are an amazing tool for sending error notifications. They do have to be configured in the code manually to display a custom error message, but this proved to be incredibly useful for debugging and general day-to-day use. It automatically sends the IT team a notification that something went wrong and the particular reason why something could have gone wrong.
The beauty of Webhooks is there is little security risk involved. The webhook token is nearly impossible to crack. Even if someone were to get ahold of our webhook token, it can immediately be regenerated to nullify the old token. Webhooks also provide an optimized way of sending information. If a particular admin functionality only needs 1-way functionality, it would be best done through Webhooks instead of the Two-way communication system. This puts less stress on the Two-way communication method.
Google Apps Scripts have a neat feature called Time triggers, which run a particular function at whichever frequency you choose. This proved to be incredibly helpful for the Webhooks, which I chose to display outputs every day at a particular hour. Google Apps Scripts uses NodeJS for its code, which is a language I have grown quite fond of.
Overall, I used my Webhooks for the following functionalities:
Bots are discord’s solution to Two-way communication. Similar to Google Apps Scripts, I can automatically run a function every day at a particular time. But now, I have the option to also run the it whenever I want. Using Discord’s slash-commands system, I was able to create commands with the format /commandname to edit or check the database.
Bots, however, need to either be hosted through a third-party server hosting service or by running the script on a local PC. The worker script needs to be constantly running, or the bot will not function at all. For the server hosting service, I chose Heroku since they generously offer 24/7 server uptime at no cost.
The hosting service uses the Heroku CLI, which is a github-based repository extension, specifically designed for Heroku’s services. Just like with any repository, changes need to be added, committed, and pushed to a live build. Since this is a third-party hosting service, a requirements file needed to be created to install any necessary pip packages.
Once the hosting was tested and ready, I took the server offline to begin developing the bot. Updating the server is a long process, so it only made sense to run the bot from my computer when I needed to test it out and debug. Overall, I went through about two weeks of developing the bot’s code, writing over 20 commands to be used by public users and the admin staff, which consisted of 2000+ lines of code.
While testing, I went through a notoriously difficult process of linking the Google Sheet and the Bot. Not only did Discord change how discord.py functions, but Google also changed how service accounts would be accessed by integrations. There were few guides and documentation available, so I had to manually test and debug until it worked. It was an incredible sigh of relief once I had it working. There were many steps of integrations, authorizations, and linking keys for the bot to read/write in Sheets.
With this complete, I could finally create methods of reading/writing the sheet in particular contexts! For instance, I created a tool that let the admin team log an infraction for a user’s misbehavior. The bot then automatically places the respective information in the sheet. Now, the team could automatically see how many infractions any user has.
Similar to the webhooks, I created a logging system for each command, denoting if it ran successfully or if it ran into any errors. All error and logging messages are placed in a specific channel, which assisted in debugging.
One problem I ran into was database optimization. The sheet started to become sluggish with thousands of empty cells sitting, so I added a method that created new rows for each new input. All formulas were also dragged. The Google sheet is doing well to this day!
Overall, the following commands were added, consisting of approximately 2,500 lines of code:
With these commands, the admin team now has full control over the database without needing to open up the sheet itself. This was an incredibly fun project overall. I learned quite a lot about integrations and server hosting. I will likely be creating another bot in the future again! Below is a gallery of some of the features included with the bot.
