Thursday, May 30, 2013

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.