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.

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".

Side quests - with semi automated XP

If you are new to the idea of side quests, consider visiting Explore Like a Pirate and GamificationEDU.

The past few weeks I have been mulling over the idea of adding more sidequests to my game. Up until now, sidequests have been few and far between for a couple of reasons. The biggest hurdle is that I have felt that it is up to me to develop the side quest, complete with resources for the students, and this simply takes too much time. Another issue is that I honestly have never put in a system for awarding XP for sidequests, so whenever a student has actually completed one that I planned for we are both dissatisfied with the XP assigned. If this was not enough, I hate the idea of prepping all of this only to have one student actually complete the side quest that may have taken me days to craft. Yes, it is a me problem.

However, my gamification #PLN often mentions sidequests as a great way to engage students, and provide in-class time for struggling or less interested students to catch up, while more advanced students happily toil on the sidequests. This got me thinking about how I could shift more of the responsibility of side quests to students by providing a board of activities with some assigned XP for attempting/submitting, but still have the choice to add either XP or items on top of that if the end product warrants it. With that in mind, I turned to Westphal's "Differentiating Instruction with Menus", and the internet, and came up with a menu of 15 ideas that could be used as a sidequest. The menu includes a general side quest rubric and explains that the base XP value of a side quest type decreases the more times it is attempted. My motivation for placing this limitation is simply that I would rather a student attempt different avenues to explore the content and not fall into a routine of recreating the same thing for different concepts. The menu also includes a space for complete student choice for my more adventurous students.

With that hurdle taken care of, the issue of how to collect the work, keep track of who did what, avoid repeats and assign XP needed to be taken care of. I toyed with the idea of writing a Google Sheets script, but that is, at the moment, beyond me. What I came up with is a Google Form whose response sheet would:

