Tuesday, June 12, 2018

Individual Rank Sheets - Google Sheets to the Rescue



Over the last couple of years, as my game has grown, so has my need to add elements that improve the sharing of ranks, perks, and standings with my students. Up until now, I have published our leaderboard two ways:

  • a "combined" leaderboard that displays the Top 15 students among all 170 of them.
  • each grade level gets their own, which ranks all 34 students for that particular class.
I also publish a Badge Sheet for each class, where students can go and see which of the badges they have earned. All of this already populates automatically from the "master leaderboard" I shared a while back (Leaderboard and Badging with Google Sheets).

This works well to inform the students of their standings. However, it requires that students navigate between several web pages and do some scrolling, often complaining about not "finding themselves" within the data. I also often worry about the students who are at the bottom of the leaderboard since these placements within the ranks are public to all my students. As I pondered these problems, I also thought about a way to give students some control about what information is shared with all and what is "just for each one".

In searching for an answer, I came across two blog posts that should be mentioned as the inspiration for the sheets I came up with, @MrMatera's "Standards Based Grading Gamified and Googled" and @MrPowley's "XP Grading: Video Blog". As always, these two masters of the gamified classroom had already come up with solutions that better informed the students of their progress - The Individual XP Sheet. With a name in hand, and knowing that it was possible to share individually with students, I set about creating one that made sense for my classroom, using a combination of IMPORTRANGE and conditional formatting so that everything would auto-populate without me having to open up a whole bunch of individual sheets. Remember I have 170 individual sheets to maintain.


Just like in the VLookup post I published a few days ago, I would love to simply give you a template to use tomorrow. Unfortunately, even if you use what I share in the set-up procedure that follows, the references for the cells will need to change as soon as you create a copy.

Before I go into explaining the set-up and scare you (it is time-consuming, but you only have to do it once!), let me show you the magic. In the following mini-video you see my leaderboard, my "Teacher Master Sheet" spreadsheet, and a Student Individual XP Sheet



Now that you've seen it in action, let me explain what to do to set this up. It all starts with the Leaderboard, where I need to be very consistent in the placement of the information, especially student names and Emails so that things do not become complicated down the line. In order to accomplish this, the first three columns on the Leaderboard sheet are the only ones where I actually type names and e-mails. The first three columns (A:C) in every other sheet on the leaderboard, where I will type in or import scores, get:

=arrayformula(index(Leaderboard!A3:C36))

With that in place, it is time to create the Teacher Master  Sheet template. I created mine using Alice Keeler's TemplateTab so I would not have to duplicate each student tab myself. If you open the Teacher Master  Sheet template, you will notice that it includes 4 tabs instead of Alice's 2. The Heraldry and Standing tabs, which are imported from the Leaderboard using TRANSPOSE(IMPORTRANGE) and IMPORTRANGE respectively, were necessary for my purposes since I wanted to display elements of both but limit the delay it could cause as I was importing the other elements.


The mini-leaderboard is set to always display the owner student in the middle and two students above and below. It combines some manipulation of conditional formatting and If- Match statements in order to not get errors when the student is at the top or bottom of the class leaderboard. This is why I had to add the Standing sheet.

Once my template was done, I used IMPORTRANGE to add the names to the roster and ran Alice's TemplateTab script. Since I ran the script after I had added the Heraldry and Standing sheets I ended up with a couple of sheets that were mislabeled. If this happens to you, simply discard them and/or duplicate and rename what you need. You will only run the script once for each Teacher Master sheet.

Then comes the onerous task of referencing each of the correct cells from the Leaderboard, as C3 will need to become C4 and D3 will become D4, etc. I wish there was some way to automate this, but I have yet to find it. However, it more painstaking than anything else, and just like the script, I only need to do this for the set-up, and never again. I opened the leaderboard, and for each name, I found the row where it was located and changed the numbers accordingly.


Once I had finished all of them and had the Teacher Master (sharing one where all the numbers have been changed for 34 students, though if you use it you would need to change the leaderboard reference), it is time to create the individual sheets. One would think that you can just open up a spreadsheet and copy/paste, but that does not preserve the format that I painstakingly created, plus I want them to all update automatically, so instead there are a couple more steps.

To preserve the formatting, I first created a new spreadsheet (I called it Student Template). Then I chose one of the student sheets in the Teacher Master. I clicked on the arrow next to the name and chose duplicate sheet. It asked where you want it, and of course, I selected Student Template. Once it is duplicated, I went to the Student Template and saw it had added a tab called Copy of "Name". I deleted Sheet 1, and instead of selecting and deleting the cells in Copy of "Name", I selected and cleared the cells. This preserves all formatting, including all conditional formatting.


Of course, since the referenced sheets were no longer there, it looked like there was an error, but I knew what the final step was...

I made a copy of the Student Template, renaming it with the correct name for my first student (Name 1 in the example), and went back to my trusty =IMPORTRANGE formula.

=IMPORTRANGE("URL of TeacherMaster","'Name 1'!A1:M14")

Notice that Name 1 includes single quotes, this is because the sheet name has a space between name and 1. This is not needed if there are no spaces in the Sheet Name.

After doing this final step 34 more times, I had my first complete set of Individual Student Sheets.


I use the same basic sheets for all my 5 classes, but I do have five leaderboards (where the badges differ) and also like to keep the Teacher Masters separate. This means that I had to change the leaderboard URL to create each of the Teacher Masters before running the Alice's TemplateTab script.

The last step is to share each Individual Student Sheet with the student "owner", which I accomplish the regular way (share, type e-mail, can view). 


Those individual links can also be added to a spreadsheet or blog post where names are sorted alphabetically and since only the student "owner" and myself have access, the risk of oversharing is minimized. Now, for those students that perhaps do want to share their badges or rank insignia, there is always the possibility of creating mini-sheets within their individual sheet. Using =arrayformula(index('Name of student sheet'!C2:E6)) you can select specific cells to share. If you publish just that mini-sheet to the web, students can then get the embed code and publish in their blogs or e-portfolios.