One of my students’ favorite mechanics is their personalized grade sheet which now, thanks to Alicia Woody (@SuperEvansMath), I will be calling a student Dashboard. On this Dashboard students can see their current XP count, the assignments that they have earned XP for, their rank, their hearts/charges, current ranking codes, and more! Since their XP is tied to their grades this dashboard becomes a place where they and their parents can check in to see how they are doing in the class. The Dashboard is based in Google Sheets.

Here is what it looks like:

Every student gets one of these Dashboard sheets but it all begins with my Gradebook which also utilizes Google Sheets. Just for full disclosure my gradebook is based on an Alice Keeler beta that she let me use 5 or 6 years ago. I have tweaked some elements of the Gradebook Sheet but the core of it is still her work. The Dashboards and the connections though are all mine.

Here is what the Gradebook looks like.

The gradebook has a bunch of cool features which I won’t get into here. This is the main page where I enter the grades and can leave notes or feedback tied to the grades if I wanted to do so. This is also the page that I will link to the students’ Dashboards using the =importrange function. In order to complete the link we will need the section of the URL address that I have highlighted in the image above.

Here is what it looks like in the Dashboard.

If you can’t read the picture here is the function that appears in cell A1:
=transpose(importrange(“1ibYiEAvMG96Xdm7pv1civ5yIXiBhEYz-_KSpWbJ4D_0″,”grade records!A5:CZ5”)). This is actually a mashup of two functions; transpose and importantrange. the importrange function will import and display the information from the tab “grade records” in cells A5:CZ5 on the Google Sheet 1ibYiEAvMG96Xdm7pv1civ5yIXiBhEYz-_KSpWbJ4D_0. By putting transpose in front of the function it will take the horizontal data set and will display it vertically.

If you would like to look at all of the various functions in the Dashboard you can click here to examine it. There are a lot of different functions that I have been playing with but don’t want to drag this post down by describing. Have fun checking it out.

Now to create the Dashboards for each student I start by creating a template that has all of the bits and pieces worked out. I have to tweak the Dashboard template for each class because it has a different gradebook URL string that will need to be included; however, once the class Dashboard has been created I can simply make a copy of the template and name it the first roster student’s name. In the above example Bill James would have been the first student on my roster. Take a look at these screen caps – In the gradebook Bill James is cells A5:CZ5 and these are the cells referenced in cell A1 of the Dashboard Transpose/Importrange.

After the first student Dashboard is created I will have the template plus the first student’s Dashboard. To share that dashboard with the student I can click the blue share button in the top right and just email the book to the student.

CAUTION – be sure to change the share settings to view only because it defaults and edit.

The next step is to make another copy of this Dashboard. Name the copy the next student’s name. Go the the importrange function in the new Dashboard and change the cells from A5:CZ5 to A6:CZ6 and MAGIC you will now pull the next students information!

CAUTION 2 – Be sure you have the template worked out before making the copies! If there is a problem you will have to change each of the copies individually!

This will take a bit of time but I did a class of 100 students in about 45 minutes and I only need to do this once per semester. I am sure that someone like Keeler would be able to do this in a much more elegantly than this but this gets the job done and it does it in a way that I and my students like.

Please let me know if you have any questions or suggestions!