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.
This will take you to the code for the FilterRoster script. You will need to COPY the code. (Try Control A to select all).
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.
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.
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 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.
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.
You can close the tab with the script and go back to the spreadsheet.
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.
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.
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.
Check out my other scripts I wrote:
17 thoughts on “Google Forms: Streamline your Data with FilterRoster Script”
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.
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.
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?
There is an Add-On to remove extra rows and columns. Suggest to do that before running the script…
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!!
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.
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?
Not without seeing your spreadsheet.
I fixed the problem but world like to be able to put a sum formula in my last column and when I do I receive an error
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.
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.
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!
Change the name of the tab so you can re run the script with new students.
You must log in to post a comment.