1. Provide a place to submit the work (if the work is a physical product, the students will have to take a picture and submit that. I wanted to avoid the "I created a mobile and left it in your room, but there is no XP!"
2. Ensure that duplicates of a file are not counted for assigning base XP. (I have middle schoolers who are prone to clicking submit over and over in hopes of rigging the system)
3. Automatically count the number of times a specific side quest type has been submitted and assign base XP accordingly.
4. Automatically add up the XP a student receives.

This is the sheet/form I came up with, and you are welcome to make a copy of both. The folder and its contents are shared with anyone can view. To make a copy, click on file/make a copy for each of them, placing them in the same folder. Depending on your district's permissions you may be able to make a copy of the response sheet and have the option to create the form, or you may need to copy both and link them yourself.  For those of you that may need to recreate the form from scratch, I have included comments on each sheet explaining what it does and the formulas that are attached. Brief recap of how this works:

• FormResponses1: Is where the data from the form is collected, and the query to check for unique URLs is created.
• Unique: Counts the number of unique URLs and type of product by student e-mail.
• PivotCount: Creates a pivot table that adds the values for each product type.
• BasePoints: Uses the data from the pivot table to assign base XP values.
• BaseXP: Creates a pivot table that adds the base XP earned by each student e-mail

I am sure that there is a more elegant way of doing all of this, and I welcome any feedback or suggestions.  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".

Flipgrid as a "Turn-in Bin"

Like many of you before me, I've been struck with Flipgrid fever. This simple, yet powerful tool has transformed many of my classroom discussions and activities, providing a space for students to develop their voice. There have been many relatively recent posts about different ways to use Flipgrid in the classroom.

Catch the Flipgrid Fever
17 ways to incorporate #Flipgridfever in your classroom
End of Year Engagement

I would like to add one more...

As an educator reaching the end of the school year, last week students noticed that I had not created our traditional Edmodo turn-in bin for their Scientist Wanted assignment. As I quickly reached my laptop to create it, I also realized that we would probably not have time to do all presentations. Pondering that thought, inspiration struck. What if instead of simply providing me the link, they also had to create a quick video on Flipgrid, which their peers could then watch and comment upon? I shifted focus to create the Grid, which takes all of two minutes, and vaguely remembered that when submitting a video response you can add a link, and that is when everything coalesced.

I showed students the "main grid" and showed them the prompt:

Introduce the scientist you researched (use his/her full name) and tell us about him/her. Share the information that you think would encourage us to know more. 

When you submit your video, add the link to the poster you created. You are able to do this on the screen where you add your name and the title of your video (name of your scientist).

AFTER you have completed your own video, come back, watch at least one video from your peers and respond to the information about the scientist presented.

With the addition of that final line in the prompt, not only did I get their work and presentation, but also created a space for peer-interaction on the content. This was an EOY activity, but I am now thinking that in future iterations of this, I could change the presentation to a reflection or provide a more specific prompt or frames for the peer commenting.


I would love to know how you use Flipgrid, and if you try it as a turn-in bin, would love to know how it went. Perhaps you may even be tempted to look at some of my students' work and comment on it :)

#EdTech and the 4Cs

I started my career as an educator 10 years ago. At the time, the buzzwords regarding preparing students for college and career was "21-century skills". In fact, I received Trilling and Fadel's "21st Century Skills" as a graduation present from my mentor teacher, and it was the text under which we anchored the AdVENTURE program. Of late, the 21-century skills framework has been distilled into what we now call the "Four Cs" (communication, collaboration, critical thinking, and creativity), and while all of them can be taught and practiced using Google Drive Tools, there are many other free tools that can also be used effectively by both teachers and students as we progress in our educational journey.

These are my favorite:

📝 Collaboration: Tools that help us work together

Scrible: My students use this chrome extension to curate, annotate, tag and share articles. The extension also reminds you when you are on a webpage you previously annotated and prompts you to load previous annotations.

Padlet: A digital canvas that allows users to add all sorts of items as they work together on a project.
I know. There is an uproar among educators because of their new pricing/limits on the number of free Padlets. However, once a Padlet is no longer active, you can export the content to make room for more.

Trello: A project management tool that helps keep teams organized as they work. I create board templates for students. They make a copy of the board, add collaborators and move things around/upload documents, etc. as they get done.

💬 Communication: Tools that help us share what we've done

Blogger: Although part of the Google Apps suite, I still mention it since it is not part of Drive and it is a powerful tool to elevate student's voice. My students write a post at least once a week sharing their learning with the world at large.

Flipgrid: Easily and quickly create topics for students to discuss ideas. These quick videos provide insight into student thinking. I also use it for mini classroom presentations that students can then watch over and over.

Seesaw: Allows students to capture, organize and share their learning. Extremely popular with my elementary colleagues as a way to share classroom activities with parents.

Jilster: A really cool tool that allows you to create online magazines. The best part is that it is collaborative. I create a magazine, assign pages to student editors who can then work collaboratively on their assigned pages.

🎨 Creativity: Tools that help us develop products to explore the content 

WeVideo: Online video editing software. We video has a shallow learning curve and gets students creating in minutes. 

Canva: Easily and quickly create visually stunning flyers, posters, collages, infographics and more. They also have a complete selection of tutorials that help students (and teachers) explore how different design elements work together (or not) to tell a story.

Tinkercad: This easy to use 3D modeling software allows my students to bring their ideas to life in a way that 2D drawing cannot, even if it remains as a virtual product for lack of a 3D printer. 

MakeBeliefsComix: What my students and I like about this site is how easy it is to start creating and the fact that the comic can be printed or e-mailed. Its major con is that it is a "one sitting" deal. However, other comic creator sites come at a price making this site my go-to for quick student developed comics.

💡 Critical Thinking: Tools that help us go beyond the content.

Coggle: Collaborative mind mapping Chrome extension that integrates with Google Drive. My students use it to brainstorm ideas and develop maps to show how the content they are learning integrates with previously understood ideas.

KQED Learn: Students work in a semi-gated environment (all students must be attached to a teacher but can communicate with each other), responding to prompts and investigations. Absolutely awesome to help students extend their thinking as they curate resources and craft responses. This is only available to students in the U.S.

I'm sure that I missed some of your favorites. I invite you to add them in the comments.

Digital Citizenship - #IronChef edition

A few days ago, @goformative shared @jcorippo's interview on Every Classroom Matters, "What I learned About Student Engagement from Watching TV". In it, he describes an interesting Iron Chef-inspired protocol he developed to modernize and re-energise the traditional jigsaw activity we learned about in our certification courses.

I immediately became intrigued by the idea, thinking not only of how I could use it in my Science class but also how useful it could be in other content areas. As I thought of the possibilities, I decided that a topic that would lend itself well to this protocol was our Digital Citizenship unit. For starters, my students receive this information from several teachers at the start of every school year, but as the year progresses they start "forgetting" about it and begin to copy/pasting material without proper attribution. They also, because they hear it from adults, often do not pay as close attention to it as they should. The more I thought about it, the more I became convinced that using this strategy would not only be beneficial at the start of a school year having the different groups present their 30-second slides and hearing the information over and over from each other, but also that I could, every month or so, have a random group present again as a refresher. Even better, as issues will inevitably arise with some of the content (oversharing, cyberbullying, plagiarism, etc.) I could call on those experts to once again present their Iron Chef work whenever it is appropriate.

With this idea in mind, I created three Iron Chef templates (going along with our school mantra "Respectful, Responsible and Safe).

https://goo.gl/sbe8zE

https://goo.gl/Sffdc2

https://goo.gl/CsY6Fa

For each of them, I also have a secret ingredient Flipgrid (made public), in which each of the experts will post their key takeaways, and that I am also envisioning using as a reference whenever only a specific group or student needs a private reminder.

As this is my first foray into this activity, I would find your comments useful. Have you tried something like this?

Data visualization, an important 21-century skill

Although the gathering of raw data and its analysis is not new, in this era of information overload, it has become an increasingly important skill. A quick search of the importance of data visualization opens up a multitude of articles that tell the story about the need to teach our students to manage, work with, and analyze vast amounts of information into visuals that can easily convey complex concepts almost at a glance.

Data Visualization - What it is and why it matters

The Importance of Big Data and Data Visualization

In the context of science the use of data visualization is deeply rooted in all three dimenstions (practices, disciplinary core ideas and crosscutting concepts). Data visualizations not only force us to analyze and interpret data, but also create visual models (charts, graphs, etc.) with it and construct arguments derived from it. The data comes from the disciplinary core ideas. For example, you must collect data on things like increase in global temperatures or carbon emissions if you are studying human impact on Earth systems, while data in population variations over time is at the core of Ecosystem dynamics, functioning and resilience. If we think in terms of crosscutting concepts, data visualization helps us identify the patterns in the data and understand systems and system models.

With all this in mind, and considering that the data is out there, how do you teach students data visualization techniques? It is not enough to simply tell students to create an infographic. After all, you do not want them to create a collage of images that do not tell the story of the data. In looking for anwers to this, a while back I took the course on Infographics on KQED Teach 

"KQED Teach is a free online learning platform, that supports educators’ growing media literacy needs by helping them develop the media skills necessary to bring media production to their learning environments."

The course itself contains easy to digest modules that guide you through the design process for an infographic, considering elements such as font combinantions, colors and great places to manipulate images. This in turn is easy to adapt to any lesson you are currently teaching where data plays a role.

Here are a couple of examples that my students have created using the skills I was able to teach them through the use of this course.

Invasive species:

Endangered species:

Severe Weather Project

There is, of course, still work to be done, especially regarding the creation of graphs and charts to map ideas, but I think we are off to a good start. What do you think?