Saturday, November 10, 2018

Five ways to use Pop-ups



A couple of days ago, @JakeMillerTech posted a way to create a pop-up window on Google Docs.
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.

Saturday, November 3, 2018

Collaborative Annotations Using Scrible


Book annotation 2 by Katherine Stone

As many of you may have experienced in your own educational journey, at some point I was taught about annotating what I read. The mighty highlighter made its appearance in every school supply list my mother received, and much like you see above, I gleefully used it to mark pretty much everything I was allowed to. As the years progressed the highlighter slowly disappeared and the annotations instead made heavy use of the marvel of Post-Its which gave me the advantage of being able to write something along the text and not simply marking things that my young self considered "important".

At some point in my career as an educator, I was introduced to the idea of close reading, and with it came a whole new way of marking text that I was expected to teach my students. Colors and specific markings were the norm and while I am not against close reading or increasing student understanding of text (What is annotating and why is it an essential skill to close reading?), the idea of having my students have to follow a specific format and key made it seem more like busy work, akin to my highlighter overuse.

The questions I asked myself throughout the close reading professional development were:

  • After all of this, will the student ever go back and use all those notes for something other than answering a couple of questions? 
  • Will they ever remember that that particular document had some important notes that could be used later for something else? 
  • What about all the reading we now do online? Am I really going to print out all their research so they can use this?
  • What about using collaborative close reading, especially in my PBL classroom where students are often reading a relatively dense scientific text? Shouldn't there be a way for students to close read together?
All of these questions can be answered with just one "magical" digital annotation tool -  Scrible. Let me explain...

A couple of years ago, I developed a project for my 6th graders on Climate Change. The idea was that the students would choose a topic from a list, interact with several digital resources from places like the EPA and the NCA and collaboratively develop a product to educate others about the impact of climate change. Par for the course, except that many of them were overwhelmed by the amount of reading and synthesizing they had to do. Add to that that they had to share their thoughts on the reading with each other and organize all of the information and ideas into one cohesive product, all the hallmarks of chaos leading to disengagement.  That is when Scrible, a free tool, makes its entrance.

Scrible makes the possibility of collaborative digital annotations a reality! The only thing students have to do is create an account (using their district e-mail). Once that is done, a student can use the tool on any webpage sharing their thoughts right next to the text they highlight. If they create and share a permalink, they can also annotate collaboratively, which means other students can join a conversation about that digital text; this can take the form of questions, responses, comments and even links to other corroborating sources.


At first, students used the tool simply to remind themselves of the information, stating simply that "this information is important". However, as they progressed, and with some prodding on my part (adding cryptic replies such as "why?"), they started adding a more thorough commentary, and even inserting links that corroborated what they were reading. These annotations then allowed them not only to record their thinking but also to organize their thoughts in preparation for their project work. They served as reminders of key concepts and lateral readings they had done as they interacted with the resources. Since they were sharing the reading and annotating load, all the students were happy to add to what was being said instead of that solo "this is too much" mentality that we had before the use of the tool.

What is even more perfect, is that as long as you are logged on, the tool will keep track of the web pages you have visited and their annotations. This came into play for us when the students used pages they had annotated for the climate change project several months after the fact to support some of their building choices in their disaster-proof housing project during our human impacts unit. 

However, the best indication that the tool worked for my students was when I discovered it being used, without prompting, by former students. When I asked them why, they shared that it made their work easier since they could talk to each other about what they were reading, saving them time and allowing for everyone to join even if they were not in the same room. That, in itself, is a win for me.



There are a couple of Scrible tutorials on Youtube in case you need help signing up and using it:


It is worth noting that you can use Scrible on pdf's and "published to the web" Google documents, saving you the time and cost of printing resources you may already have to share with students.

I invite you to start playing with Scrible and share a comment telling us how it went. Until next time.

Friday, September 14, 2018

Eight reasons to love GoFormative



In a recent tweet, my good friend and colleague @JudyZaccheo posted the following "challenge"
Since this cannot be answered in a mere 240 characters or less, I had to respond "long text", and what better way than a post that can then be shared with all of you.

