ADV Draft Baseball League Other

Introduction

I've played League of Legends since September 8th, 2012 -- nearly half of my life up to this point. For nearly as long as I've been playing League of Legends, I've been what players call a "one-trick", someone that specializes in playing a single character while leaving the others to the wayside. This character and role, Vi as a Jungler, would make up hours upon hours of my League gameplay. I estimate that I've played close to 3,000 games as Vi (probably more), and I think that learning to play a character as deeply as I have Vi has helped me in both climbing rank (I can't hit Diamond as anything else) and in other facets of life. However, I do know there's limits to this--what happens if I'm in a bad matchup? What if Vi is banned? I knew that after years and years of playing League, I wanted to branch out and experience this game I've been playing for so long to its full breadth. So, I sent a text to my friends that I wanted to start playing for Mastery after my finals finished on June 8th, 2021, and started tracking my mastery from then.

Here, I'm going to attempt to write a history of how far this spreadsheet has come since its inception, and how it's tracked my growth as both a League player and a problem-solver as I tried to improve it. At this point in 2021, I didn't know much about Google Sheets' App Script, and design principles, or anything about the API--now, I can look back and be proud of what I've created here.

The Old Workbook

MSheet had quite the humble beginning. It was a single sheet in a Google Sheets workbook that I manually updated with my mastery on each champion after each game. Looking back on it almost gives me whiplash, like I'm turning through an old yearbook or moving through some ruins.

This was an intuitive start, and something that I kept in mind and updated while playing. I'll call this Version 0 of MSheet, as at this point it was nothing special or complex--it was also in its purest form at this point, as a true Mastery tracker and nothing else. At this point, I have Google Sheets' revision history to guide me through a timeline of features added (this is why these screenshots are purple). Some years ago, I decided to name the version at the top of the month in case I wanted to look back on things. Very smart by my past self, I'd say.

Within the month, I had added (also manual) tracking of champion tokens and the in-game currency needed to upgrade their levels, as in the old system you had to earn tokens and spend blue essence (BE) to upgrade from level 5 to 6, and 6 to 7. This would be a backbone for some functionality later down the line, but also stubbornly remained the only manual part of the sheet until the systems changed as there was no way to pull a user's total number of champion shards.

The July 2021 version also had rudimentary stat-tracking. Nothing spectacular by any means, but distribution of levels and the percent progress is something that remains in MSheet to this day.

By August of 2021, I was starting to look ahead by creating a schedule for myself to play enough games a day to reach certain level milestones on certain champions to keep a good pace during the summer. I had worked for a 10-week shift as a researcher and had nothing else to do that entire summer, so I made myself a budget for five wins a day (which, in hindsight, is insane).

I made this budget by using a friend that will soon become familiar: an estimated wins needed counter. I decided to initially call it WRn, which I believe stood for Win Rate Needed (in hindsight, this is dumb as it's not Win Rate but Wins themselves, but I digress), and set a win to be 850 mastery points. This, as I was to soon find out, was a fortunate undershot. It was at this point I decided to call what I had the "Mastery Sheet."

On August 18th, I started tracking the amount of Mastery gained in a win. By September 1st, I had 28 wins of data to play with and the average game was an A+ grade that gave 967.46 mastery points in 1780.25 seconds. Pretty good!

To my surprise when looking back now, I was keeping pretty tight to the schedule I had set. I remember the summer of 2021 being quite eventful, but I guess I had the time to play a few games of League every day. However, school was about to start on September 15th, so I slowed the scheduled cadence to getting the 7 wins necessary to take every champion from Level 3 to Level 4 in 3 days. This is around 12 games of League every 3 days with a 50% win rate, so I have no idea why I thought I could realistically stick to it.

On the October 2021 version, the scheduling spreadsheet mysteriously disappears. I wonder why! I also start to use it to keep tabs future champion releases, based on Riot's teasers that they put out. I wonder what the "Zaun Marksman" and "Villain Support" will wind up looking like? The win tracking study is up to 77 games, with the average game now being an A+ grade that gave 968 mastery points in 1773.71 seconds. I also start to see the logistic trend in the graph.

At this point, I conclude the study with the last games played on September 29th and accept 970 in a 30-minute game as the benchmark. This is what I had used up until the very end of the first iteration of champion mastery.

Nothing changes too much from here until February 2022. I diligently, and manually, copy the table from championmastery.gg into my spreadsheet daily or near-daily. It's at this point I decide to track more than just mastery points on the spreadsheet and make two new tabs in the workbook: one to track progress in the Lunar Revel Event Pass (that I turned entirely into champion shards for future mastery upgrades), and another to track my win rates in solo queue for that season as I wanted to branch out and play champions not named Vi in ranked. Spoiler alert--it did not go well.

I consider myself to be able to play Sejuani, Lillia, and Vi in my ranked games at a Diamond level, as I'm ranked now. I cannot say the same about the other 9 champions on that list. This is also the point where I decide to store raw mastery data separate from the sheet itself. This way, I can manipulate the latter without having it ruined by putting in new mastery data. This is also around the time I learned what an XLOOKUP was and was loving using it. I also edited the Token and Shard sheet this month, calculating the amount of champions I could level up for free using my newly earned Lunar Revel shards.

On May 11th, 2022, Challenges were released into League of Legends. As a completionist that was trying to "beat League of Legends," I ate these up immediately and quickly turned my attention towards completing challenges and shooting up the leaderboard. As of the time of writing, I'm within the Top 50 in North America and am on pace to hit Master rank by the end of the summer, so I believe it's safe to say I've done a good job completing them. Seeing as I had my spreadsheet track more than just Mastery now, I changed its name from "Mastery Sheet" to "The League Sheet" and started using it to track progress in both Challenges and League's other champion-specific progress system, Eternals.

I quickly ditched the latter as it became too cumbersome to track but kept the challenges around. Humble beginnings for this part of the spreadsheet, too--at this point, it was a list of the champions in the game with my progress in each challenge marked next to them. In June of 2022, I made another spreadsheet called "Challenges II" that (again, manually) tracked the progress for each of my challenges. It was, in a word, time-consuming.

I renamed the old Challenges spreadsheet to "Challenges I," and additionally created a spreadsheet for the ongoing Blue Essence Emporium, as I also had ward skins and icons to buy for even more challenges.

The infancy of challenges was a very exciting time, as people banded together to tackle the challenges that required a premade team of five and created communities around this goal, finding bugs in challenges, and tips and tricks in completing them. Of course, there were the more dubious parties that would manufacture fake hours-long 5v5 games to plow through tens of challenges at once, but I stayed out of these parties. Regardless, I got roped into these communities and by August 2022 I was an active participant in trying to get through all of these challenges. By the time I finished all the ones a five-man party was required for, it was June of 2023. This takes me to the end of this workbook, however, as it was linked to my college email which had the clock ticking on the time I was allowed to use it. So, I copied everything I had over to a new workbook (my current one!) and continued to tinker with what I had.

Mastery Sheet, Version 0: The New Workbook

In October of 2022, I moved to Chicago, got roped into social obligations and learned to truly live as an adult and as a result I did not have time to play much League. During this time, though, I did take interest in the actual historical data over the year-plus I had spent on progressing mastery thus far and created a "Historical Data" sheet where I stored the mastery totals for each champion at the top of the month.

The historical totals section tracked my percent progress to getting all champions to level 5, as afterward points did not matter and I had to get Mastery Tokens instead. I also calculated the total WRn and points needed to this goal, as well, and used some of my first Google Scripts work to create a function to do so on this sheet. Finally, I kept track of the number of champions below level 5 and, my favorite stat of all on this sheet, the percentage of my points associated with Vi.

I play a lot of Vi. I love the character, and quite literally have her tattooed on my back. At the beginning of this grind, I had 46.82% of my total mastery points--nearly half!--on just one character. By November of 2022, this was at 37.53%. Presently, as of June 15th, 2024, it's at 31.52%. A huge drop-off caused by me exploring the breadth of one of my favorite games, which was shocking, humbling, and impressive to see. It's always very satisfying to me when a huge data set coalesces like this.

In January of 2023, I created what would eventually become the pinned tab on my web browser and the sheet I visit the most in the entire workbook: The Mastery Hub. My goal with this was to create a dashboard (partially inspired by my job) that I could go to and figure out which champions I wanted to play. The beginnings of this were small but do still resemble parts of Mastery Hub today.

My goal was to be able to take a quick look at my closest champions to level up. I became very familiar with the FILTER and QUERY functions while doing this, which has been nothing but a boon for me when it comes to working in Excel since. Over the course of the month, I added more to the hub such as the starting stages of a dictionary for champions that contained role information. By February, it was looking much more like the current version of the hub, with roles highlighted next to champions and the projection data being much more accurate.

I also tried creating another hub in my hub-mania: a Solo Queue hub, where I put my match data from solo queue games in and tried to display it. It wound up looking a bit bloated, and as I've moved my focus away from ranked games I abandoned it after a few months of use.

When it came to collecting data to feed into the hub, it was also quite cumbersome--as was the norm for the rest of the Mastery Sheet, all data was entered manually, and I had around 30 fields' worth of data to enter to get the information I thought I needed. My sample size was too small for actual analysis to be had, and there were plenty of websites such as League of Graphs that did what I was attempting to do here but better.

In June of 2023, I made an advancement that would change MSheet forever for me: using it as a web scraper to pull data automatically. No more manual entry and copying data from champion mastery websites--rather, I can just take data from the websites themselves! Using Google Sheets' IMPORT functions, I was able to get data from championmastery.gg and format it in a way that my Mastery Sheet could ingest for the Mastery Hub. Google Sheets refreshed the data occasionally, at a cadence of multiple times per day, which was good enough for me at the time. It was all coming together at this point, and I knew that I had wanted to give copies out to those in the challenges community that were asking for it. I decided to call the newly-automated sheet "MSheet", its current moniker, and spun off a copy of my personal spreadsheet to be the "public release". At this point, I only had Mastery information on there and I was still updating challenges manually. However, I knew I had automating challenges on the horizon. I also changed the name WRn to EWN, or Estimated Wins Needed, as this stat was simply misnamed.

MSheet, Version 1: Automation and API Pulls

Publicly releasing MSheet for the challenges communities I had mentioned earlier was a very exciting time for me. Sure, only a few people that I know use it, but the fact that the collective hundred hours of work I had put into it had a public product people could download and use felt very cool to me. An issue I did see, though, is that I released it to the challenges community but had not included any way to keep track of challenges.

My first step was reworking how I was storing challenges information. Working with the creator of the main challenges-tracking website, the talented and great teacher Darkintaqt, I found a way to scrape challenges data for certain summoners. I stored this data in a separate spreadsheet and created a new sheet that I called "New Challenges II" (to pay homage to the old, hulking, manually-populated document) that read from the raw data to format the challenges in a prettier manner, which very much resembles the challenge-tracking sheet of today.

Now that I had this information, I began constructing a Challenges Hub that would be the mirror of the Mastery Hub. Unlike the rest of the sheets in the workbook, the beginning of Challenges Hub looked quite similar to the Challenges Hub of today--I have not had to change much about it at all and have only added quality-of-life features since.

At the time, I was working on the challenges that require players to win 150 games as each of League's five roles and decided that would be relevant information to include on the left side.

The Mastery Hub also got a bit more built up during this time, and I included new features for myself such as rolling averages and projections using those rolling averages. If I spent a year playing four games a day, and suddenly jumped up to a sustained period of six games a day, the overall projection becomes a bit more inaccurate. The goal I was aiming towards became very clear: some time in 2025, I will have achieved getting Level 5 on every champion. I did this by storing the total number of points I have at the end of each night in a separate sheet.

I also added a section that showed me champions that I could level up past 5 quickly, given that I had a bunch of tokens for them already. It was sorted by the number of shards required to get to the next level first, then level--so it showed level 6 champions with four out of five total tokens first, then level 5 champions with one out of two required tokens, et cetera. I released the Challenge Hub and more updates out to the public version of MSheet on August 2nd, 2023. I also created a new sheet with this version called "Starter / Guide", where users could enter relevant information such that MSheet worked (before, this all was in the Mastery Hub).

In September of 2023, I got what I can only call "Filter Fever," and added checkbox filters to both the Mastery Hub and Challenges Hub. Did I get filled to the Top Lane and needed a champion to play? No worries, as I clicked checkboxes accordingly and now Mastery Hub only showed top laners.

Editing the queries to support the filters was some of the most satisfying work I've done on MSheet. Seeing the queries work with a bunch of wildcard characters and conditional statements felt amazing, and toying with them to squash bugs makes the hours go by quickly and happily. Fiddling in spreadsheets is my version of something like sewing or crocheting—a time-consuming activity that has an end product that I can enjoy. Around this point, I also added the champion-specific challenges that I manually track onto the Mastery Hub so I could see the challenges-based value in playing a specific champion.

In the Monthly History sheet, I added another statistic that I like looking at: Unique Champions. The title is self-explanatory: how many unique champions did I play in the month? My record so far is 108 in December of 2023, and my average is 49. Maybe one month I'll try to hit them all.

Around this time, I also added classes (different than roles) to the Champion Dictionary, as there are challenges that require getting Mastery 7 on certain champion classes. There are six that exist: tanks, assassins, marksmen, supports, mages, and fighters.

This means that champions now have 11 boolean properties (6 classes and 5 roles) that I can represent cleanly on the Mastery Hub with dots. From here on, not too much changed with MSheet as a whole--new champions were announced and added, various filter and sorting options were added, but the general structure of the workbook and sheets within stayed rather constant. I was happy with where things were at and diligently worked towards my goal of hitting Mastery Level 5 on every champion, until the entire system was due for change.

MSheet, Version 2: The New System

When the new Champion Mastery system was announced, I was approached by a few people curious about my goal. I've decided to track my progress to Mastery 10 on each character instead of Mastery 7, as this is where both the challenges and new mastery emotes end. Anything past level 10 is the same emote but with a number indicating level.

Converting what I had from the old system to the new system was not very painful, thankfully--I decided at this point to fully lean in to learning how to use the Riot API, which thankfully worked similar to the current web-scraping system I had, and I was able to pull all relevant information and create new progress tracking for both the new milestone system and individual games. I was finally able to shed the "Token and Shards" sheet, as Mastery Tokens no longer existed and milestone progress was pullable from the API. I was able to combine the recommend champion sections of the Mastery Hub into one, and added a more elegant query that allowed me to add new options for users to sort on milestones, points, marks, and EWN. At this time, I also added an "Update" button connected to a Google Script that I could click to refresh the API pulls--something that has proven to be very useful, as I could refresh on command without digging into the pulls and manually deleting and repopulating the cell.

I also worked to figure out the most important part of MSheet with the new system: the projections. I wrote about the new mastery formula I derived on my website, and thanks to Wayne and others in the challenges community I was able to get a formula to a degree of accuracy I am satisfied with. It is possible I revisit this in the future but am happy with how things currently exist. As a result of deriving this formula, I made a change that I was itching to make for a bit: tracking progress between either wins (as I had previously), or games. I always track my own progress with games now, as I believe it gives a more realistic projection.

I took this time to fully theme MSheet instead of having a theme that changed throughout the patches and various events Riot puts in the game. Seeing as the new system was already themed around the mystical Mount Targon region of League's universe, and the logo looked quite like the letter M already, I repurposed it to become the logo I use today.

In the way of cleaning up MSheet and making things pretty, I also added a bunch of new icons and used champion splash art on both the Champion Dictionary and the Champion Challenge Tracker to be able to show more information, in a cleaner manner, in the Mastery Hub. As a result of this, I also added a "Fill" role so the number of roles can match the number of classes, making it easy to switch between them in the Mastery Hub.

The final change I made when converting MSheet from Version 1 to Version 2 was incorporating parts of the new mastery formula into MSheet--users can now enter their win rate and average grade to determine how much mastery they gain in a game, as guided by my earlier-mentioned empirically derived formula. The Starter / Guide sheet looks much prettier because of both this and the Targon-based theming.

Acknowledgements

As mentioned earlier, MSheet is a digital needle and thread for me--something that I can pour time into to knit something that I can not only use to track my progress in League's Mastery and Challenge systems, but also a product that tracks my growth in using Excel (or Google Sheets), is used by other people, and something I can step back from and be proud of. I love working in this silly workbook and hope that those of you coming across this post or the workbook itself can find use in it (or just enjoyed the read!)

I want to take the end of the post to acknowledge those who have helped me in constructing MSheet thus far: Darkintaqt, Wayne, and everyone in the challenges and LoL Developer communities that has given me feedback and suggestions.