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?

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.

Evidence Based Rubrics using Google Forms

It all started a few weeks back. My students were putting final touches on one of their projects, and as is usual in my classroom, I asked them to bring out their rubrics and go over the work. I also asked them to go over each other's work, with the rubric in hand so they could provide some feedback to each other.  At this point, they were supposed to act on the feedback before presenting their final submissions. Everything was OK; I saw the exchange of papers and students went back to work. Then came the final submissions that included both rubrics. I sat down to grade and as I looked over the first submission and compared my graded rubric with the ones the students had submitted I had to stop. Was I looking at the same piece of work? The students had given themselves perfect to almost perfect scores for work that was quite sub-par. What had gone so completely wrong? How can I ensure that students look at the rubrics and identify the specific items that are done correctly or that may need work? I needed to teach them how to provide evidence for the scores and not simply mark an "X" on a rubric with no thought about what it means.

So I set about creating my first evidence-based rubric. I had already created some rubrics using Google Forms (Alice Keeler showed me how). However, to solve this particular problem, I wanted the students to be able to add the "evidence" for the scores they were giving. In order to do that, I set up a form that had multiple choice items, page breaks and "go to page based on answers" functionality, requiring students to provide evidence for the scores they were giving.

Satisfied with what I had created, I patted myself on the back and submitted a trial run. I then opened the form responses, added a formula that would add the score, and formatted the columns so the comments/evidence would be easier to read and thought I was brilliant. Oh, how wrong I was. I submitted my second trial, only to figure out that my formula, which I had painstakingly copied over and over in my results and the formatting was "ignored" as a new form came in!

So now, what? I knew that I would not be the only one with this problem, so I dedicated an afternoon to figure it out. As I immersed myself in this, I came across this array tutorial by Ad:AM, solving the first part of my problem: being able to apply a formula (adding the individual scores), to a form.

The formula that I applied to my spreadsheet is:
=arrayformula(IF(ROW(A:A)=1,"Overall Score",IF(LEN(A:A),(D:D + F:F + H:H + J:J+ L:L+N:N+P:P),)))
where D-P are the cells where the response in the score. I could not use a simple =SUM because the columns were not adjacent.

With that problem solved, I still needed a way to keep the formatting. Although it is hard to see in the previous image, you may have noticed that the paragraph responses where the students are providing evidence do not wrap, making the "evidence" the students are providing almost unreadable. Once again, through a Google search and the generosity of strangers who have come across the same issue, I found this silent tutorial on how to solve the problem, using =QUERY('Form Responses 1'!A:Q).

With the "problems" solved, I went back to the classroom and had my students each create their own copies of the three rubrics/spreadsheets I wanted them to use:

Evidence Based Essay Rubric
Evidence Based Project Rubric
Evidence Based CITE-IT Rubric - used to evaluate websites

In all three, I have hidden the "Form Responses" page, and when the students make a copy, it remains hidden. To view it in case you want to modify any of it before sharing with your students you just need to click View>Hidden sheets.

Once each student had made their own copy, I asked them to share it with me so I could have access to the responses. However, when having the students peer review, this is not necessary, they just need to send the form to the reviewer.

As a final step, I also taught them to create filtered views. My students use these to create filters that correspond to the websites, projects or essays that they evaluated, making it easy to share and have discussions about just one piece of work without having the rest of the information showing. The filtered views also have unique URL's, allowing for three-way discussion with other students or even parents without displaying everyone's input in the forms.

Have you found other ways to use Google Forms? I would love to hear from you.

Avoiding death by presentation.

It is project presentation day! Your students are excited (and anxious) about presenting their work. They had lots of choices to demonstrate what they learned, each team has a different topic or solution to the problem. They have also used different tools to create amazing presentations. Slide decks using Powerpoint, Google presentations, Prezi, E-maze or even Piktochart abound. Everything is going swell, until students start presenting.

That is when you again realize that it does not matter that everyone is presenting something different or that the tool chosen has lots of bells and whistles. Students, and many adults, still rely on text heavy slide decks, and more often than not, they "present" by reading each slide out-loud. By the third presentation, and even though you have stated several times, "I can read your slide, turn around and tell us about your work", you are ready to pull your hair out. Out of the corner of your eye you see Juanita doodling and Johnny dozing off. The class is bored out of their minds. Something has to change!

Now you may already be thinking about authentic audiences, but the same thing happens when students are presenting to the community at large, and even in professional settings. And yes, I know that presentation skills need to be taught and students need to practice beforehand. We have had complete lessons on what makes a good presentation and critiqued posted presentations from around the web. But even then, the reliance on reading text-heavy slide decks is still an issue.

As I searched for an answer, I came across the idea of using an Ignite presentation format. The Ignite presentation is a 5 minutes long presentation with 20 slides where the slides advance automatically every 15 seconds. You can think of it as the presentation equivalent of a sonnet.

The idea is simple, but putting it into practice will require some prep and teaching on my part. This is the plan:

1. Introduce the idea of Ignite presentations. Share Scott Berkun's - "Why and How to Give an Ignite Talk".

2. Provide students with an Ignite presentation planner. This document becomes the presentation outline.

3. Based on the planner, students can create a slide index (on paper or a Google doc). This is basically a "what will go in each of the presentation slides". Students then practice with this slide index in hand to figure out what to say and what to include as visuals for that slide.

4. Have students choose a slide deck creator, and draft their visual presentation. Remind them of the "20/15" rule. 

• Google slides: Create the 20 slide deck. Publish  to the web, and select auto-advance every 15 seconds. The link created is what they submit to be played on presentation day.
• Prezi: Prezi does not offer the 15 second option in auto-play, so students will need to get a little more creative. For example it could be 15 slides every 20 seconds or 30 slides every 10 seconds. 
• Emaze: Apply the 15 second stop duration in slides options to the complete 20 slides presentation.

The key idea in this step is that no matter what tool they use, they will be presenting using the automatic changing of slides. It should almost be a choreographed dance between the slide deck and the presenters. Practice is key!

This is what our first attempt looks like:

5. During presentation day, I will continue to use my peer-presentation rubric, which I have transformed into a Google form. Feel free to create your own copy from this presentation rubric response form  (If you are unsure of how, read my previous post Evaluating websites using Google forms).

So, that's it. What do you think?
What other ways have you come up with to avoid death by presentation? I would love to hear your thoughts.