Sunday, February 10, 2019

Foldables or what to do when there are no devices allowed

I am sure that many of you are familiar with the foldable idea. There are books and blogs specifically dedicated to them, and the whole interactive notebook movement is still making the rounds. Although I love the idea, I am more of an #edtech teacher and tend to prefer to find opportunities for students to create digitally. That is until I am absent from the classroom, and I need to find an activity that ties into what we are doing and that will not need the use of devices. Too many times I've been burned by either my sub not knowing the rules of using tech with students or my students taking that absence as an opportunity to be off-task on their devices under the guise of "doing research".

So for those times, I find that foldables are "just right" in that they allow for a bit of scaffolding, but they are easy enough for students to handle without my presence. Unfortunately, every time I need one and do a Google search, I end up with several Pinterest hits that lead to TpT - a site that I prefer to avoid.

Today was one of those days, which spurred the idea of sharing the one that I created this morning for human body systems, along with some others, in case you are in need of them.

Monday, January 21, 2019

Experience Points for Student Goal Setting

A couple of days ago, I posted about using forms and sheets for student reflections. As I was pushing it out to students, I started thinking about how to make it easy for me to assign XP and leveled badges for its use. Ideally, I would have some sort of automation that:

1. Allowed me to see students' latest goal without having to open each individual spreadsheet, and
2. Assigned XP and badges automatically.

Students latest goals:

I created a new spreadsheet, copy pasted my roster in column A, and used the importrange formula to bring the timestamp and goal from each individual grade reflection Goal sheet. For example:

=IMPORTRANGE("", "Goals!$A$3:$B$3")


  •" is the URL of the individual grade sheet
  • Goals is the name of the sheet I am bringing over
  • $A$3:$B$3 is the cell reference for time stamp and goal

Painstaking in that you do have to change each cell reference, but you only have to do this during set up. It is worth noting that when you do this the first time, you will probably get an error telling you that you need to grant permission. Just click on the cell and allow access. Also note that the sorting by timestamp was already done in their individual sheets as explained under Form Responses 2 in my previous post.

Automatically assign XP for goals

This portion of the post assumes that you are already using spreadsheets to house your leaderboard, and works with any version you may have created. If you are looking for one, I offer mine, Mr. Matera's or Mr. Powley's.

As we all know, the faster XP are show/given out, the better. This posed a problem for the assigning of XP for this activity since nothing would replace the student/teacher conferencing about their goals or the student actually reflecting on them. However, I figured that if I could at least give out points and badges for some measure of completion, I could always go back and make time for those conversations. As I toyed with how to do this, I thought about making sure that some specific words were included, but quickly gave up on that simply because my list of possibilities grew rather quickly. I settled instead for a simple word count and figured that 30 words at a minimum per goal are sufficient. Letting Google spreadsheets do the work for me, I added a hidden spreadsheet to the grade reflection spreadsheet template I shared before. When you make your own copy of that document, simply unhide it

As displayed when unhidden, this sheet is simply the place where the counting and calculation of XP happens and can be modified to suit your needs.

With that in place, it is back to the leaderboard page, where you would need to decide where you would like to maintain the information and when to assign badges. 

The formula used to bring the XP from the student grade reflection sheet to the corresponding cell in your leaderboard is:

=IMPORTRANGE("", "Wordcount!C2")

As before, each spreadsheet reference is unique to the student grade reflection sheet, so it needs to be manually changed and the spreadsheets need to be linked.

Finally, the awarding of badges is done using the techniques described in my Leaderboard and Badges post, using a conditional formula that awards badges according to the total points accumulated from the word count.

And the badges are displayed on the individual student sheets which feed from the leaderboard as explained in this post.

In an ideal world, the badges would only be assigned if the student actually met the goal they set for themselves, but that is a conundrum that I have yet to solve. Perhaps you have some ideas?

Friday, January 18, 2019

Reflective Students with Google Forms and Sheets

It is the end of the semester, and like most teachers, my colleagues and I are currently under the barrage of with last minute regrade requests that should have been taken care of months ago. Like you, we published grades in our LMS as assignments were due, and the students received feedback with full knowledge that their grades are fluid. We have been encouraging them to take action on the feedback, make corrections, additions, etc. We have had individual conferences, sent e-mails, and clarified, but still, some of them are surprised when they finally log on to look at their grades, and the inevitable "What can I do?" question pops up. At this point, the reply is, "Go do what I've been asking you to do all along."

As we pondered this in our last PLC meeting, we figured that one of the issues our students have is that not only are they not looking at our LMS consistently, they are not opening the rubrics and comments left on their assignments. By the same token, they are not thinking about the work habits they are cultivating and forget about any type of goal setting related to their work or performance. The question then became, how can we set up a routine that will allow our students to do all of this consistently.