Much like Judy, my first foray into GoFormative was perhaps a bit bumpy. At the time, I loved the idea of some of the question types I saw ("show your work", for example) so much that chose to present about it at a district PD, but I did not really use it much myself. This had nothing to do with the platform itself, but rather with my unfamiliarity with it. Back then, you created an account and landed on the home page but there was not much to see in terms of its capabilities, and my account remained dormant for a while.

About a year ago while participating in a Twitter chat, GoFormative came up quite a few times, and I went back in if only to see what people were talking about. That led me to the first reason why I love GoFormative:

1. The Formative Community Center: A vibrant community of educators that share content, best practices, implementation ideas and all sorts of ideas on everything from pedagogy to the latest in #edtech. What makes this community unique from others is that it also provides you with a direct line to GoFormative's amazing team of developers, who listen and respond with a "can do" attitude. To most of my "it would be wonderful if..." posts I have gotten a "we'll make that happen!" or at least a "we cannot do that right now, but what if we ...; would this be useful?"

Within that community and before some of the more recent changes that have made GoFormative super user-friendly, I found a great introduction to GoFormative made by Michael Lutz:
 Formative within a Formative, which helped me explore all the different things that could be done with formative, which leads me to the 7 other reasons why I love GoFormative:

2. Ability to create multimedia-rich lessons with just a few clicks. Not only does GoFormative allow you to include a wide variety of question types, you can also embed all sorts of media. It is almost an "if it is on the web, you can add it to a Formative".


Even if there is no obvious embed code on whatever you want to add, a simple iframe generated using the tricks learned in the Formative within a Formative mentioned above makes everything embeddable. In this "Relationship between Organisms" example, I have not only a presentation and videos, but also LearningApps and a Flipgrid.

3. Along the same lines, the "enhance a PDF/Doc feature", which allows you to digitally gather answers to anything you are already using, or add reflection questions to any formative, much like Rebecca Mann did in this example:



4. Ability to assess and provide feedback in real time, with no lag! Gone are the days when you have to wait for a student to submit a paper or finish the digital work. With GoFormative you can immediately send feedback to a student or group of students - even whole class, while they are working on a formative.



5. Identify the struggling student, find patterns in answers and be ready to pull a small group or even get everyone's attention to clarify a misconception or difficult concept.


6. Track student progress towards mastery of standards. All you need to do is tag your standards, and Formative will summarize all the data for you!


7. Short on time? GoFormative now has a searchable library of ready-made formatives submitted by educators, searchable by grade level and content. No need to reinvent the wheel when you can clone to use as is and/or adapt to your particular situation


8. Google classroom integration. Whether you want to import your classes from GC or you want your formatives posted directly to GC, it is all available to your students with the click of a button. No need to remember passwords or try to find "what they are supposed to do".

Now, this is not to say that you have to do all of this. GoFormative is so versatile that you can even produce a quick GoFormative on the fly to act as an exit ticket or as a "Do Now" in a matter of seconds. That versatility is the reason why my students now equate GoFormative with being successful in my classroom.


Saturday, September 1, 2018

The "Once a Zero - Always a Zero" policy as a first step

First Step by Porapak Apichodilok 

Every year at this time, I get pushback from many of my educator friends and the parents of my students for AdVENTURE's "Once a Zero-Always a Zero" policy. I hear cries of "how is a student able to recover from this demotivating policy", "this sets up kids for failure" and "this policy promotes a fixed mindset". It seems that I am alone in this world of "First Attempt At Learning" mentality. The adults hear "zero" and appear to stop listening to how the policy is actually implemented.

What is very interesting is that I do not hear the same cries from the students sitting in my classroom. The students that are actually living and working with this policy in place, understand what it means, how to avoid that zero and most especially how it actually sets the stage for ongoing feedback and "re-dos".

Let me explain...

