Google Forms: Streamline your Data with FilterRoster Script

2015-01-12_17-52-30

If you re-use a Google Form for attendance, turning in homework, daily warm-up, peer evaluation data, or anything where there are multiple rows per student  in your Form data my script FilterRoster may be helpful. It creates a tab for EACH student with a filter to show just that students data. Rather than filtering the Google Form data one student at a time, this will filter ALL of your students at once.

Beta

I have known JavaScript and Google Apps script for about 2 weeks now, I am still playing around with it. This script works, but should be considered in beta. If you would like to try it out please send me feedback so I can make it better. I will be working on making the workflow a little easier also. I will republish the script once it is a little sharper.

The Template

http://alicekeeler.com/filterroster

This will take you to the code for the FilterRoster script. You will need to COPY the code. (Try Control A to select all).

Google Form

This script should work with a Google Form data that has a unique student identifier such as student ID number or if students have different names (ie: you do not have 2 Amy’s in your class.) Identify a Google Form you want to install this script to.

Spreadsheet

The script actually goes with the spreadsheet data. Your spreadsheet must be NEW Google Sheets. You can tell if your spreadsheet is new Google Sheets by looking in the bottom right hand corner of your spreadsheet. There should be a green circle with a checkmark if it is new Google Sheets.
New Google Sheets

Script Editor

In the spreadsheet with the Google Form data go to the Tools menu and choose “Script editor.”
Tools Script editor

Close out of the splash screen.
Close blank project script

Delete Default Text

The blank script project will have a function started. You want to delete the default text. Try Control A to select all and then choose delete.
Delete my function

Paste Script

Paste the script from http://alicekeeler.com/filterroster into the untitled project. Click on the save icon in the toolbar. You can name the project anything you want.

Run Script

After saving the script you need to run it. Choose the “Run” menu in the toolbar. Run the onOpen function. This will set up the Filter By Student menu in the spreadsheet.
Run on Open

Close Script

You can close the tab with the script and go back to the spreadsheet.

Unique Identifier

From your Google Form data determine which column you will filter by. I usually use Student ID (SID) since they are different for every student. The problem with using student names is that sometimes you have two students in the same class with the same name. Another issue is students do not always spell their name correctly when filling out the Google Form. For this reason I always ask students to provide their student ID number. You will be prompted for what column the unique identifier is in. In my Forms I always ask for SID first so I can usually find the Student ID Number in column B. When asked I simply type in the letter B to let the script know to filter by column B.

Filter By Student

Use the Filter By Student menu in the spreadsheet and choose “Run SetUp.” This will create a new tab for your roster and a tab for the template.
Run SetUp

 

Paste Your Roster

On the roster tab you will want to paste your class roster. Column A should be the unique identifier that you are filtering. If it is the students name you are looking up you can place that in column A instead. The tab name of first initial and last name will be automatically created in Column D.
Paste Student Roster

Run FilterRoster

Go back to the Filter By Student menu and choose “Run FilterRoster.”
Run FIlterRoster

This will create a named tab for each student on the roster. On each sheet will be the students individual data.
2015-01-12_17-52-30

2015-01-12_17-53-11

 

Sample

Try the script out with this sample data. CLICK HERE to make a copy of the spreadsheet. The script is already installed on the spreadsheet so you can simply go to Filter By Student menu and choose to Run SetUp. Copy and Paste the roster into the roster tab. Go back to the Filter By Student menu and choose to Run FilterTab.

Here is the roster that you can copy and paste into the roster tab when it is time. When prompted put B in for the column with the unique identifier.

23927

Debbi

Criss

29203

Vance

Farrier

44428

Cristin

Bovee

48530

Leann

Eisenhower

63976

Noelle

Evens

77374

Neda

Bagdon

79824

Laronda

Blythe

80542

Jeannetta

Brassfield

80683

Pierre

Aultman

83910

Yessenia

Bergevin

95777

Rogelio

Braddock

96662

Bob

Febres

Video Demonstration

Other Scripts

Check out my other scripts I wrote: