Experience Points for Student Goal Setting

A couple of days ago, I posted about using forms and sheets for student reflections. As I was pushing it out to students, I started thinking about how to make it easy for me to assign XP and leveled badges for its use. Ideally, I would have some sort of automation that:

1. Allowed me to see students' latest goal without having to open each individual spreadsheet, and
2. Assigned XP and badges automatically.

Students latest goals:

I created a new spreadsheet, copy pasted my roster in column A, and used the importrange formula to bring the timestamp and goal from each individual grade reflection Goal sheet. For example:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/180ZOz9vkPq20ZDKy3KJUN--gVpWP6TABFKzk-hSxW5Q/edit#gid=168726689", "Goals!$A$3:$B$3")

Where:

•  https://docs.google.com/spreadsheets/d/180ZOz9vkPq20ZDKy3KJUN--gVpWP6TABFKzk-hSxW5Q/edit#gid=168726689" is the URL of the individual grade sheet
• Goals is the name of the sheet I am bringing over
• $A$3:$B$3 is the cell reference for time stamp and goal

Painstaking in that you do have to change each cell reference, but you only have to do this during set up. It is worth noting that when you do this the first time, you will probably get an error telling you that you need to grant permission. Just click on the cell and allow access. Also note that the sorting by timestamp was already done in their individual sheets as explained under Form Responses 2 in my previous post.

Automatically assign XP for goals

This portion of the post assumes that you are already using spreadsheets to house your leaderboard, and works with any version you may have created. If you are looking for one, I offer mine, Mr. Matera's or Mr. Powley's.

As we all know, the faster XP are show/given out, the better. This posed a problem for the assigning of XP for this activity since nothing would replace the student/teacher conferencing about their goals or the student actually reflecting on them. However, I figured that if I could at least give out points and badges for some measure of completion, I could always go back and make time for those conversations. As I toyed with how to do this, I thought about making sure that some specific words were included, but quickly gave up on that simply because my list of possibilities grew rather quickly. I settled instead for a simple word count and figured that 30 words at a minimum per goal are sufficient. Letting Google spreadsheets do the work for me, I added a hidden spreadsheet to the grade reflection spreadsheet template I shared before. When you make your own copy of that document, simply unhide it

As displayed when unhidden, this sheet is simply the place where the counting and calculation of XP happens and can be modified to suit your needs.

With that in place, it is back to the leaderboard page, where you would need to decide where you would like to maintain the information and when to assign badges. 

The formula used to bring the XP from the student grade reflection sheet to the corresponding cell in your leaderboard is:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/180ZOz9vkPq20ZDKy3KJUN--gVpWP6TABFKzk-hSxW5Q/edit#gid=168726689", "Wordcount!C2")

As before, each spreadsheet reference is unique to the student grade reflection sheet, so it needs to be manually changed and the spreadsheets need to be linked.

Finally, the awarding of badges is done using the techniques described in my Leaderboard and Badges post, using a conditional formula that awards badges according to the total points accumulated from the word count.

And the badges are displayed on the individual student sheets which feed from the leaderboard as explained in this post.

In an ideal world, the badges would only be assigned if the student actually met the goal they set for themselves, but that is a conundrum that I have yet to solve. Perhaps you have some ideas?