The only way a student in my classes will ever get a zero for an assignment is for doing absolutely nothing by the time the work is due, and I mean exactly that. Zeros are avoided by simply putting in a name on a paper and/or clicking turn in/submit if it is digital work. This is our way of having students acknowledge that there was a due date for the assignment and that if they "forgot" they are aware that there is work to be done. After what could be a pretty much blank turn in, everything is re-gradeable for full credit.

The Once a Zero policy at AdVENTURE, actually allows my students to, dare I say it, "Fail Forward". The idea is that instead of having students turn nothing in or extend due dates waiting for the student to turn in "perfect work", I'd much rather have a student turn in a piece of incomplete or badly done work, go over it and give feedback and set up a conference where we can talk about the pieces that are hard for him/her, and regrade (often several times). In order to achieve this, I have to know where the struggles, errors or misconceptions lie, and I cannot do that if the student simply avoids the whole thing and does not turn anything in.

The student who turns in something that he/she knows is not perfect and knows that by turning it in he/she is acknowledging that help is needed, also knows that help is on the way. He/she knows that there will be multiple opportunities to regrade until the work is where they want it to be, so in reality, this policy ends up fostering the growth mindset of the lifelong learner. "I could not do it when I first turned in, but I can do it now."

Of course, this only works because we allow multiple submissions and regrades, and this is where I think the adults get the wrong idea. "Once a zero, always a zero" does imply that there are no second chances, and yes, there are no second chances for those that decide to go the complete avoidance route, but there are two caveats to this:

1. We really do not allow students to not turn something in. On turn in day, you will often hear the call of "everyone stand up, you may sit when I say your name", while I go over the list of papers/digital submissions that I have. Anyone left standing after this gets a visit from me and cannot leave the room until whatever they have has been submitted, again even if basically blank with just a name.
2. The relationship we have with students is one of trust, where they know that resubmitting is not only allowed, but encouraged, and re-grading is as immediate as possible, always for full credit. The onus is on the student to take that first step by submitting what they have.

"Once a Zero, Always a Zero" has actually helped us become better at feedback and motivated our students to continue their learning journey as more empowered individuals. They have come to expect that a first attempt can always get better and that if they take that first step of submitting their work, their teachers are there to help throughout the process.


Tuesday, August 7, 2018

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.

Saturday, August 4, 2018

Internet search like a pro - a lesson

Google Search Engine by Simon Steinberger

Back when I first got a notebook cart (yes, that tells you how long ago it was), I used to explicitly teach my students how to perform Google searches. It was one of the very first lessons I taught at the start of each year, and it always came complete with a cheat sheet that my students religiously pasted to the back of their notebooks. I do not remember when I stopped teaching it, but I do know it was not a conscious decision. It could have been that it simply was forgotten as I was putting things on my beginning of year calendar, or maybe it was the year when the school went 1:1 and IT was in charge of introducing the use of chromebooks to all classes. The reason really does not matter, what does matter is that I moved on, perhaps under the assumption that since my students are digital natives and they live in a world where they are used to finding YouTube videos to DIY everything, the lesson had become irrelevant.

However, I was recently watching a group of students stumble and get frustrated as they performed a simple Google search for a Genius Hour project. When I approached them and said, "Just exclude the terms you don't need", they looked at me as if I was speaking in tongues and it dawned on me that not only had I not taught them to be effective searchers, nobody had! My students had all been intuitively finding shortcuts and relying on each other to learn them, which although very cool in demonstrating some problem solving and collaboration skills, was probably not as effective as it could be.

With that in mind, I did a deep dive of my drive to find those old resources and updated some to include them in the activity shared below. The plan is for students to make a copy of the presentation (assign it through Google classroom), and have them work through it practicing the skills so that in the end they will have a handy reference that they can then use to remind themselves if all the different "how-tos" when needed.




I know that there are many effective search tips that are not included, but I think it's a good place to start. 

What do you think, are there some other lessons that you may have forgotten to teach and that are just waiting to be re-discovered? I invite you to share them in the comment section.

Monday, July 23, 2018

Digital Citizenship for Educators - KQED Teach


Pixabay via Pexels 


