Boss Battles with Google Forms and Sheets - An Update

I spent most of the last summer creating Boss Battles for my gamified classroom. Throughout the school year, I also shared with you a couple of tweaks I made to those original sheets, adding "first hit and first perfect hit", and then adding "shiny bosses".  My students really got into this whole idea of battling the bosses and I was really happy with the excitement they generated.

A couple of days ago, I began the painstaking process of clearing all of this work out in preparation for the new school year. While I would have loved to use them as they are, they no longer fit the game narrative that I am exploring for the upcoming year, plus, I reused Bosses across my grade levels. While this may not be a problem for most teachers, it is for me because I loop the students, so most of them would be re-battling the same bosses they already know. Perhaps they would not remember, but all it would take is one of them to check their student sheets (where the bosses are displayed), and the excitement of getting collecting the boss would be diminished. 

With all of this in mind, I started creating new bosses - we will be rescuing scientists - and clearing out the old forms/sheets tweaking the originals a bit, which is what I am sharing today.

Boss Battle Form (will auto copy)  - I have dummy-filled it with some data so that it is easier to explain. If you make a copy simply delete all the prefilled contents of "form responses 1"

Boss Sheet

While it looks basically the same, there are important things to note. 

• Column L contains each of the Boss possibilities. Each is its own drawing (published to the web) and placed into the cell using 

=image("published address of your google drawing")

You may be tempted to make use of the new "insert image in cell" function. Don't. You will be calling the images into the boss battle pane and into the scores sheet. Google Sheets does not recognize images insterted that way as actual objects so until they fix that what will happen is that you get blank instead of the image you want to display.

•  Cell F15 now contains a new formula:

=index('Form Responses 1'!$B:$B, match(true,isnumber(search("/",'Form Responses 1'!$A:$A)),""))

This formula looks at the timestamp looking for the first appearance of the slash symbol (/) in Form Responses 1 column A and reports the  matching contents of column B in the same sheet. This formula solves the problem of having to change cell references as the sheet is populated the first time around.

• Cell F16 contains a similar formula:

=index('Form Responses 1'!$B:$B, match(true, 'Form Responses 1'!$C:$C=12, 0))

In this case, it matches the contents in column B with the first time the "perfect score" of 12 is achieved, and of course that can be changed to whatever number you is your "perfect score". 

Perfect Sheet

That sheet simply reports the contents of first hit and first perfect hit and assigns them a value. I bring that into my leaderboard using the Vlookup function as a way to give students an added incentive.
Formula that imports this into your leaderboard:

=IFERROR(VLOOKUP(A17,IMPORTRANGE("Boss Battle Google Sheet Identifier","Perfect!A:B"),2,0), 0)

Where the number 2 is the column number where those "extra points" are housed.
If you are interested in the Vlookup formula as it pertains to the leaderboard, visit my post "Assign XP automatically using Vlookup - Google Sheets"

Scores Sheet - AKA Where the magic happens

As originally, this is a pivot table that aggregates the data generated first by adding all the scores each student generates (Column B - Sum of Scores) and then by reporting the maximum score the student obtained (Column C - Max Score).

Column D (Counts the number of times a student achieved a Perfect Score)

I've been toying with the idea of having students "catch/rescue the Bosses". This collecting of the bosses had a great impact in engaging students to attempt the quizzes multiple times throughout a semester, regardless of whether it is an official Boss Battle day with the whole display of the Boss. In order to achieve this assigning of bosses I first needed to know how many times a student scored a perfect score. To do this, I added a Calculated Field to my Pivot Table:

If you click anywhere inside the already populated pivot table, the Pivot Table Editor opens. This already had the score summarization by SUM and by MAX.

The Times Perfect Score was added using by clicking Add values and selecting "calculated field". The formula is simply a COUNTIF, though you notice that the normal use of the quotations is eliminated.

=COUNTIF (Score, 12)

The number 12 obviously corresponds to the "perfect score" in this particular case and could be changed to whatever suits your needs.

If you are recreating your own, also make sure that you select Summarize by Custom and Show as Default.

That got me the number of times a student got a perfect score. You may be wondering why this was important. The answer is that I did not want to give out the Boss simply based on the number of points. My students are notorious for finding the easy way out and often would rather take a quiz 100 times scoring 1 each time than getting 100 the first time.

Column F (Where the Shiny Bosses come in)

With that number of "perfect scores" aggregated by student, it was time to move on to the "catching/rescuing" of the boss.

When working to pivot tables, you can still add formulas to the columns that are not being used by the pivot table itself. I added a title to Column F and called it boss obtained. To give the illusion of randomness to the catch, I decided on the following formula

