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.

We don't need badges, or do we?

When I started my gamification journey, one of the sore spots, if you will, was the awarding of badges. On the one hand, I agree with the ideas in Daniel Pink's Drive and the overjustification effect, and which translates into "Badges ruining intrinsic motivation to learn". On the other hand, I have a gamer son, who keeps going back to specific games in his collection simply because he wants to get that elusive gold badge just so he can show it off in his profile. So which is it?

As I continued to ponder the answer to this question, I participated in a couple of workshops and PD that offered badges that could be "added to my profile". I was surprised by my own drive to complete the activities, not really for the sake of learning, but because I wanted to show off the badges I had. Badges were giving me a sense of accomplishment and encouraged me to persevere, even when I got bored! If badges were doing this for me, why was I being so reluctant to add them to my classroom?

I began exploring different ways that other educators have used badges and came up with two reasons that if addressed would make me re-think the whole badging issue.

First, if I were to include badges, I had to make sure that the badges represented real achievement. One of my concerns with badging is that it can simply become a sticker chart. The idea that everyone gets a badge simply for showing up or participating in an activity takes away the value of the badges. Let's face it if the students know that they will get a badge simply for filling in boxes in an assignment, will they put any effort into making sure that their answers are correct? Probably not. However, if they know that they will only get the badge if they get a high enough score, then they may feel that the badge has some value attached to it. Even better, let's say that they did not get a high score in the badge assignment(s) the first time around. Will knowing that they can re-work the assignment giving them more than one opportunity to earn the badge, motivate them to keep at it, even if they think it is boring or not worth their time? I think it will.

Now, if this is the case, then I knew that I needed to be able to make my own badges. There are several online tools that allow us to do that. Classbadges, Credly and OpenBadges come to mind. However, I prefer to fully create my own simply using GoogleDraw and clearly explained here by Alice Keeler.

Second, the whole idea of badges for me is that they must be public. Students will want to know, not only if they have the badge, but also who else in the classroom has it. That gamer sense of competition and being able to showcase achievement adds value to the badge. The tools mentioned before for creating the badges, also allow students to log in and view their badges. Now this would require students to create an account and/or log in with accounts that the teacher creates. That, for me, was a no go, and although I toyed with the idea of creating my own system using GoogleSheets, I decided against it (simply due to time constraints), when I came across Flippity.net's Badge Tracker. With a few tweaks, this tool allowed me to import the data of my existing leaderboard, use my own badges and embed it in my webpage for public viewing. All around win!

For now, I have decided on three types of badges:

Leveled Badges: These symbolize achievement in on-going assignments. Students will earn these badges by scoring well on their weekly writings and reading assignments.

Project Badges: These badges represent the skills and knowledge gained in a specific unit of study within the scope of science and engineering curriculum. 

Commitment Badges: These are badges awarded for achievement outside the leveled and project badges. What I envision here is, for example, a "Digital Citizenship" badge or a "Creative Commons" badge. 

So, what do you think about adding badges to your gamified classroom? I would love to hear your ideas.