Building a Discord Bot: Integrating Google Sheets as the Database

Discord.js allows developers to interact with the Discord API to create bots to perform various operations. It provides a wide range of features, ranging from user management to server or guild management.

For this project, initially, following the discord.js documentation, a basic layout for the bot was set up, which included creating a file interactionCreate.js that would handle interactions created by users, ready.js that will display a successful login, and a deploy-command.js file that will search for all the commands that would later be added to the project.

Creating Registration Form

The main goal of this project was to open a form that would act as a registration form, implemented with the help of the ModalBuilder class provided by discord.js. The input field was created using the TextInputBuilder from discord.js. The field defined with TextInputBuilder was added as a component and then injected into the modal created earlier. This information was all written inside a command that would respond to the interaction, reply with this modal, and automatically close after a certain interval. These fields also had text validation.

Firstly, the data was saved in local storage in CSV format. Additionally, a layer of validation was added to iterate through the data and in case of duplicate results, it would throw a user-defined error.

For storing this same information in Google Sheets, a method was created with the help of googleapis. This method takes the specific spreadsheetId, data to store, and a specific sheet name and row.

There was also a feature that allows users assigned to specific roles to use commands to open or close registration. To perform these operations, a specific cell of the Google sheet was read using an execute read operation, and an append operation was done to update the information. Both functions were based on googleapis.

Assigning Role

Another important task of this project was to assign specific roles based on the group assigned in the Google Sheet. When a user registered, their userId (from Discord) was extracted and saved to the storage. From the Google Sheet, a group ranging from A to G to Final would be assigned in the sheet. Then, the bot would read the column containing group information and map this column information/data to the assigned dictionary. It would extract the role ID for the specific group and assign it. For this operation, the bot would need specific permissions.

Removing Roles

In order to reset the user’s role, firstly, the role assigned to the user was extracted and a separate list was made with specific roles. If the user had those roles, the bot would remove the roles from them.

Status

There was also another feature that interacted with the database to check if the user’s details were validated and a group was assigned to them. This operation also involved mapping the userId to the spreadsheet record.

Show Group

This part was dedicated to reading information from Google Sheets and creating a dictionary with the group name as the key and a list of information such as team name and slot as the values. These values were sorted according to the slot and replied back.

Difficulties faced

During registration, when multiple users interacted with /register, data mismatches occurred where the same information was written twice. To address this issue, a data structure of type Map called pendingInteractions was created. It acts as a temporary storage mechanism for managing and processing user interactions.

Also, to interact with Google Sheets, a specific Google project was created and added to the Google Sheet with read and write permissions. This project includes credentials that were saved and passed to the Google Sheets API, enabling interactions with the spreadsheet associated with that ID.