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.

Five ways to use Pop-ups

A couple of days ago, @JakeMillerTech posted a way to create a pop-up window on Google Docs.

When you see the phrase "Code a popup message for your #GoogleDocs" it sounds really complex 🤔, but it's not. Watch, I can do it in a ~40 second ⏳ #EduGIF: #elearning #21stedchat #21stcenturyskills #hyperdoc https://t.co/Rxo5vmcNLX pic.twitter.com/rDMRBSMb81

— Jake Miller (@JakeMillerTech) November 6, 2018

I was intrigued by the idea, especially after a couple of messages back and forth with Jake and @dyerksjr1 revealed that even with the "must be editor of the document" limitation, it could work via Google Classroom using the create a copy for each student and insert "from Drive" options (link). So I set about finding out whether you could include links, change fonts, add images (not just emojis), etc. Two frustrating days later, it dawned on me that I've never seen these kinds of adjustments on Google's actual pop-ups, so while I still have hope that it perhaps can be done, I instead changed my focus to what I could use the pop-up idea for.

So let's start by referencing Jake's original post "Add a Popup Message to your Google Docs", where he provides step by step instructions on how to add the code to your document.

The code for a Google doc is:

function onOpen() {DocumentApp.getUi().alert(<head> "insert your message here" </head>)}

If you would like to add to Slides instead, you would use:

function onOpen() {SlidesApp.getUi().alert(<head> "insert your message here" </head>)}

and for Sheets, well:

function onOpen() {SpreadsheetApp.getUi().alert(<head> "insert your message here" </head>)}

With that out of the way, it is time to have some fun and think of ways of using it:

1. Reminders:

2. Provide links: Although the link itself will not work, students can always copy/paste it:

3. Give words of encouragement:

If you do not mind a little extra work, you can even provide individual pop-ups or even chose only a couple of random or carefully selected students for whom the pop-up appears.

4. Provide hints in a Break-out Edu activity:

5. In a gamified environment, perhaps even provide some hints about Easter Eggs. 

I'm sure you can think of some other awesome uses, and if so inclined tell us about them in the comments.

Thank you @JakeMillerTech and @dyerksjr1 for teaching me about this fun addition to my teaching arsenal.

Beat the Teacher - Back to School Edition

On many occasions during the game-based learning and gamification Tweeter chats I participate in, we've talked about game skins (#games4ed, #XPLAP). For those of you that may be unfamiliar with the term, a game skin is a cosmetic change to a game that does not change the basic gameplay. Much like what I shared when I talked about my FLUXX Mod project, or what @MrPowley shared in his Skin in the Game post the idea is not to create a new game from scratch, but simply to change the topic of the game adapting it to specific situations.

As I was going through my collection of board games thinking about what I could do to start the 2018/2019 school year I happened upon the Spin Master game called "Beat the Parents". As I remembered, the gameplay was pretty easy, but being a full-on trivia game it did not make my family's game-night rotation too often. However, after dusting it off, I figured that it would be perfect to mod as a Back to School game, giving my class the opportunity to review expectations, policies, procedures, and locations of classroom items, while allowing me to get to know my student's trivia and preferences.


Thus, I started by creating the board (click to open the file), which I plan to project to the class, using post-its as tokens (mover pieces) so that the whole class can play at once.
Normally, I would have created and laminated the necessary cards, but I decided against it for this skin since I want the students to come up with their individual "getting to know you" trivia questions. The plan is to provide each student in my 5 periods with a couple of index cards where they can write questions like "What is my (the student's) preferred nickname?", "How many siblings do I have?" or "What is my preferred sport/book/content area?", really anything that would be traditionally asked in a student interest survey. On the other hand, I prepared a file with the teacher questions that are specific to my class (sharing to give you ideas in case you also want to try this out).

Since I was not going to create physical cards, I then had to figure out a way to create digital wild-cards. These became the numbers 1-20 at the top and bottom of the board you see above, and the plan is that when we land on a wild card, I will roll a 20 sided dice and click on the corresponding card. The wild cards linked on the board are simply links to individual slides in this slide deck.

I tried to make the wildcards somewhat generic, but if you find that they are too specific or you are interested in creating your own set, just remember that you can obtain the links to individual slides in any slide deck as explained here.

The gameplay itself is exactly as the original (Beat the Parents instructions), except that there will always be only one question per turn (in the original there are up to three). The game is so easy to play that once you have the board, you can have students create questions for a topic at any point during the school year for an impromptu review and quick game of Beat the Teacher.

While I am certain that the students will beat me when I present this as part of my getting to know you activities, before knowing the new students very well or at all, I think they will get a kick out of beating the teacher, and who knows, maybe I'll surprise them.

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.

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.