@judyzaccheo shared her use of Sown to Grow, a platform whose goal is to "Empower students to set goals, reflect on strategies, and learn how to learn.". While it looks awesome and an easy way to address these issues, if you have been here before, you know that:

1. I do not have the funds to pay for stuff like that ( even if it is inexpensive), and 
2. I do not like to use platforms that cannot "move" with the students. (I like to use things that my students can keep using even after they have left my class).

So, I spent the last few days coming up with a solution, and along the way, I learned some things about how to allow anyone to make a copy of a Google form, automatically sort form responses by timestamp so that the latest shows on top, and forgo creating charts within a spreadsheet using a sparkline instead. 

Before we get into the how-to, let me show you the final product.

We start off with a traditional Google form. The form is divided into sections that allow students to input their grades for each subject, discuss what made them successful (or not) in each class, and set a goal for the next week.

This feeds into a spreadsheet that then organizes the information so that the latest goal and information is at the top.  I figured that if we are doing this every week and things populated at the bottom, the students would never scroll down to find the latest entry. This is also why I needed the "Goals" sheet as the landing sheet. They need to see, front and center what they said they were going to do.

Finally the "Content Area" sheets, where students can see their grade trends, and notice when their grade changed, and the work habits that led to those grades.

Hopefully, you are still with me as I share just how to do this. First off, I started with the creation of a Folder with "Anyone with the link can view" permissions that would house both the master spreadsheet and form. This step is important when sharing the forced copy of the form.

Copy of a Google Form. 

In the past, I've shared copies with teachers by simply having them create copies of the spreadsheet linked to it. I tried to do the same with the students, first by making them copies in Google classroom and when that didn't work forcing a copy of a link I sent them. For some reason, this does not work when working with student accounts! Even when the Form button appears, once students click on it ends up creating a blank form. Yup, you guessed it, spent a class period with my pilot class trying to troubleshoot with them and then the bell rang... After some digging that still did not explain why it would not work, I came across Mrs. Drasby's post. Following her instructions, I was able to create a link that would force that copy of the actual form for my students. That was all fine, but now I had to walk my students through recreating the spreadsheet with all that sorting and tabs that I had painstakingly created.

Copy of the Spreadsheet 

While some of my students would jump at the idea of working with the different formulas and conditional formatting that make the spreadsheet work, I know that for many it would end up in frustration and me running around "fixing" it. So instead I created instructions that would allow the students to copy each of the sheets from my master with minimal possibility of error. 

Note, these start off with the copying of the form. Once the students had their personal forms and sheets, it was simply a matter of having them submit their sheets to me in Google classroom so I would have them all for an easy looking into.

How the spreadsheet works

While I have shared my form, spreadsheet and instructions in this folder for you to use "as is", I am under no illusion that it suits your needs perfectly. So a little explanation that would allow you to recreate or modify is in order. I do recommend that when you make your own copies, you house them in a folder with "Anyone with the link can view" permissions to ensure that you do not run into problems when students copy your form. Anyway...

Form Responses 1

This is simply the sheet created by the form. Notice the purple box indicating that it is linked.

Form Responses 2

This sheet sorts the responses from Form Responses 1 in ascending order. The only thing it actually contains is this formula in cell A1.
=sort('Form Responses 1'!A:O, 1,0)
If your form has more or fewer cells than A:O, simply change the cell reference.

Goals sheet

This sheet brings up the responses from the Goals column from the sorted Forms Responses 2, using

=ARRAYFORMULA('Form Responses 2'!A1:A) in cell A2 to bring up the timestamp in column A
=ARRAYFORMULA('Form Responses 2'!O:O) in cell B2 to bring up the goals typed in from column O

Column B has a set of conditional formatting color-coding the responses when they include the content area names. Here the idea was that students can look at how frequently they make a goal for a specific class and reflect on whether they are meeting their goal or not.

Content Area Sheets

Bring up the grades reported for each class to its corresponding sheet along with the "what made you successful or not answer. In each, the only thing that changes is the cell references. 

=ARRAYFORMULA('Form Responses 2'!A1:A) in cell A2 to bring up the timestamp in column A
=ARRAYFORMULA('Form Responses 2'!H:I) in cell B2 to bring up the grade and comment, which have adjoining columns, in this case, cells from column H and I. 

Column B in these sheets has conditional formatting as a visual for students to notice when their grades for a class dip or move up and reflect on the habits that took them there.

