A few weeks ago Mr. Sederquist (@MrSederquist) shared an amazing new innovation on Boss Fights using Google Tech. His system utilizes Google Forms as well as Google Sheets in a really fun and smart new way. I asked for his permission to share out my Reverse Engineering of his Form Fights and he let me show you some of the really cool nuts and bolts of the design. In the next few post I hope to show some of the twists I put on the design.

To start check out Mr. Sederquist’s Model. He shared these links on his Twitter Feed – The “Boss Sheet” and the Fight Form. I will provide some Screenshots as I discuss some of the cool tricks on the sheet but I suggest checking these out for yourself.

Start with a Form Quiz

The Form Fight starts with the Google Form. Students provide some basic information and then take what would normally be called a Google Form Quiz. If you are unfamiliar with making a quiz out of a Google Form you start by creating multiple choice questions.

Then open the Settings button (looks like a little gear) at the top of the form and select the “quizzes” tab.

Once the form is turned into a Quiz assign a point value to each Multiple Choice Question.

The value of the points assigned will become the amount of damage that is taken by the Boss. In the picture above a correct answer is worth 1 Point (highlighted). Add as many question but for a quick fight I would estimate about 1 minute per question not including transition time.

Create a Form Response Sheet

The thing that makes this entire Google Form Fight possible is that the data from a form can be used generate a Google Sheet with all of the relevant data. To do this the author of the form examines the google form creator page and click on the “Responses” tab. Then click on the Green Google Sheet button to create a Sheets page that will be saved in the form creator’s Google Drive.

I don’t have access to Mr. Sederquist’s form so this is my reverse engineered form.

Once the Google Sheet is created then the data will be organized by the form entry saved in a useable format.

As the form is completed the data will be added to the next row. This is what allows the main formula of the Mr. Sederquist’s Form Fight.

The “Boss” Sheet – Formula Tab – Importing the Scores

This Formula page taught me something new! By adding the URL Link to the Responses Sheet created by Google Form in cell J2 the =importrange formula in cell A2 just needs to refer to the J2 URL link to import the data!

There are 2 tabs on the “Boss Sheet” and we are looking at the “Formulas” page. Cell A2 is importing the data from Form Response Sheet. The formula =importrange(J2, “Form Responses 1!C2:C”) is telling the Sheet to look at the Sheet found at the URL in J2 and then bring back the information in column starting at cell C2 on the Form Responses 1 Tab. I can deduce that this sheet tab contains all of the student’s scores on the Quiz in this column. Because the Form is set as a Quiz we get the data found in the A column cells on this page which will appear as #correct/Total#Questions. If a student answer four out of the five questions correctly it will appear as 4/5. All of the other formulas on this page are based on this simple importrange formula.

The “Boss” Sheet – Formula Tab – Calculating Boss Damage

The Boss Damage formula is found in Cell C2.

The formula is =-1*Sum(A2:A,B2:B). There is a lot going on in this simple formula. From what I understand column B is set aside for Item cards. These extra attacks would be part of the Google Form and I have not added them in my example. Regardless, in cell C2 the attacks in column A and column B would be added together and then multiplied by a negative 1. This negative number will be subtracted from the Boss’ Starting Hit Points in cell D2 in cell E2. The formula in E2 is simply =SUM(C2:D2) which adds the numbers in Cell C2 and D2.

The teacher can modify the starting hit points for the Boss by changing the number in D2. The teacher can also change the student’s starting hit points in cell F2.

The “Boss” Sheet – Boss Tab – Displaying the Boss Damage

The number in Cell E2 is then transferred into the Boss Tab.

In Cell A5 the formula =Formulas!$E$2 pulls whatever is in cell E2 in the Formulas Tab.

The “Boss” Sheet – Formula Tab – Calculating the Students’ Damage

One of the cool parts of this Form Fight does is automatically, simply, and elegantly calculate the “damage” the attacker receives. It is similar to the Boss’ damage calculation with a small but important difference.

The formula in Cell G2 is =1*(Sum(A2:A)-5*COUNT(A2:A)). My goodness this is an amazing formula. The first thing that this is doing is the COUNT formula. The formula counts the total number of entries in column A (Count (A2:A)) and then multiply that by 5 to represent the total number of questions on the quiz. THEN, the formula adds the number of correct answers on the left side of the slash. The formula calculates the difference between the number correct by the total questions then displays that number in G2. I still don’t quite understand how it works but it does. That number is then subtracted from the Students’ Starting HP in cell F2 in cell I2 to calculate the current health. That number is then displayed on the Boss Tab with the formula =Formulas!I2.

The “Boss” Sheet – Boss Tab – Sparkline Damage Meter

This little bit was a revelation to me. Mr. Sederquist created a bar graph WITHIN a cell using the Sparkline function that AUTOMATICALLY adjusts based on the number in the Current Hit Points relative to the Starting Hit Points. In other words he created a DAMAGE METER!

I feel like this needs its own post but this is too good to overlook here. The meter is created with the formula =SPARKLINE(A5,{“charttype”,”bar”;”color1″,”red”;”max”,Formulas!$D$2}) I had to Google most of this but it Sparkline creates a chart within the cell. “Bar” tells the formula to create a bar graph chartype and I haven’t even begun to scratch the possibilities with this formula as you can see in this Support Site. The bar setting actually creates a “stacked bar graph” and “color 1”, “red” tells the formula to create one red bar graph and “A5” tells the formula what the value is to project for the red bar. “max” tells the formula the maximum value based on number found on the Formulas Tab in cell D2 which happens to be the starting HP. The same basic formula is used for the students’ HP as well.

Classroom Applications

One of the issues I consistently faced with my Dreadsheets Model was the amount of time it took to introduce the spreadsheets and then get students on the correct spreadsheets. This is especially true with the first few times using the Dreadsheets. The Form Fights was easy for students to pick up and use because the student facing tools were all simple Google Forms. I had always been looking for a quick and easy way to get students to have daily mini-boss fights and this is the perfect delivery device. I introduced my first Form Fight to classes yesterday and today and students were immediately able to begin the fight.

What I really like about the system is that it is easy for groups to attack the Boss! I have all of my Juniors battling the same boss which allows for my favorite game mechanic – The Collaborative Victory!

This is really an excellent innovation in the Boss Fight and please follow Mr. Sederquist!