=IF(D2>=10, Boss!$L$9, IF (AND(D2=9), Boss!$L$8, IF (AND(D2<=8,D2>=6),Boss!$L$9, IF (AND(D2<=5,D2>=1),Boss!$L$8," "))))

which gives the Boss in Cell L9 or L8 of the Boss sheet depending on the number of perfect scores. You would think that you could use the RAND (random) function, but unfortunately, it is what is called a volatile function, which means it refreshes each time and I did not want students to have a new boss catch simply by opening the sheet.

The formula is just copy/pasted down through the sheet so that each row gets its own student reference. The results of this column are again imported to my Leaderboard using VLOOKUP as mentioned in Perfect Sheet above. The key here is to remember that VLOOKUP references the column number:

=IFERROR(VLOOKUP(A2,IMPORTRANGE("your sheet identifier","scores!$A$2:F"),6,0), 0)

where the initial A2 - is what it is looking for (e-mail of my student),

"scores!$A$2:F"- the sheet and range where the data is housed

6 - really column F where the bosses caught were "assigned"

Column E - Maximum Possible Score: the end to "grinding"

Last year, some of my students greatly inflated their leaderboard standing by retaking boss battles over and over again. While in principle this would be OK with me, there is a point where enough is enough. I know that I can simply close the form, but that leaves the student who may need the extra practice out of the loop, and opening and closing a form manually means that I must remember to do so. Taking this into consideration, I opted for writing in a formula that reports a set number of points out to my leaderboard (again with the VLOOKUP formula) to my leaderboard once the maximum is reached.

=if (B2>40, 40, B2)

