Game Changer: Spreadsheet Filters

In our last post, Margaret and I wrote about how much we are loving the feedback we’ve been getting from our students via weekly reflections.   Of course, good things can always get better, and, in this case, we felt that reflections could be improved by a more efficient workflow on our end.

When we started, we made a separate reflection form for each of our classes, with each form sending its responses to a separate spreadsheet, which we then checked on a weekly basis. which sent its responses to a separate spreadsheet.   But, I thought,

Why can’t I have all of the responses from all of my classes go to different pages in ONE spreadsheet.

There’s nothing I love more than efficiency (except maybe a good math pun) and I have a lot of faith in Google Drive, so I assumed this could be done and set to work.  At first, it looked like this was going to be a cake because I saw the New Sheet in an Exisiting Spreadhsheet option in the Choose Response Destination dialog box on Google Forms.

Perfect.  Perfect, that is, until I tried to send the responses from one form to the response spreadsheet for another form and got the following error message:

This spreadsheet is already the response destination for a form.

Tell me something I don’t know, right?  Stumped, I decided it was time to phone a friend, and she told me that this option is meant to let you send form responses to a pre-existing spreadsheet, NOT to let you send two sets of form responses to the same spreadsheet.  Sad, but, luckily, my tail of woe has a happy ending.  She also had a workaround, which involves using a filter in the response spreadsheet.

Here’s how it works:

1)  Created a new tab in the response spreadsheet for each category by which you want to filter responses.  I have a new sheet for each class.

2) In each of the new sheets, type the formula below A1.  (Later, I added and froze the header row to match the one that is automatically generated in the main Form Responses sheet.)

 =filter(‘Form Responses’!range;’Form Responses’!filter column:filter column =”desired value”)

What you have to change:

  • range:  the columns in the Form Responses sheet that you want to copy to the new sheet
  • filter column:  the column in the Form Responses sheet whose value determines which new spreadsheet the entry should be filtered to
  • filter column = “desired value”:  tells the filter what value in the filter column should trigger it being filtered into this particular sheet

I used =filter(‘Form Responses’!A:G;’Form Responses’!B:B=”B”)  in my form because 1) I have responses from the form in columns A:G, 2) want to use column B to filter the responses because that is the column with the letter of the period in it, and 3) this is my period B spreadsheet, so I want to copy entries that have B as the value in column B.

My response spreadsheet with example filter

Result:  one form, one organized response destination.  I love it!

Now that I know how to do it, I am filtering the responses to all of my forms!  It makes using the data so much easier!

Try it.  You’ll like it.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s