A Google Sheets based standards gamification system

As described in a previous post, I recently decided to try a mastery-based grading system with gamification for my geometry classes, inspired by some amazing work from Kyle Pearce and John Orr. Implementation of the system is key, and they had together, with the help of Alice Keeler, created one of the most amazing pieces of Google Sheet work I have ever seen to help with this; you can see his post explaining it here.

Here is the highlight version:

The spreadsheet has one tab, called “Master,” which controls most of the system. On this tab you define your standards or learning goals for the unit/course, put your roster, and assess each standard. Students can earn from 0-4 stars on each standard (though you enter it as a standard 1-100 grade). You can also award Mastery Badges to students when they have, in your estimation, mastered a goal. You can also add feedback, links to assessments or resources, and notes either to the class, a student, or to a student in response to a particular standard.

All of this is automatically imported by the students’ own personal tab. They can see their grade on every standard, which ones they have mastered, how many they’ve mastered, and what level this makes them. Their goal is to level up to the highest possible polygon! This can then be published to the web so they can see it as their own personal Mastery Portal, with links, feedback, and so forth that (should) automatically update. It looks like this:

All of this was awesome, but I immediately saw some things I could do to improve the sheet; my summer of working as a spreadsheet programming professional really came in handy here!

I made three major changes to Kyle’s sheet:

1. I sped it up. The original student sheets relied extensively on HLookup and VLookup calls, which are amazing spreadsheet functions that also tend to be rather slow when used a lot. I was able to use some different commands (Index and Match) to speed up the calculation of student spreadsheets by limiting the number of times one sheet looks up data in another.
2. I added some automation. Specifically, I added scripts to automatically create the student tabs from the roster, automatically get their URLs so you don’t have to copy and paste links one-by-one from a menu, and delete all of the student tabs if you need to start over. I also added a script to force the student tabs to update if for some reason they don’t change when you enter a score. Thanks to Alice Keeler for her TemplateTab script from which I started and got inspiration.
3. I added a little bit of customization that was not in the original (though not as much as I’d like to add eventually)
4. I added a tab with directions, so there’s no need to reference a blog post to remember how to work it. =)

I’m very excited to use this spreadsheet for this unit. Thanks so much to John Orr and Kyle Pearce and all of their inspirations for the brilliant idea and work – I think this could be a real game changer.

Click Here to get your own copy of the Gamified Standards sheet