Welcome to EdListen; Podcast for educators.
Never Stop Learning
Please Join the EdListen G+ Community

Using the filter button, =Query(), =Filter() functions for Google Form/Spreadsheet Data

Google form data comes into a spreadsheet nicely, but often it is only a small portion of the data that someone would like view.   Below are some functions and tools that make sifting that data much easier.

Filter Button:

  • The filter button is a very quick way to only show certain fields.  You can apply multiple filters to the data to show only what you want to see.

Filter Formula (  =filter()  ):
  • The filter formula is a more permanent solution to filtering data in a spreadsheet.  The only catch is that it pulls all the columns.   To use the =filter() formula on form data, you should create a new sheet.  Enter the headers into the sheet that match the questions (tip: use =Form Responses 1!A1), then in cell A2 put your =filter() formula.
=filter(<data range to filter>, <criteria>)

=filter('Form Responses 1'!A2:L5042,'Form Responses 1'!B2:B5042="Cat")
=filter('Form Responses 1'!A2:L5042,('Form Responses 1'!B2:B5042="Dog")+('Form Responses 1'!B2:B5042="Rabbit"))

Query Formula (  =query()  ):
  • The query formula works like the filter function, however it allows you to choose only the columns you want to view.  This makes it ideal for creation displays of data that is only relevant to the viewer. The catch is that it uses SQL syntax which is not well documented in Google's documentation, but there is plenty on the web.  Additionally this allows you to filter on a column that is not being viewed, allowing for better formatting for printing or viewing.
=query(<data range to filter>, "Select <Columns> where <criteria>")

=query('Form Responses 1'!1:1007,"select A,D where B = 'Cat'")

EdListen 50: It is good to be back (1:1 update)

We are back.  The Chromebooks have been rolled out to students and we talk about the experience.

We look forward to your feedback and comments, and Never Stop Learning.

Pizza Conference 2014

Panel Discussion: Cloud Computing 

Intro to Google Apps Scripting

An icebreaker for those who are comfortable using Google Apps and would like to take it a step further by taking advantage of a simple scripting tool built into the Google Product. The scripts can enhance workflow, or automate repetitive tasks.

Managing Chrome and Chromebooks

How to centrally manage the Google Chrome Browser and Chromebooks using Group Policies and/or the Google Admin Dashboard. The workshop will cover common configurations for a lab setting or a 1:1 environment.

Announcing the gClassFolders Add-on

I am very proud to announce the gClassFolders add-on.    gClassFolders is designed to improve workflow when using Google Apps in the classroom.  The add-on version offers a much more streamline interface for teachers than previous versions.

There are some school-wide solutions like Hapara Teacher Dashboard that also offer an organizational structure, however they are only available if the school, as a whole, decides to move in that direction.  gClassFolders puts the power in the teachers hand, allowing them to get organized on their own schedule without waiting for an IT department or board approval to get started.  That said, many schools, world wide, have chosen to standardize on gClassFolders for all teachers.

With gClassFolders you can start off the year organized and ready to go, unlike Google Classroom which is set to launch after many schools are already in session.  gClassFolders is also one of the few tools that teaches authentic folder and file management vs going through a separate program that students will not likely see or use in the business world.

To get started you can search for "gClassFolders" (no spaces) from the spreadsheet add-on gallery, or follow this direct link:

I have built the website http://www.gclassfolders.com to help provide information about the add-on.  There is also an active Google Plus Community, which I encourage everyone to join to collaborate with other educators using the structure.

Some new features or improvements you will see in this version of gClassFolders:

  • Streamlined for the teacher.
  • New "Send File" tool to quickly send a copy of a file to selected assignment folders.
    • If you want a more advanced file distribution, gClassFolders continues to integrate with the Doctopus add-on.
  • Fix email, and better tools for managing add/drops.
  • Improved ability to set labels used during the folder creation process.
  • Many user interface improvements.

gClassFolders is free and will continue to be free, however if you found this add-on helpful please consider donating to the project. This will help me continue to support it in the future.