Looking at the formula, it simply reads if the contents of cell B2 (where the scores are added is greater than 40, report 40, otherwise report the number present in cell B2. The formula is copied down Column E.

That is it for today. I hope this inspires you to keep working on your leaderboards and perhaps make some additions or modifications. And as always a big shout-out to Mr. Powley who although has moved on to standards-based grading and may be letting go of the Dread Sheets inspired this whole Boss Battle process.

Reviewing Content with Shiny Bosses

By far, the greatest successful addition to my game this year was the Boss Battles. Piloted at the end of last year, and fully implemented this year, I've even had students present this form of testing during Student Exhibition Nights. This boggles my mind since the Boss Battles are in reality nothing more than the normal tests and quizzes I would normally have, but as we all know, beating the Boss, and even better, adding each to our collection of "perfect bosses", is so much better than simply taking a test.

My boss battles have undergone a couple of minor tweaks over the course of the year, with the addition of the first hit and first perfect hit, and Google Sheet's new (to me) shuffle question order, but other than that they have remained the same as when I first created them. 

With CASP dates approaching, I started to think about pulling questions from this already created "bank" of questions to create new battles for review. However, I did not love this idea simply because, I did not think this far along in the school year when I set up the individual student sheets, and thus did not leave myself extra places for more than a couple extra bosses. The other issue I had was that each unit is tied to a race of aliens we encounter through our narrative so it would not make much sense for a "robot" boss (matter) to suddenly ask questions about earth systems, for example. 

I was pondering this one morning while on my daily Pokemon Go walk (yes, I still play) when the idea to make shiny bosses came. If you are unfamiliar with the term, a shiny Pokemon is a variant of a regular Pokemon that has a slightly different coloration. It does not give anything new out nor does it have any special attacks, it is simply different. So I set about trying to figure out how to make that happen without having to recreate (copy) a multitude of forms and sheets and do a bunch of work with changing cell references in my evergrowing leaderboard. Most importantly, I did not want to touch the student ranking sheets. So here goes - Fullscreen link

If you are presenting the shiny boss battles as a stand-alone thing for review, you are done, but if like me, you were already pulling data into your leaderboard, you will need to make some other adjustments. 

 As with most of the gamification set-up tasks, the joy for you may be in creating the first shiny boss, and the rest of the process may become tedious, but remember, once done you do not have to do anything other than enjoy your students as they confront the shiny boss. As you see below, the figuring things out ends up being done by Google Sheets so there is no time wasted in looking up individual students to see if they got the shiny boss or not.

What do you think? Any other ideas to make review a part of your game or narrative? I'd love to hear about them.

First Perfect Hit - The Match Function

A couple of months ago I shared with you how to create Boss Battles using Google Forms and Sheets and assign XP automatically using the Vlookup function. I spent most of my summer creating all the Boss Battles I intended to use this school year and tweaking my individual student sheets so that if a student scored a "perfect" hit on the boss they would capture it and add it to their collection.

The response of my students to this whole idea has been very encouraging. They actually look forward to Boss Battle days, literally rushing to class to participate in what you and I would actually call a testing day. However, there were a couple of little nagging issues that my more hardcore gamers immediately asked for: Extra XP for "first hit" and/or for "first perfect hit". Apparently, this is a "thing", and while they were satisfied with my answer at the time "Don't know how to do that other than scouring the sheet, which I do not want to do. If you figure it out, we can implement it", it stayed in the back of my head. 

A couple of days ago, I once again sat down to try to figure it out, and it finally crystallized.

On one of my existing Boss Battle Sheets I added:

- First hit: Easy, since you only need to bring up the first entry on the form

='Form Responses 1'!$B$2

- First perfect hit: This formula looks up the e-mail of the student Form Responses sheet (column B) and returns the value found in column C when it equals 10 (the perfect score for this Boss battle). Once it finds the first one, it stops.

=index('Form Responses 1'!B:B, match(true, 'Form Responses 1'!C:C=10, 0))  

These two formulas made the students happy since at least they now had the "bragging rights". The addition of extra XP for those two instances can be done manually, or by adding a "Perfect" sheet to your BossBattle (as in the example), where you again report those two values and input whatever XP you wish for them. You can then use the Vlookup function explained in my previous post to have Google sheets find the value for you. That formula in your leaderboard would look something like:

=IFERROR(VLOOKUP(A2,IMPORTRANGE("GoogleSheetID","Perfect!A:B"),2,0), 0)

I now have to add those formulas to all my Boss Battles, but I know that it will be a nice surprise when we come back from break. What about you, any other tweaks to share?

Boss Battles with Google Forms/Sheets

For a long time, I've been thinking about how to do more than just award experience points (XP) to my students who complete a mastery quest (quiz/test). I've been toying with the idea of making them a bit more interactive and wanted a way to show students how the Boss would lose hit points (HP) as they answered.

Adam Powley (@MrPowley) wrote a piece on Dreadsheets a few months ago, detailing his system, using a very elegant mix of Google sheets, group roles, and dice, kindly sharing all that is needed to implement his system. Make sure to read his post, as it is truly masterful.

His system works really well when you have all those elements in place, but I sadly am not there yet. However, this did not deter me from continuing my pursuit, so borrowing some of his ideas, but trimming it down to the game elements that I do have, I came up with a simpler version that works for me. Before I get down to the nitty-gritty, let me show you how it works.




These boss battles use the simple quiz version of Google Forms and its corresponding Form Responses spreadsheet. You can obtain the folder where I housed both here, though you may just need to make a copy of the Boss Battle Share (Responses).
Also, although I have deleted the data and created several copies, you may find that if you use a copy directly it still carries over some of the "extras". If this is the case, simply create your own Quiz as you would normally do, and once you have the corresponding Form Responses spreadsheet, add a Boss Battle sheet where you copy/paste everything that is included in cells A1:L16. If you do this, you will also need to create a new tachometer, which is a simple gauge chart; if you do not know how to do this, here are some instructions (Stop at slide 8 since you will not need to embed it anywhere else)

Elements of the Boss Battle Sheet (although embedded below, you may want to open the link if it is not quite as clear as you wish it to be.)

Finally, I set the quiz to automatically collect email adresses, release grade immediately after each submission, respondents can see missed questions, and, depending on what I want, either limit to one response or not. 

With all of this in place, it is just a matter of sharing your quiz with students, and displaying the Boss Battle sheet to the class using your projector and screen. As students complete the quiz and submit their responses, they can see the "damage" they inflicted on the boss, until eventually, they defeat it.

I usually provide individual XP after a boss battle like this one to encourage students to actively participate. The XP for each student can easily be obtained from the Form Responses sheet. Also, once the boss has been beaten, you can use the information from that same sheet to determine which questions were missed most often and/or sort it to find out how many responses each student submitted as well as their specific answers. 

Now, I do realize that one student can answer 50 times (simply memorizing answers), while another student can decide to not even try once. This can be somewhat deterred by limiting the number of responses so that the reluctant student has to answer in order for the whole class to beat the boss. You can also make use of the shuffle answers and the shuffle questions options on the form. This at least "forces" the students to re-read the questions and not just memorize answer placement. You could also make the quiz much longer (this 10 question quiz is just an example, with questions that are not particularly insightful). I do have to say that when I did the latter, making a 25 question quiz, my middle-schoolers complained that it was way too much. They like that relatively immediate "hit" and looking up to see the boss "transformation" 

I would love to hear what you think about all of this. 

_________________________________________________________________________________

I thought you may be interested to know what my students had to say about this format of "testing". I presented them with a 10 question and a 20 question format, and as you can see, their response was positive, although they all agreed that a shorter quiz was more engaging than a long one.

If you would like to know how to automatically add these scores to your leaderboard or grade book, I invite you to take a look at "Assign XP automatically using Vlookup - Google Sheets".