Quest #6 Design A Unit
Map out your Unit
Gamify Lesson Plan Sample - Look at a sample lesson plan for inspiration!
Gamified Lesson Template Choose File, Make a Copy, Rename it! You will fill this out for your gamified unit. You might have changed your mind since you first starting thinking about the unit you plan to gamify. That is okay! Follow your heart and design the unit that makes the most sense right now. You will now pull everything together into quests and you will get your Leaderboard programmed and ready to go!
A few ideas before you get started…..When giving points, remember that if you can’t measure it or prove it, then it really can’t be earned. For example, it would be unfair for a student to earn points because you love their shirt or because they gave you a compliment. HA HA! You can go any direction, but to eliminate arguments about fairness, just be sure that the earning of points is objective. For example, any student who dressed up for spirit day or the amount of points you earned on a quiz or if supplies are put away properly. I also deduct points as a penalty. So, if a student is on their phone without purchasing a pass, they get fined. If a student lines up at the door at the end of the hour, they get fined. I actually use the game system to enforce the procedures I want followed! As long as your demeanor is light and fun, it actually works well!
Quests
Instead of just calling your assignments quests, you can identify them as a types of quest so students will know what to expect. Below are some examples of different types of quests you could design.
Collect Quest could be to collect/harvest x resources, supplies, information.
Puzzle Quest is to solve a problem (might also be called a Code Cracker Quest).
Share Quest is to share or present x resources.
Drama Quest is to enact a system or behavior.
ConQuest is to capture a territory or resource.
Spy or Scout Quest is to observe and gather information and report back.
Research Quest Research a question and return with the answer. This research might
take any number of forms, from questioning friends and teachers for viewpoints to reading and more.
Heroic Quest Could be tests or quizzes
Social Quest - Discussion/Participation
Lesson Plans that work
Now you will be sure the following requirements are met for your lesson plan. Complete the Lesson Plan Template for your unit. This will guide the direction of your Leaderboard. Be sure that you are prepared to expand each quest since you will be organizing the instructions and links on a Google Site. It would be helpful for you to include links to your resources within the Lesson Plan Template. I recommend that your lesson plan has all of these attributes:
- Your plan is complete.
- Your plan shows thought and thorough planning.
- The activities within a quest are designed with best practices in mind and allow for students to ‘go deep’ into the learning (AKA higher level thinking).
- Your big mission has a minimum of 5 quests.
- You have points issued for a minimum of 3 quests.
- You have a badge issued for at least one quest or accomplishment (use the avatar Google drawing instructions to design your badges).
- Your final assessment is included (linking it will work). It can be a test, virtual lab, scavenger hunt, group project, etc and does not have to occur ‘online’. It can be something that happens when students are ‘unplugged’ but I will need the full instructions for the assessment.
- You have a working leaderboard to accompany the lesson plan.
Programming a Leaderboard in Sheets (scroll down for a manual and video tutorial)
- Now you are ready to create a Spreadsheet that is your main file.
2. Create a second spreadsheet file that will house your leaderboard only.
3. Before you begin to start Forms, you might need a quick overview if you are new to this! If you need help getting started with Forms and linking your responses, here are some great video tutorials to get you started! Here is a more advanced way to use Forms to create Choose Your Own Adventures if you are interested.
4. Create a Form that asks students for their name, gamertag, and avatar url (remember - they will have to create the avatar using Google Drawings). This first form is VITAL to continue with the programming of your leaderboard so choose to collect the responses for the first Form in the Spreadsheet you already created that is your main file.
5. You should now begin creating your Forms/Point based activities to accompany each quest. Each Form needs the response to be submitted into the spreadsheet that is your main file. If it is an activity that is not automatically scored, then you will manually enter the scores for each student. We will deal with that later.
6. In the main file, create a sheet called Badges. Copy/Paste the url’s for your badges here. This is pretty handy for issuing badges and you can easily copy and paste the entire contents of the sheet into a new spreadsheet later (when you begin a new Mission).
7. Before programming the main file, go through and take all the Forms as a sample user. This way you will know if your formulas are working properly.
8. Now you are ready to program the main file.
9. Insert a column between the avatar url and the gamertag. Call it avatar icon. In the first row type this formula. Remember - sometimes copying and pasting formulas causes errors but you’re welcome to try it! The reason this image formula is soooo wonky is because using a simple image formula would not allow me to copy the formula to other cells. Some people have used a simple image formula and it worked so here is the simple AND complicated versions!
=image(C2)
Change C2 to the cell that contains the url of your image
=image(indirect("!C"&ROW()))
Change !C to reflect the column that contains the url
10. Label the columns following the gamertag as
Total Points Badge Quest#1 Quest#2 Etc.
11.For each badge column you will need to write the formula for display. Here are some options:
Display an image for any score or text or else leave blank.
=if(J2<>"",image("url"),"")
Change J2 to that determines if a badge is earned.
Change url to YOUR published badge url.
Display an image if the sum from one or more quests meets requirements or else leave blank.
=if(sum(M2+N2+O2)>169,image("url"),"")
Change M2, N2 and O2 to be the cells you want to sum.
Change 169 to one point below the minimum score they can get to earn the badge.
Change url to YOUR published badge url.
Display an image if a cell contains a certain piece of text. This would be useful if you want to give a badge based on a specific answer in a cell.
=if(C2=”The war of 1812.”,image(“url”),””)
Change C2 to the cell that contains an answer with text.
Change The war of 1812. To the text you are looking for.
Change url to YOUR published badge url.
12. For each Form that is linked to the spreadsheet you will program the instructions for pulling the gamers score from the Form responses.
This formula says, “Look in a cell on this particular sheet and find this particular gamertag. Now, go find that gamertag on this other sheet. When you find it, look at this column for his score and take display it in this cell. Now, if the gamer doesn’t have a score yet, just leave this cell blank.
Before we move on I want to give some background info about this formula. When I first started using it, my Form scores were only reported in fractions and the formula wouldn’t read the score. I had to use the left code to have the only the first two digits to the left of the cell imported. Since then I have learned to change the number format to Automatic and I no longer need to use the left code. If you should run into these same problems with your formula, here is the old code I used:
=iferror(INDEX(Left(Quest2!B:B,2), MATCH(E2,Quest2!C:C,0),""))
The difference is Left( and 2), which are in bold in the formula.
=iferror(INDEX(Quest2!B:B,MATCH(E2,Quest2!C:C,0),""))
Change Quest2! to the name of the sheet of responses for that Form.
Change B:B to the column on the sheet of responses that contains the score.
Change E2 to the cell that contains the gamertag on THE SHEET you are programming.
Change C:C to the column on the sheet of responses that contains the gamertag.
If you want students to be able to retake a Form more than once and you only want to report the highest score then you can use the following formula.
=iferror(max(filter(Sheet!B:B,Sheet!C:C=E2)),””)
Change Sheet! to the name of the sheet of responses for that Form.
Change B:B to the column on the sheet of responses that contains the score.
Change C:C to the column on the sheet of responses that contains the gamertag.
Change E2 to the cell that contains the gamertag on THE SHEET you are programming.
13. You will not program instructions for any activity that you will manually enter scores, but you will still want to label a column for that activity.
14. In the Total Points column you will program it to sum up the points from each quest.
=sum(J2+K2+L2+M2+N2+O2+P2+Q2)
Change J2 - Q2 to the cells which will have scores.
15. Now, click Add-ons, CopyDown, CopyDown settings. This add-on will search your spreadsheet for any sheet that accepts form submissions. It will copy all formulas in row 2 to any new submission. Maybe you are wondering why we don’t just grab the corner in the bottom right of the cell and drag the formula down. There is a good reason for this! The drag feature is great for existing data or for data that you will manually enter later. The problem is that every time a form is submitted a brand new row is inserted to house the new data rather than filling the existing row (that has a formula) with the new data. For this reason, we use CopyDown, which will automatically copy the formulas to any new row that gets inserted.
16. You have two options for publishing your leaderboard. I prefer to use a separate spreadsheet file (you titled yours leaderboard only) since this embeds nicely into Google Sites. You can also house your leaderboard within your main file, but you only be able to provide a link to the leaderboard rather than embedding it as is.
To embed at the site:
Open your leaderboard only file and choose share, ‘anyone on the web’. This will allow anyone to view it.
In cell A1 type this formula:
=sort(importrange("url","Intro!D:I"),3,False)
Change url to your main file url.
Change the Intro! To the name of the sheet that has all your scores and totals.
Change D:I to reflect the range you want to display. I typically show the avatar, gamertag, points and badge columns.
Change 3 to the column in your range that contains the total points, which is what you want to sort.
For example, in my range D:I, the column sorted is F.
Keep the False for a leaderboard. True would change it to lowest to highest score.
At the website you will choose to embed slide and you will locate and choose the leaderboard only file.
To link it at the site:
Create a new sheet by clicking the + button in the bottom left and click the arrow, choose rename and rename it leaderboard. In cell A1 put this formula:
=sort(Intro!D:I,3,False)
Change the Intro! To the name of the sheet that has all your scores and totals.
Change D:I to reflect the range you want to display. I typically show the avatar, gamertag, points and badge columns.
Change 3 to the column in your range that contains the total points, which is what you want to sort.
For example, in my range D:I, the column sorted is F.
Keep the False for a leaderboard. True would change it to lowest to highest score.
Go to File, Publish to the Web, Under Link you will choose only the sheet that contains your sorted leaderboard. Choose to publish. Copy the url and link it on your site.
17. Now you should take all the Forms again as a sample user and make sure everything works properly.
