What started out as a manual tracker eventually grew to become a massive API-driven attendance tracker. In the game, RuneScape, clan members work together to “cap” certain skilling points to help grow their clan castle. In doing so, clan members receive access to special rewards that are given by the administrative team. However, doing so for a clan of nearly 150 members quickly involves a lot of tracking. Thus, the project was created.
An automated solution
The project takes place in Google Sheets, which primarily uses Excel code for functions and Javascript for VBA custom add-ons. This attendance tracker denotes my first significant portfolio-styled project in Google Sheets. Once the project’s idea was formed, the next task was turning it into a reality. Since I had previously used Google Sheets for several smaller projects before, I chose to use this platform again to create the tracker. Sheets has a convenient way to retrieve data from APIs, so I already knew the project could be completed.
The project started with the Historical Tracking, Data, and Statistics sheets. The Historical Tracking sheet was needed for long-term data storage for historical trends of veteran members and for tracking members who left. The Data sheet was used as a hassle-free system of displaying the current month’s trends, later used in the Statistics Sheet. While the Statistics sheet is capable of tracking progress throughout the entire year, most trends of interest for the administration team only involved the current month.
The Statistics sheet displays the clan’s numerical trend information. The primary trends calculated and displayed are:
Total clan members
Total active clan members
From this, a percentage could be created to see how many members don’t play
Total clanmates capped
From this, a percentage could be created to see how many active players actually cap
Weekly recruiting
Derived from seeing if the user had no pre-existing data from the previous week.
Once these three sheets were created, smaller add-ons were created for specific needs to the sheet. For instance, in the Points sheet, I added a method of tracking and calculating member ranks and promotions. Later, I added the Blacklist sheet to display any members that had left the clan, were banned, or had to take a vacation leave.
Specifications
10 sheets
Historical Tracking – Presents all historical data tracking, saved week-by-week by a time-based function in Google Sheets Scripts/VBA with JavaScript.
Override – Provides a way to override any output the tracker produces that may be incorrect.
Results – Displays relevant capping information for the week
Capped – Displays who capped and provides the user the opportunity to capture realtime cap data
Citadel – The main data collection sheet. Collected data from the RuneScape Clan API and the RuneMetrics API, and filters information into a display from which trends can be generated.
Blacklist – A historical collection of inactive users generated automatically by the script for the administration team.
Raffle – A feature that pulls data from the Points sheet which allows an administrator to simulate a weighted, randomized raffle depending on Fealty. Each level of Fealty denotes how many entries are placed in the raffle.
Statistics – A display of the previous two months’ data trends.
Points – Each member’s rank, fealty, and status information for the administration team. Additional features include an automatic “clan XP” calculator to display how close someone is to levelling to the next rank.
Data – Displays relevant capping information for each member for only the current month.
