Having to shuffle through multiple spreadsheets to check everyone's answers? Boo!
Getting lost when students submit multiple times? More boo!
After a lot of trial and error and Google searches, I created a workflow that aggregates multiple Google Forms quizzes onto one sheet and only displays the latest entry per student. The idea is for this to be as out of the box as possible, with no requirements for teachers to have to import rosters or do extra steps after the initial set up. Enjoy the fruits of my labor!
STEP 1: Make your google forms quiz
In my example, I named them Quiz 1, Quiz 2, and Quiz 3.
STEP 2: Filter the latest entry per student
Open the Form Response spreadsheet for one of your quizzes. In the first blank column, copy and paste this formula. You'll want to customize the column letter to match the column that can most easily detect duplicates. In my example, it's the username, and thus column B.
Duplicates will return a value of zero, while the latest entry from each user will return a value of one.
Next, create a new tabbed sheet within each of the Responses spreadsheets (click on + in bottom left hand corner). In my example, I named them SortedResponses.
Copy and paste this code into cell A1 of your new, sorted sheet. Customize with your sheet names and column letters where needed. Column E is where I added the formula above, resulting in either a zero or a one.
=query('Form Responses'!A:E,"select A,B,C,D where E=1")
STEP 3: Create your aggregated spreadsheet
How do I find my spreadsheet key?
Look in the URL of the Responses spreadsheet. In the middle, you'll see ...key=a whole bunch of numbers&usp.... The numbers in between the = and the & is the key.
(optional) Step 4: Pull this to one sheet
Create a new tabbed sheet in your aggregated spreadsheet, in my example it is called AllResults. Since there is no imported roster to match up the different quiz results, I collect usernames from the first quiz and then match subsequent quizzes.
To collect submitted usernames from Quiz 1, simply reference the username cell in A2.
Then pull the formula down to populate the rest of the column.
To populate the rest of the row, use a VLOOKUP function to reference the quiz results associated with that username on each of the tabbed sheets. Use the formula below, customizing sheet names and ranges as necessary.
- Where A2 is the cell in which you've referenced the username (see above)
- There are dollar signs ($) in between the column letter and row number to tell the computer to make these locations unchanging
- Which column from the referenced sheet you want pasted. Here the second column on my tabbed sheet will appear.
- Ignore the zero.
Your spreadsheet is ready to go! There's always the option for adding a conditional formatting bonus, as well!!