As the owner and moderator of a 100+ member group chat, I quickly found it necessary to develop some means of automating certain processes, such as giving certain information, keeping track of members, and provoking new discussion. The app that this bot is built for is a language learning and exchange app called HelloTalk – specifically the web version as this would mean I did not need to reverse engineer the app.
The functionality of the bot is comprised of three separate parts:
- The PHP based back-end communicates with a MySQL database that contains a list of all current users in the group, along with their given country. Any more complex commands parsed by the front-end will make a call to the database through the PHP page.
- A Google Sheets spreadsheet that stores a list of members in a much more readable and legible format
The bot is first injected into the page using the browser console, and checks for new HTML elements of the message class. If any new message contains any of the commands listed in the list from the image above, it will respond accordingly. For example, in the image, I have used some game commands, such as /rps and /flip. The functionality of these commands is very simple, and boils down to a random integer generation to respond.
The other HTML element that the bot tracks is each user of the group. When a user joins, their image and name is added to the column on the right of the screen, which the bot then subsequently reads and compares to an existing list of usernames. If a new name is detected, or an old name missing, the bot announces the joining/leaving of the user.
As HelloTalk does not natively announce when a user leaves the group, it is much easier to track user counts with this feature. Once the bot has announced the change, it creates a HTTP POST request to a PHP page which then executes a MySQL query to update the database.
Back-End and Database
The back-end uses PHP and links to a MySQL database. By using HTTP POST, the username and country of the user are inserted into the database. This has several uses, such as tracking user counts, and ensuring that the same user is not invited to the group again should they leave. The majority of the file consists of SQL insert and delete queries.
One issue I encountered when preparing the user data to be sent to the database is that in the web app, the only way to programmatically tell where a user is from is by the codepoint of the emoji next to each user’s profile picture. For example, the bot’s profile is set to the UK, and has the Union Jack emoji. The respective codepoint for this is “_1f1ec-1f1e7” as found in the HTML element.
To allow for the storage of users in the database in a readable form, and not just by their country’s emoji code, I needed to create a dictionary of all the codepoints and their respective country names. I used this website to collect a full list of the codes and made a case statement for all the possible solutions.
Finally, once the codepoint has been converted into a legible country name, both the name and country are added – or removed – from the database. The structure of the database is simplistic and contains three columns: ID, Name and Country. The ID allows for multiple people with the same name, and for data to be modified directly from PHPMyAdmin.
The spreadsheet was originally built to be updated manually, as I had more free time to perform moderator actions at the time. However, in the run-up to University, I found myself having less and less time to maintain it. As a result, I decided to link it to the bot via the database that was detailed in the above section.
The layout of the spreadsheet is an alphabetical list of countries with adjacent fields for the name of the user from that country. This would then update separate sheets that were divided by country and region, so that I could easily and quickly check for different groups of users.
In order for the Spreadsheet to be updated automatically, I needed to employ the use of Google Apps Script to schedule a regular check of the database and to update any fields that change. To avoid having my database credentials stored in any third-party, I opted to write a second PHP page to fetch the data from the database and display it in a parsable format.
The script loops through the names column from fig.9, and for every non-blank value it finds, it then searches the fetched data to see if the name still exists. If it doesn’t, it is cleared, and if it does, the next cell is checked. For every blank value it finds, it checks the respective country for the blank cell, and if that is found in the fetched data, it adds the user from that country’s name to the cell.
The final feature of the Spreadsheet is a daily user count that is added to a dataset on a separate sheet that tracks the amount of users. This gives an insight into our past and allows us to set new targets.