Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Forms: Streamline your Data with FilterRoster Script

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

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.
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 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.
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

YouTube video

Other Scripts

Check out my other scripts I wrote:

15 thoughts on “Google Forms: Streamline your Data with FilterRoster Script

  1. Hey, Alice, how did you get started on learning Javascript and Google Apps Script. I realize there are tons of programs out there, but I’m just curious about your own path.

  2. I followed your instructions carefully once and received the message that continuing to run the script would exceed the limit of 200000 cells. I deleted each tab the script had created, watched the video, pausing to complete each step you described, and again received the same error message. My roster contains 54 students and both times I ran the script, it gave me the error message after student 36. My form has 7 columns of data. What do you think I’m doing wrong? I really love this idea as my students have to check their books in online. This script would allow me to see all the books the students have read on one sheet and save me lots of search time.

    1. Oh, I should mention you can’t have too many names. You should have been okay with 54…. I’ll add to the script to trim the page to the size of the data.

      1. I ran into the same problem when trying to run the script today. Granted, I have a large number of students, so I was thinking I would need to find a way to just divide them into hours. There should be no limit no matter what?

  3. Alice,

    Thanks for the great video and walkthrough on this. I have kind of a specific issue that I would love your thoughts on if possible. I am using flipped learning principles to teach adult learners in my industry. All quizzes and evaluations are done via google forms. Some of those I work with have purchased LMS systems to track student info and quizzes etc… Most can’t afford this option, and they really like using google forms. However, the one drawback so far is the ability to track student assignments, and to easily notify a supervisor or run a “Report” that shows who has not completed their work. We aren’t like a school teacher where we monitor daily, our members may have 2-3 weeks to turn things in in most cases. To this point the only solution has been to manually sort and go through each sheet to see who is missing and didn’t turn in assignments.

    I am looking for the best method of tracking student assignments and also automating the process of creating reports and emails to get this information to the correct person to follow up.

    It seems your script that pulls form data into individual sheets can be part of the process, but I just can’t seem to get an entire workflow together for this back end reporting that can rival some of the LMS options.

    Any guidance you could give would be awesome! Great site!!

    Frank Lipski

    1. Some formulas and a script could definitely be created for this. Unfortunately it is a very specific use case and in order to help I’d have to see your system. What I use to do when students submitted work via a Google Form is have a hidden question on a page that was skipped in the Google Form that I could code the assignment. After grading I marked it with an X or other code. Then I could filter and run formulas based on the code.

  4. I love this thought. I am trying to keep up with drama points per student. I created a google form and used your script. I am good until I check the students sheet then it only list their name on their tab and a copy of my headers but not the students data. Can you help with what I did wrong?

        1. Glad you got it fixed. I am sure it is possible but yours is a very specific use case. You will have to examine the formulas I already have and adapt the spreadsheet to suit your needs. Be careful not to break the formulas I already have.

  5. I love this and have it working great. I have a question though I need to have my last column being added to the previous column and have a running total. Every time I try to add a sum it gives me and error in the timestamp column and everything disappears. As soon as I remove the formula everything reappears. Any help would be greatly appreciated.

  6. Thanks for this amazing script! I am helping teachers use this tool. After setting up and running the script, they realized a few students didn’t take the assessment yet. Is there a way to add them, or do we have to delete it all and run the filter again? Thanks!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.