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


I’m Gamifying Learning Goals with help from the MTBoS

On Thursday night I was up until about 11 pm – far past my normal weeknight bedtime – working on finishing some grading as midquarter grades were being posted the next day. As I worked my way through 40+ copies of a Big Unit Test, I realized that I was being surprised by them more often than I’d like.

I’ve never mastered formative assessment. I have a hard time putting emphasis on it and time into it for the same reason that my students do – I’m a procrastinator and heart and work better with deadline and attached value. So we worked through a right triangle trig and area test in my geometry class, and some students never really got it, and I didn’t know that. Then there were some students who just screwed up on test day – as one student told me later, her test grade was collateral damage to a lab report. And there were just as many positive surprises – which is nice, but still tells me I didn’t know what I was doing.

I wrote this tweet:

And then, after finishing grading and writing necessary comments, I stayed up a little later, in a tired-but-annoyed fugue state. I stumbled upon this tweet by Kyle Pearce:

Go ahead and read his post. I’ll wait.

I followed the link, read the post, and realized that I needed to try it out. Immediately. And I couldn’t wait. I decided that I would try it starting the very next day, with the unit I had already been doing for two days with my geometry classes: circles.

The next morning, I had 80 minutes to prepare for my first geometry class. I was able to get their names entered on Kyle’s spreadsheet, create a sample web page to show them, get some preliminary standards written up, and make assessments for my first three standards – naming parts of a circle, sketching parts of a circle, and moving between area, circumference, and diameter of a circle. You can see the assessments I made here: Circle Standard Assessments .  The assessments are not particularly clever or good – I made them fast – but it’s a start. I ended up doing standards 1 and 2 at the end of class with them, and assigned standard 3 as homework – they can either do it for practice and attempt it again later OR pledge not to use notes/books/others and do it for Mastery (we have an honor code that makes it reasonable for me to offer this option).

I’m really excited about this. I think it is going to be awesome. My students were excited as well.

If you want details on implementation, see this post on how exactly to use the spreadsheet to implement this system, with some modifications I added.