Embedded Files
bit.ly/button-game
Session ID: 14A - Gamify Learning Reflections: The Dragon Button Game
Welcome everyone! Thanks for visiting this site. I'm Anthony Devine, Educational Technologist at Netzaberg Elementary School in DoDEA Europe East. I created this site to gather resources for my DoDEA EPIC Presentation on Gamifying Learning Reflections. Please feel free to use/adapt/modify anything you find on this page. If you have any questions please reach out to me at anthony.devine@dodea.edu.
Welcome everyone! Thanks for visiting this site. I'm Anthony Devine, Educational Technologist at Netzaberg Elementary School in DoDEA Europe East. I created this site to gather resources for my DoDEA EPIC Presentation on Gamifying Learning Reflections. Please feel free to use/adapt/modify anything you find on this page. If you have any questions please reach out to me at anthony.devine@dodea.edu.
If you attended my session, please take a moment to complete an evaluation for my session. Thank you!
If you attended my session, please take a moment to complete an evaluation for my session. Thank you!
What is the Dragon Button Game?
What is the Dragon Button Game?
The Dragon Button Game is a way to gamify learning reflections. Getting students to think about what they are learning and get used to communicating that learning is valuable. But how do we nudge students to regularly reflect on their learning schoolwide? By gamifying the process, we create an opportunity for students to get in the habit of reflecting on what they are learning while recognizing their efforts with a leaderboard and small tokens of appreciation.
In a nutshell, gamifying learning reflections involves:
student choice (I allow students to share whatever learning they want to share through typing out their learning and/or creating videos)
a way to collect reflections (I use Google Forms and Flip)
a way to keep track of participation (I use a Google sheet tricked out with counting formulas)
a way to comunicate/recognize participation (the more immediate the better! I embed my Google Sheet on a Google Site)
make everything easily accessible to students (I have a Google Site linked in ClassLink)
What does the game look like to students?
What does the game look like to students?
Building each element of the game
Building each element of the game
Collecting student reflections
Collecting student reflections
Create a simple Google Form
Create a simple Google Form
Go to the Responses tab and click Link to Sheets
Go to the Responses tab and click Link to Sheets
Create a new spreadsheet
Create a new spreadsheet
When you send the live Google Form to students, you are now collecting their reflections in your spreadsheet!
When you send the live Google Form to students, you are now collecting their reflections in your spreadsheet!
Keeping track of who is participating
Keeping track of who is participating
Let's say you've been collecting student reflections for a while, so now your spreadsheet looks something like this
Let's say you've been collecting student reflections for a while, so now your spreadsheet looks something like this
Don't worry, these are not real student usernames.
Don't worry, these are not real student usernames.
Click the "+" symbol in the bottom left of your sheet to create a new sheet.
Click the "+" symbol in the bottom left of your sheet to create a new sheet.
Give it a name like "Query Sheet".
Give it a name like "Query Sheet".
Use formulas to gather all your responses and tag each one with an identifier that will help you ensure that students only get "points" once each day.
Use formulas to gather all your responses and tag each one with an identifier that will help you ensure that students only get "points" once each day.
=query('Form Responses 1'!A:C,"select * where A is not null order by A desc")
In cell B1 use a query formula (see text above) that will pull all your form responses from your "Form Response 1" sheet into your "Query Sheet" and sort them from newest to oldest response.
=split(B2," ")
In cell E2 use a split formula (see text above) that will split up the long time stamp in column B into 2 separate values (date and time). Drag/copy the fomula in E2 all the way down to the bottom of your Query Sheet -- probably 1,000 rows for now! Don't worry about the #VALUE warnings you get in the rows after your responses have stopped showing up.
Notice that some of the formulas I share here go in row 2. Be sure to add headings in row 1 just so you know what each row is!
Notice that some of the formulas I share here go in row 2. Be sure to add headings in row 1 just so you know what each row is!
=E2&C2
In cell A2 use a simple formula (see text above) combine the date value in column E with the email value in column B. Just like with the formula in E2, copy this formula in A2 down to the bottom of your sheet. This will give us a way to make sure that students can only get "points" once each day. We don't want students to submit a bunch in one day. We want them to build the habit of coming back over time to share their learning.
=unique(A2:A)
In cell G2 use the UNIQUE formula (see text above) to select all the unique values in column A. Because A combined the date and email columns, using this unique formula means that even if a student submitted more than once in a day, they will only earn the points once. Remember, we're trying to build habits--not just give a bunch of "points" all at once.
Now that we have that unique date and email in column G, now we need to go BACK to just having the standard name (the part of the student email address before the @ symbol).
Now that we have that unique date and email in column G, now we need to go BACK to just having the standard name (the part of the student email address before the @ symbol).
Then, we need to count how many times each student responded.
Then, we need to count how many times each student responded.
=mid(G2,6,8)
In cell H2, use the above MID formula to only select the 8 characters that represent the student's standard name. Copy that formula down to the bottom of your sheet.
=if(istext(G2),countif(H:H,H2),"")
In cell I2, use the above COUNTIF formula to count how many times each standard names appears in column H. This tells us how many times each standard name participated in our game! Copy this formula down to the bottom of your sheet. Don't worry that the names might repeat--we take care of that below.
Note: my COUNTIF formula is wrapped in an IF formula--this is my solution for just leaving a blank once names stop getting listed in column H.
Calculate points & get rid of duplicate Standard Names
Calculate points & get rid of duplicate Standard Names
=I2*10
In cell J2, use the simple multiplication formula above to calculate points for each student. For this example, submissions are worth 10 points a day, so I multiply the number of responses by 10. Be sure to copy that formula all the way down to the bottom of your sheet.
=vlookup(K2,H:J,3,0)
In cell L2, use the above VLOOKUP formula to match up each student (Standard name) with the total number of points for that student. Drag/copy this formula down to the bottom of your sheet. The last 2 formulas leave you with columns K and L on this sheet displaying student participants once--right next to their total points.
Use the "+" symbol again to create a new sheet. Call it "Student Info" and list your class/school roster. The only vital columns would be Standard Name, First Name, and Last Name.
Use the "+" symbol again to create a new sheet. Call it "Student Info" and list your class/school roster. The only vital columns would be Standard Name, First Name, and Last Name.
Add headings for Display Name in Column D and Points Earned in Column E. Also add the increments by which you plan to reward students.
Add headings for Display Name in Column D and Points Earned in Column E. Also add the increments by which you plan to reward students.
Use a formula to generate a Display Name for each student.
Use a formula to generate a Display Name for each student.
=B2&" "&left(C2,1)&"."
Add the formula above to cell D2 and drag it to the bottom of your roster. This is one way to create a display name for each student that you can feel comfortable sharing schoolwide.
Use a VLOOKUP formula to bring point totals over from your Query Sheet.
Use a VLOOKUP formula to bring point totals over from your Query Sheet.
=if(istext(A2), vlookup(A2,'Query Sheet'!K:L,2,0),"")
Add the formula above to cell E2 and drag it to the bottom of your roster. This is a VLOOKUP formula wrapped in an IF statement. It will look up the value in Column A, find it in Column K on your Query Sheet, and then bring over the corresponding Point Value in column L. If there s no value in a row in A, then this formula will just leave a blank.
Acknowledging & honoring top participants in a timely manner
Acknowledging & honoring top participants in a timely manner
Add some kind of icon here on the Student Info tab as students reach point thresholds.
Add some kind of icon here on the Student Info tab as students reach point thresholds.
I copy and paste a simple star (★) as students reach point thresholds. I do this because I'm giving a button to kids for every 50 points they earn. This way, I know which students I'm giving a button to each day--and I keep track of who I've already given buttons to.
Now that you've got display names, points, and icons going, let's make a leaderboard!
Now that you've got display names, points, and icons going, let's make a leaderboard!
Click on the "+" symbol again to create a new sheet. Name it "Student Leaderboard".
Click on the "+" symbol again to create a new sheet. Name it "Student Leaderboard".
You can even drag that tab so it's the first one that appears when you open this Google Sheets workbook.
You can even drag that tab so it's the first one that appears when you open this Google Sheets workbook.
=query('Student Info'!D2:W,"select * where E is not null order by E desc")
In cell A2 of your Student Leaderboard sheet, add the QUERY formla above. This formula will bring over information in columns D through W of your Student Info sheet and organize it all according to column E--the points column. Be sure that in row 1 you add your headers for each column.
This is the sheet that you can embed on a Google Site in order for students to see how many points they have earned. The way you acknowledge how students reach thresholds is up to you. I give students buttons. You can give them something else: stickers, pencils, or just a shoutout on the school's morning announcements.
With the Google Form going, and your formulas doing a lot of work on your Google Sheets, much of this is now automatic.
With the Google Form going, and your formulas doing a lot of work on your Google Sheets, much of this is now automatic.
However, there are a few things you will need to do to keep things running smoothly and to keep students honest.
However, there are a few things you will need to do to keep things running smoothly and to keep students honest.
As your roster changes, be sure to update your Student Info tab with new students. You can manually add them to the bottom of your current roster.
As your roster changes, be sure to update your Student Info tab with new students. You can manually add them to the bottom of your current roster.
You don't have to read every single response, but check out your Form Responses 1 tab once in a while. Scan for responses that look out of place--that don't follow the spirit of your game.
You don't have to read every single response, but check out your Form Responses 1 tab once in a while. Scan for responses that look out of place--that don't follow the spirit of your game.
Deleting a whole row on this tab removes any ill-gotten points.
Deleting a whole row on this tab removes any ill-gotten points.
Remember that your other sheets are running mostly on formulas. Avoid making changes on sheets where you have a lot of formulas working for you.
Remember that your other sheets are running mostly on formulas. Avoid making changes on sheets where you have a lot of formulas working for you.
You can build on this game. Remember I have multiple options for participation on my version of the game. However, for now this is a good start. If you get something like this going and you want to expand it, please reach out to me. I'd be glad to show you additional spreadsheet solutions for:
You can build on this game. Remember I have multiple options for participation on my version of the game. However, for now this is a good start. If you get something like this going and you want to expand it, please reach out to me. I'd be glad to show you additional spreadsheet solutions for:
--creating a teacher leaderboard
--creating a teacher leaderboard
--having multiple sheets of data for different aspects of the game (multiple Google Forms going to one spreadsheet)
--having multiple sheets of data for different aspects of the game (multiple Google Forms going to one spreadsheet)
--making different activities worth different point values
--making different activities worth different point values
--combining points from multiple sheets
--combining points from multiple sheets
This is a start! Give it a try and build your own version!
This is a start! Give it a try and build your own version!
Page updated
Report abuse