Finally, these sheets also contain a sparkline formula that creates the added "graph":
I toyed with the idea of adding an actual chart, which you could still do. Since it was really an added visual to show quick trends I opted for a sparkline. The one I used is
=SPARKLINE(B3:B, {"charttype","column"; "axis", true; "axiscolor", "red"}) - Using the information now housed in column B, but you could change it up with any of the other versions found in this support article from Google.

Using this with students

I basically just rolled this out a couple of days ago. The intention is that students will input answers in their forms weekly, during class time, giving them a moment to stop and reflect on how they are doing. I know, from the examples submitted that we definitely have work to do in the goal-setting department, but my hope is that with instruction, consistency, and practice we will finally get rid of those last minute "What can I do to bring my grade up?" requests. I am also thinking that it is a great resource to have at parent conferences and SSTs. It even helps address ISTE standard for students 1a "Students articulate and set personal learning goals, develop strategies leveraging technology to achieve them and reflect on the learning process itself to improve learning outcomes".

What do you think?

Friday, November 23, 2018

Drag and drop with Google Draw and Slides

This morning, as I was looking over my upcoming lessons on analyzing webs I found out that the app I used to create drag and drop assignments is no more. Since before we get deeper into what happens in a food web when the population of an organism decreases or increases I need to be sure that my students are able to track the different food chains within a web, and we have been struggling with what is known in my classroom as "the arrows mean something", this was not a step I was willing to simply forgo. So I turned to the trusty internet for something already made. As I was inputting different search parameters I stumbled upon Matt Miller's "Creating moveable digital activities with Google Drawings + Slides". I watched the first part and, being a Google Drawing fan I went ahead and created my drawing, stacking multiples of the same text boxes as needed by simply copy/pasting them on top of each other and placing them as a stack on top of an "empty" box.

I felt rather pleased with myself and called my daughter to try it out. Dutifully she did, and immediately two super important things became evident:

1. I needed to be able to lock the background
After another round of searching and watching Matt's whole video, I found out that while you cannot lock the background on Google Drawing, you can set an image as a background on Google Slides. 
2. I also needed to be able to "lock" the text the students were dragging so they could not modify it accidentally.
Instead of adding them as text boxes, I created the text and used the snipping tool to create them as images. While they can still be deleted as a "block" the content cannot be modified.
I am sharing the student template to give you an idea of what you can do.

I then started thinking, what else could we do with this? I love the idea of the drag and drop but also wanted this to be a little more challenging. Inspiration struck as I moved on to a different grade level, where I decided that students could use the traditional labeling assignment as a drag and drop that leads to a presentation. Using the same technique as before, but adding links to blank slides students could do more than just a simple labeling assignment.

For example, in this assignment where students will be asked to label a plant and an animal cell and use that as a springboard to create a presentation detailing organelle functions.

What do you think? What other uses of the drag and drop do you see yourself creating?

Tuesday, November 20, 2018

First Perfect Hit - The Match Function

A couple of months ago I shared with you how to create Boss Battles using Google Forms and Sheets and assign XP automatically using the Vlookup function. I spent most of my summer creating all the Boss Battles I intended to use this school year and tweaking my individual student sheets so that if a student scored a "perfect" hit on the boss they would capture it and add it to their collection.

The response of my students to this whole idea has been very encouraging. They actually look forward to Boss Battle days, literally rushing to class to participate in what you and I would actually call a testing day. However, there were a couple of little nagging issues that my more hardcore gamers immediately asked for: Extra XP for "first hit" and/or for "first perfect hit". Apparently, this is a "thing", and while they were satisfied with my answer at the time "Don't know how to do that other than scouring the sheet, which I do not want to do. If you figure it out, we can implement it", it stayed in the back of my head. 

A couple of days ago, I once again sat down to try to figure it out, and it finally crystallized.

On one of my existing Boss Battle Sheets I added:

- First hit: Easy, since you only need to bring up the first entry on the form
='Form Responses 1'!$B$2
- First perfect hit: This formula looks up the e-mail of the student Form Responses sheet (column B) and returns the value found in column C when it equals 10 (the perfect score for this Boss battle). Once it finds the first one, it stops.
=index('Form Responses 1'!B:B, match(true, 'Form Responses 1'!C:C=10, 0))  
These two formulas made the students happy since at least they now had the "bragging rights". The addition of extra XP for those two instances can be done manually, or by adding a "Perfect" sheet to your BossBattle (as in the example), where you again report those two values and input whatever XP you wish for them. You can then use the Vlookup function explained in my previous post to have Google sheets find the value for you. That formula in your leaderboard would look something like:
=IFERROR(VLOOKUP(A2,IMPORTRANGE("GoogleSheetID","Perfect!A:B"),2,0), 0)
I now have to add those formulas to all my Boss Battles, but I know that it will be a nice surprise when we come back from break. What about you, any other tweaks to share?

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.