Working with the spreadsheet data

Welcome back!

Last time, I went over the steps to create the form, and link the responses to a Google Sheet. Now that we've got a spreadsheet linked to the form, responses will go directly into the sheet. Another feature that we'll take advantage of is the option to run a script every time there is a form submission - but I'm getting ahead of myself.

To begin, open up the spreadsheet that you linked to the form. You should end up with a tab at the bottom for "Form Submissions." This is where every submission will show up whenever your form is filled out and submitted. This is good, but we can't play too much with the layout of this sheet, because it's determined by the content of your form.

However, we can add new sheets to the spreadsheet, and manipulate the data there to do what we want. This has two advantages: 1) it allows you to customize how the spreadsheet will look, and where the data will go, and 2) it leaves the form submissions alone, so you know that every time the form is submitted, the data will end up in a new row, but it will follow exactly the same format. This is important, because we need to know the format so we can structure our data properly.

Step 1:

Go back to your Google Form, and click on "View Live Form." You're now going to complete the form to establish an answer key. Whenever I do this, I put in "Answer Key" as the name, and I do it before I publish the form live, so that the answer key is always the first row in the responses. This makes comparison easier.

Go through, and make sure every entry is correct, then click on the submit button. Once you've done that, switch to the linked spreadsheet, and take a look at how your data appears in the sheet.

Step 2:

Look at how the data is structured. You've got a header row that shows the timestamp, and the response that was recorded.

You're going to follow this structure in the other sheets, so that working with the data will be as easy as possible. It comes with a pre-made name (usually something like "Form Responses 1." I chose to rename it "entry."

You'll need to add another sheet. In the bottom left of your browser window, there is a small + symbol. Click on it to add a new sheet. I chose to name this sheet "Results." You can choose whatever you like, but for simplicity's sake, I'm going to refer to each sheet by name. Here is a list of each sheet (you can add each of these right now, if you're following along):

• entry: This sheet is where response destinations show up. The script I wrote refers to this sheet by name, so if you want to avoid modifying the script, use the same name.
• results: This sheet gives information about each student's responses. It filters correct responses, so you can easily see where student understanding needs to be improved.
• grades: This sheet is used to calculate the average grade for each set of responses. The final grade shows up in the results sheet, so once this has been set up, you can just leave it alone.
• key: This sheet gives the answer key, and the weighting for each question. I've given each question a weight of 1, but you can change this to affect how important each concept is.
• sorted results: This sheet sorts the results into alphabetical order by last name. Useful for entering information into your grade book.

Next time,

I will talk about the spreadsheet commands to use to manage your data. There is good help information available online, but it doesn't cover the specific application. I will talk about the commands as they apply to this project, and I encourage you to check out any other commands that you think might be useful.

Creating the Self-Assessing Review Activity

Image: Flickr user redteam

I posted about my attempt to create and implement a review where students can go over material before an exam, and will instantly get feedback through email about their results. In this post, I will explain in more detail the purpose and the procedure for this activity.

Purpose

In order to help students do well on the exam, I intend to spend some class time reviewing material. I want to be sure that this time is well spent, and getting some information about what content to target in the review will help me increase student understanding. Thus, the review activity has two main objectives:

1. Help students get an idea of what they can expect on the exam. Both in the kinds of questions that will be asked, and in how their knowledge stacks up against what I expect them to know.
2. Help me as a teacher target my review time to focus on those topics that many students are struggling with.

I will achieve these goals by:

1. Providing students with example questions, and giving them instant feedback of their results.
2. Looking at overall results for each individual question, and targeting those concepts where student understanding is not as complete as I would hope.

Procedure

Create a form.

I will not go into full detail in this post. But don't worry: I will explain all the steps I used in the next few posts to my blog. I will, however, post enough to get you started on creating your own review. Begin by creating a Google Form. To do this, you will need a Google account. If you're completely new to the idea, a Google search on how to create a form will prove very useful. Enter questions that you would like students to answer. I used a multiple-choice format, but you can use any type of question that has pre-determined responses. By that, I mean that students will select from answers already given. While it is possible to have a Google Form automatically assess all types of responses, it becomes far more complex as you give students more liberty in their choice of response. For example, if a student types an answer, but makes a spelling mistake or forgets to capitalize a word, it will be evaluated by the form as being incorrect.

Link the form to a spreadsheet.

On your form, clink on the menu item Responses - Choose Response Destination. Create a new spreadsheet to collect response information. In this spreadsheet, every time the form is filled out, the response time and all answers will appear in the sheet. This will be useful, because answers will follow the same layout every time, and each time an answer is entered, the linked spreadsheet will be modified to add the new data.

Next:

In a future post, I will be explaining how to manage the data once it appears in your spreadsheet. There are a few functions I used in order to manage the data to suit my purposes. They are:

• Filter. This command compares entries to a specific cell in your spreadsheet. I set it up to compare all entries to those in the first set of responses (which will be the answer key). Filter can either return the cell's value, or leave it blank. I chose to use the filter to eliminate correct responses, but show incorrect responses. This will help me see where students are making the same mistakes as a group, and where to direct my review.
• If. Anyone familiar with programming will recognize this function. It chooses between two options, based on some criteria. In my case, I used it to check the contents of the filtered responses. Empty cells are given a numeric value (equal to the weighting of the question), and cells that are not empty are given a zero.
• Average. This finds the average of a range of cells. Useful for calculating a numeric grade.
• Sort. I used this to sort entries in alphabetical order. Keeps things lined up with my class lists. Very neat and tidy.

Hope you're learning from all this. I know I am!

Self-Grading Review Activity with Google Forms

Image: http://fc09.deviantart.net/fs71/i/2011/227/f/a
/chemistry_exam_by_joouchiyanmon-d46m4ke.jpg

As the end of the year approaches, I want to help my students do well on their final exams. I know they've probably forgotten some of what they've learned, and some time spent on an in-class review will no doubt be helpful to them.

However, I want to avoid wasting their time by covering concepts they mostly understand. Instead, I'd like to target those concepts that are important, but where student understanding appears to need some reinforcing.

This long weekend, I decided to try out something new. Instead of asking them what they'd like to review, or going over their results and targeting the review that way, I wanted to experiment with some of the new tools that are a part of the Google Apps suite.

So I began by creating a Google Form that included several multiple-choice questions. They are taken from the students' tests, and I've added a few new ones as well. This will allow students to go over the questions as many times as they'd like, and give them a good idea what they can expect on the exam.

BUT

What good is a review without feedback? I can collect data that will show me where the students will benefit from review. Great! But without any motivation to the students, I suspect that it may be unlikely that many of them would bother using the review tool.

So I thought "What if I could somehow tabulate their results, and send them an email?" This would give instant feedback, and help students determine how they can expect to do on the exam. Not only would the tool become a valuable study aid, but it will help me guide the review activities in class.

So I spend much of the weekend tinkering. Trying out scripts. Struggling with the FILTER function in my spreadsheet. Finding clunky work-arounds. Tearing

Lo and behold, it works! Hooray! Okay, well, sometimes it works and sometimes it doesn't. I'm still working the bugs out.

Image: OpenClipArt.org

I completed the form for one of the units that will be on the test. The other units will come later. And the best part? I only have to complete the form once (the first time, before it goes live), and choose the correct answers. This sets up the answer key, and the validation of all future answers in the form. Students can complete the review as many times as they would like, and get their results each time.

Some things I didn't quite figure out:

• I'm not yet sure how to require Google sign-in for the form. That's not an issue (yet) because students aren't required to have a Google account. In the future, when all students will be using Google accounts, this is a feature I'd like to add.
• Why my filter function and my if function wouldn't cooperate. I ended up using two sheets just to compare students answers to answer key answers, and then to assign a mark to each question. Not an elegant solution at all, but at least it works.

If you'd like to do something similar, there's a great page that even gives you a template. I decided to try to do mine from scratch, mostly because I really want to know how it works. If you're more interested in having it work quickly, just grab the template. Trust me, you'll save a lot of time and frustration. But, for me personally, the frustration is worth it, because the satisfaction of getting it to work, and knowing why it works, just really can't be beat!

Enjoy!
James

Technology in Education

There is a gap that exists between the technology that will allow teachers to go further with their students, and the majority of teachers who have not yet implemented them. I have put together a 5 minute video detailing my understanding and experience of the issue.

Please do not misunderstand - this has no official sanction, and I have not done this for any organization. I am merely presenting my own opinion on the issue, and some findings from research I have performed.

The way I see the issue is that we need to get teachers to understand the benefits of integration of technology. Once they see how their work will change, how they will be better able to reach each student, teachers will happily adopt new technology. Training needs to focus as much on application of technology as it does on use of technology.

I hope you enjoy the video!

Get yourself a sneak-preview of the ChromeBook experience

As a tech junkie, I am very curious about all the tools at my disposal, especially those that I may have a chance to use professionally. ChromeBooks - Google's affordable hardware and operating system platform - are appealing to me on a cost/benefit level, but I can't speak personally about the user experience.

Yet.

I plan to change that by taking ChromiumOS for a test drive. There are some good tutorials out there going over the steps you need to take, so I won't repeat them here. Click on the link to find one of the many that exist. If you're nervous about installing an operating system, have no fear.

You can install and boot from a USB drive, leaving your hard drive untouched.

That means that if you make a mistake, don't like it, or just want to go back to your original configuration, all you will need to do is shut down, remove the USB drive, then turn on your PC, and all will be back to normal!

An important note: this is an unofficial release of the ChromiumOS. It is similar to what comes installed on ChromeBooks, but with a few restrictions. The operating system is updated daily by Hexxeh - an intern with Google - and lacks a few of the features that are included with the official version. At the time of this writing, Flash, MP3, and PDF were not supported due to licensing restrictions (note that it is no problem for the official ChromiumOS - it is simply a legal issue). However, I believe that the general feel of the operating system should be a worthwhile experiment.

I have downloaded ChromiumOS build 4028 from http://chromeos.hexxeh.net/. Depending on the build you download (a new one is released each day), your experience may vary.