Leaderboard and Badging with Google Sheets

Many of us in the gamified education game have toyed with different leaderboards and badging systems over the years. I've often dreamed of having a system that does the tracking of XP, badges, and items automatically as I input scores, without having to navigate between spreadsheets copy/pasting data from one to the other. This dream also includes the ability to change ranking and badging systems relatively quickly so that if my students become bored with something I do not have to start from scratch each time. As I've kept toying with this idea, I've been discovering some new tricks on spreadsheets that have allowed me to come up with a template of sorts.

I invite you to make your own copy of the template before I explain what is happening in each sheet. Doing so will allow you to follow along and make changes so that by the end of this post you have your own working copy.

Leaderboard and Badging Template

Leaderboard Sheet

Displays all names, ranks, total XP and badges earned by the students. The names and last names are carried over to all other sheets, so any changes you make there will be present in all other sheets. Inversely, it populates the data and images from the other sheets, so changes made in other sheets will display on the Leaderboard sheet without you needing to make any adjustments to it. This is the only sheet I share with the students.

Ranking Sheet

This is a pivot table created from the data on the Leaderboard sheet. If you are using the template, you do not need to do anything to it, but I am sharing a video that explains how to create one in case you are interested.

Heraldry Sheet

In this sheet, I include the images and points needed for each of the 11 ranks I have in my game this year. If you want to change the image to something else, you will need to have the URL for the image you want to display. Simply substitute that URL within the =image("URL goes here, inside the quotations") formulas found in cells A2-A12. Changing those URLs will automatically change the images on the leaderboard page. Same goes for changes in the name and the min and max XP for each rank, allowing you to quickly change the theme of your ranks as well as make adjustments to your ranking when you find that the range of points for a rank is too wide or too narrow.

Badges Sheet

Much like the heraldry sheet, this sheet contains the images and descriptors for each of the badges I have for this class. Any changes in name or image for the badge are reflected on the Leaderboard sheet. Just like in the Heraldry sheet, to substitute the badge image change the URL of the image to one of your liking within the quotations of the =image("URL") function. All of my badges were created using Google Draw as explained by Alice Keeler in this blog post.

XP Sheets - (Blogs, PBL quests, Mastery Quests, Repeat Assignment)

These are the sheets where I input the XP. Although I could have done this on just one sheet, I prefer to have the different sheets in order to organize the data. The names in all of them are populated from the leaderboard, and the total values calculated in each sheet, populated back into the hidden columns (F-J) on the Leaderboard and added into column E. Changing the names of the sheets will not affect the Leaderboard calculations nor the ranking sheet. The "magic happens" on the cells with a grey background so those are the ones that should not be touched directly.

Badge Tally Sheet

This is the sheet where the badges are "awarded".  You can award the badges "manually" by entering the scores directly, or you can pull those scores from the sheets and manipulating them using the different formulas you see on the sheet. The names for the badges come from the Badges sheet, making it easy to keep everything organized.

Right now, I am toying with adding an Items sheet that will work much like the Badges sheet, and I will share that with you as soon as I am done. If there is anything that does not work, or you find confusing drop me a note in the comment section. I'll gladly help you figure it out.