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

https://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.

Script Editor

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

Close out of the splash screen.

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.

Paste Script

Paste the script from https://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.

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.

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.

Run FilterRoster

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

This will create a named tab for each student on the roster. On each sheet will be the students individual data.

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