Over the last several years educators have worked hard to increase their use of media in the classroom. We have witnessed how students continue to grow their online presence and many different outlets have encouraged us to seek out lessons to teach our students how to be active participants in this environment. If you are like me, you have scoured Common Sense Media and their wonderful toolkits and developed many different digital citizenship lesson sequences to guide your students, using a wide variety of platforms. As you have developed these lessons, you have been learning alongside your students many of the ins and outs of what is appropriate, how to stay safe, etc. You have probably also participated in district-mandated training and even sought out your own professional development in order to stay current with the exponential growth in legislation surrounding student privacy.

Your own depth of knowledge on the subject of digital citizenship comes into play every time you decide what you are going to have your students do to demonstrate mastery in your content area. Your students, as well as most parents, rely on teachers to help them navigate new platforms and tools while staying safe and current in the ever-changing online world. At this point in the summer, you are probably starting to think about what you can reuse, what you can tweak and what to completely overhaul in your lessons to address "fake news", evaluating information and giving proper attribution to content your students may want to use this coming school year.  Some of your tried and true plans may suffer from the disappearance of the specific tools you were using, or you may simply want to strengthen your own knowledge in order to be better prepared to address these topics, but where can you find easy to follow professional development that can help you uncover your own blind-spots?

For me, the answer came in the form of KQEDTeach. I've written about them before, but what you may not know is that not only are they free, but they have recently added several new courses specifically addressing digital citizenship. Each of the courses follows a learn-make-teach cycle, where you are not only given the tools and practice each skill yourself, but you are also encouraged to think about and develop a lesson plan that you can use in the fall to address it with students. If you complete the course and share your lesson plan, you also get a certificate which in many instances can be used to certify PD hours. Win all around!

Finding and Evaluating Information: This course walks you through the ins and outs of finding reliable sources of information and evaluating your search results. There is a whole module on lateral reading that I found particularly useful as I tweaked my original lesson plan to come up with the new "Stop the Fake News Cycle"(to open this link, and all the subsequent following lesson plan links, you will need to register on the KQEDTeach platform).

Safety and Privacy in a Participatory Culture: This course addresses online safety from the perspective of consumers of digital media, the development of your online persona and most importantly, navigating the privacy settings and terms of service of different platforms. After taking this course my whole approach to clicking "I agree" changed, and led to a better iteration of my Digital Citizenship lesson plan.

Understanding Copyright and Fair Use: Not only does this course help you dive more deeply into copyright and fair use and gives you tools to go beyond clicking "usage rights" when teaching this to students, it also provides you with ways to address the proper attribution of several forms of media. After taking this course, I knew that many of my lessons would need to fully address how my students were using media to support their knowledge and led to the inclusion of these concepts in lessons such as the one I used to exemplify how I plan to incorporate this moving forward (Proper Attribution)

Constructing Media Messages: Building upon the previous courses, this course also includes how to deconstruct media to interpret hidden messages and identify biases. The course puts you, and by extension your students, in the role of creators of media. To exemplify the lengths to which advertisers go to spread their message and how images can be manipulated I developed a completely new lesson as a final product for this course entitled Media Fake Out, which though I have not taught, I plan to incorporate at the beginning of the school year.

These are the courses that I've found most helpful under the umbrella of digital citizenship for educators on KQED teach, but as you explore you will also find many others to address media creation in your classroom. There is even one that specifically talks about managing and assessing media projects! As your summer winds down, I invite you to hone your skills and join me on KQED Teach.

Thursday, July 12, 2018

Appsmash your narrative



One of the biggest struggles I've had in the whole gamification business has been the narrative aspect of the gamified classroom. As many before me have suggested, a good narrative engages the students and helps drive the game forward. (The importance of narrative in the student-centered classroom - Adam Powley). The narrative gives meaning to the whole idea of why we are collecting points and/or struggling to reach the top of a leaderboard.

As my games have evolved through the years, I've used different themes and attempts at narratives. We've trained dragons, explored idyllic islands and even survived a zombie apocalypse, however, in all of these my narrative has been a secondary consideration, mainly due to not having figured out a way to deliver that narrative consistently. In fact, it was not until the end of the zombie apocalypse (last year) that I re-discovered the mini-videos I had created to accompany the original narrative. In the day to day business of teaching they had been left behind and by then my students had lost interest in the story they had pushed for when we started. They saw the leaderboard and acquisition of privileges as the only end goal of the game, and though this was enough for some of them, for others it became nothing more than "regular" school with a few bells added.

In an effort to remedy this, I started looking for ways to have everything I needed to move the story forward from the beginning. Now, this meant that I needed not only a changeable storyline, a place to hold everything, and a way to hold myself accountable (lest I forget again) but at the same time keep the story hidden from students with enough crumbs so that if I did forget they would ask about it.

The changeable storyline was easy. I had already decided that we would be space explorers giving me the ability to add or remove planets to explore through the different units I teach. Star Trek and its "continuing mission" being the obvious choice for this. You land on a planet, meet a new civilization perhaps capture a couple of aliens in our Boss Battles, and on to the next one. Best part, you can always revisit a planet or remain there longer if needed. With this in mind, and using LunaPic (as illustrated by Mr. Powley in his ClassroomPowerUps blog), I started a Google slide deck to house the complete story. Each slide is a mission log, has a quick recap of events and hints at what's coming next as the ship moves through its assigned sector. The idea is that it would read like a serial comic book of sorts (or like the logs entered by the different captains in the series).



I was busy with this for a few days, but the main issue remained... How could I publish only the portions of the story that have been visited ("the story o far") while letting the students know that there is more to come. After much Googling, I found that there is no easy way to password protect only portions of a slide deck, there are many ways to prevent people from editing, but not from moving forward on a deck. That being the case, I transformed a slide into a Google drawing, and that was strike two. Not only can you not directly password protect the drawing, you cannot open it directly when published to the web, it will always be a download. Not being one to give up, I finally came across a handy tutorial appropriately called "How to Password Protect ANY File in Google Drive" from Flipped Classroom Tutorials, which uses Google Forms' response validation to ask for a password and deliver a link if correct. Although using this, I still had to have individual artifacts for each part of the story, at least now I could hide them in plain sight.


As it usually happens, I got distracted with something else and left this alone for a few days, as the back of my head considered where to house all of this in a way that would be consistent with the space narrative. I considered places like Deck Toys or Symbaloo's Learning Paths which would give this a "gamy" look, but I wanted an even more engaging feel to it that would also not require students to log in to read the logs (other than what they already had with Google). Serendipitously, I came across Roberto Fantini's VR Tour "Space conquest" on Thinglink 360. Now, if you've read my posts before you know that I avoid using paid for stuff, but I have Thinglink premium already so I took it as a sign that this should be an avenue to explore. So I finished up most of the narrative for one grade level (still debating whether we will come back to earth or not) and "remixed" the image to come up with this:



The first "hidden" part of the narrative is item 3, and in case you would like to see the story the passwords are:
3) hydrogen
4) lithium
5) sodium
6) potassium
7) rubidium
8) cesium
9) francium
10) beryllium

The added benefit of this is that I can also add Easter Eggs and links to directed side quests to the narrative slides, or add elements to the story as needed, which gives me the flexibility I was looking for in my narrative.

I am happy with this solution, but wonder whether there is a less time-intensive or totally free way. If you've found it, please share. In the meantime, I' still have to do this for three other grade levels :)

Tuesday, June 12, 2018

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.


Saturday, June 9, 2018

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.

Saturday, June 2, 2018

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). (They are shared as anyone can view, but you may need to be outside of your district domain to make copies).
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)

Elementanywhere 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 could also make the quiz much longer (this 10 question quiz is just an example, with questions that are not particularly insightful). Another idea, which I have not yet implemented, would be to use the Form Responses sheet in a similar scheme to the one discussed in my post about side quests to determine the number of times a specific student answered and assigning XP on a sliding scale, but that is a post for another day.

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