Assign XP automatically using Vlookup - Google Sheets

My last couple of posts ended with a question, "How can I give the XP generated by sidequests or Boss Battles automatically to students?" This is a key question in both instances because:

• In true gamification style, it is imperative that students' can instantly see their progress in ranks. The immediacy of the auto-updating of progress serves as a motivator, and there is nothing worse for my students to have to wait until I manually input the data.
• I really do not want to have to input values manually, it is time-consuming and prone to error, especially when you have multiple submissions by the same student.

Much like I ask my students to do when attempting to solve a problem, I first asked what is it exactly that I need. So in simplest terms, "I needed a way to have sheets look-up an e-mail (name) in one workbook (Boss Battle or side quest results) and input the value that accompanies it into another workbook (leaderboard). I set about finding the answer and after several attempts, I found the answer is a combination of Vlookup/import range combination. 

As much as I would love to provide you with a template that you can simply copy and start using, it is not as easy since the workbook and cell references need to be changed in order for this to work. What I can do is provide you with a skeleton and an explanation of what needs to be done. 

1. Have your leaderboard set up with the e-mail addresses of your students - e-mail is necessary since the other sheets will match the e-mails auto-collected in the corresponding forms. Here is the one I used to set this up. For a full explanation of how just the leaderboard works you may wish to visit "Leaderboard and Badging with Google Sheets".

2. Have your boss battle sheet(s), each set up with a pivot table that sorts the data by e-mail and "sum of score". I am sharing a blank one, just remember that this one is tied to a specific form. To recreate a boss battle sheet with your own question set, look at my post Boss Battles with Google Forms/Sheets. 

You can use the process I am describing with any form responses/quiz you create on Google sheets. What needs to be done is to have a form that collects e-mails automatically. Once you have your form responses sheet, add a pivot table. I remane mine "scores".

3. It is now time to connect both sheets. On your destination sheet, in this case the Mastery Quest sheet in my Leaderboard, add the following formula to the first cell where you would like your imported scores to appear.

=IFERROR(VLOOKUP(A2,IMPORTRANGE("1O5AhXP4qJhbcNbjzDOI7-trYDjSDBQtN4iA2MIDzfeo","scores!A3:B"),2,0), 0)

The big string (1O5AhXP4qJhbcNbjzDOI7-trYDjSDBQtN4iA2MIDzfeo), which must remain in quotations, is the URL for the sheet the scores are coming from.

Important to note:

• Depending on the sheets version you are using, you may first need to allow both sheets to connect. If you paste the formula and it does not seem to work, add =IMPORTRANGE("long sheet identifier","scores!A3:B"),  anywhere on the destination sheet. A little box will appear asking if you want to connect the sheets. Once they are connected, delete the formula. This is just to give it access.
• If you rename your Pivot Table anything other than scores at any time, you must manually change it in the formula.
• The 2 that shows after the parenthesis in your formula identifies the column where the data you want to bring is found. If you add any other values to your Pivot Table, or you rearrange them in any way, you must change this number to whatever number column your data is in.

4. Finally, it is just a matter of copying the formula down the column in your target sheet (in this case the leaderboard sheet). In order to accomplish this quickly, and to save you from the carpal tunnel syndrome that would inevitably arise from all that Ctrl+C/Ctrl+V, simply position yourself on the cell you want to copy down, and drag the little blue box that appears, down.

Once you have copied down the formula, it is a good idea to double check that the references changed correctly. 

When you are setting this up, all the values will be zero. The same is true if there is no Email match between the quiz/form and the destination sheet/leaderboard. This is helpful if you are running a boss battle or if you have absent students, as you can quickly see who has not done the work at all. However, once your students have submitted their quiz, your leaderboard scores will be auto-updated to reflect this "change".

 This same process would need to be repeated for each quiz/form you want to Vlookup, but really once you have done it a couple of times you will find that it is not as cumbersome as it seems. It simply boils down to creating a pivot table to aggregate your data, copying the Vlookup formula and changing the reference to the corresponding sheet. You can also modify it to assign XP only for max score changing that final column reference in the formula, or if you add another value column you could even use averages. Whatever makes the most sense for you and your students.

Also, it is important to note that you do not have to have a pivot table other than to summarize your data initially. For example, I use Alice Keeler's Rubrictab to grade my students' work. Using the same process described above, with the corresponding modifications to the references in the formula, I can have the roster sheet I create from her template each time I grade linked to my leaderboard.

=IFERROR(VLOOKUP(A2,IMPORTRANGE("1VVD83QukeZ6P3pMmpoJnxEt9cgg8tv2LnnirkM71Vso","roster!B2:E"),4,0), 0)

where

"1VVD83QukeZ6P3pMmpoJnxEt9cgg8tv2LnnirkM71Vso" is the workbook identifier

"roster!B2:E" is the sheet name and cell references (Email Address through Score)

4 is the number of the column where the score is found, starting the count from column B

For more ideas on the use of Vlookup, you may want to read Mr. Powley's post "The Magic of VLOOKUP: G.Sheets, Boss Fights, and Badges".

As always, if anything seems confusing or you have questions, leave a comment or drop me a Twitter question @MarianaGSerrato.

Grades in the Gamified Classroom

https://twitter.com/mr_isaacs/status/885658805002018816

In a recent #games4ed conversation we were talking about gamified grading, and the two tweets above came up. This resonated with me for several reasons:

1. As I am sure it is true in most cases, at the end of the day (term, school year), I have to submit regular letter grades. 

2. I have been guilty of falling for the above-mentioned pointification, or simple substitution of traditional grading for an XP-like system and leaderboard. While the change did infuse some excitement in my classroom, the students quickly discovered that it was simply "a rose by another name" and rebelled accordingly - Gamification - Don't Fake It

Simply changing the grades to points does not change the student's mindset. At its core, gamified grading can be a visual representation of competency-based grading, which as Matt Townsley reminds us is different than standards-based grading.

"Competency Based Education is a system in which students move from one level of learning to the next based on their understanding of pre-determined competencies without regard to seat time, days, or hours.

In a competency-based system…

• Students advance to higher-level work and can earn credit at their own pace. (In a building, district, or classroom using a standards-based grading philosophy, this is not necessarily the case. Students are likely required to complete x number of hours of seat time in order to earn credit for the course.)
• Learning expands beyond the classroom. This may or may not take place in a standards-based grading philosophy. For example, in a competency-based system, a student who learns a lot about woodworking over the summer may earn credit when he or she returns to school the next year. Similarly, students are encouraged to learn outside the classroom so that they can demonstrate competencies at their own, rapid rate.
• Teachers assess skills or concepts in multiple contexts and multiple ways. (This may or may not be the case in a standards-based grading classroom; however, it is non-negotiable in competency-based education.)"

How does this translate to a gamified environment?

1. Explicit criteria and targets are made available to students ahead of time. The students in a gamified classroom know exactly what it takes to "defeat the boss" (AKA demonstrate mastery). Basically, everything is assessed using rubrics. The rubrics are created using objective measures, provide actionable feedback and are presented in kid-friendly language. 

2. There are multiple opportunities to gain XP (practice the standards). Every piece of work submitted results in XP, even incomplete or half-correct work! Let's think about this from the gamer standpoint. A player going through the third level of Mario falls and must restart the level. The XP he gained does not go away, and he/she will try the level again using what he/she learned from the previous attempt in order to pass the checkpoint and proceed to the next level. This cycle continues throughout the game. This is also true in the gamified classroom. That half-correct work is re-done based on the feedback, resubmitted for a new assessment and opportunity to gain more XP.

3. Some may demonstrate mastery on their first attempt earning a set amount of XP and perhaps a badge that shows the students (and community) that mastery of the standard has been achieved. This does not mean that the "learning is done". The student can then go on a side quest to earn even more XP and continue to level up within that standard. While this is happening other students are re-working/adding to their work and even going through the same side quest that the "masters" are completing, practicing the standard until they have collected enough evidence of mastery and the total amount of XP available for the standard is achieved.

All of this made public to my students on the leaderboard. But what happens at the end of the week, when I am contractually obligated to publish at least one new grade in the grade book? For that, I choose the most recent evidence of learning. This means that what goes in is not the same piece for all, but rather the individual piece of work that illustrates the learning from that particular student for that week. So, for example, the student that demonstrated mastery on week one and decided to sit back and relax on week two may, in fact, receive a failing grade for the week (no evidence of learning), while the student that has not shown mastery could be receiving an A. 

I know that this system is not perfect, and there is a possibility of inflated grades. Have you found a different solution? I would love to hear your thoughts.