Combining multiple Google Forms assessments using Google Sheets

A fellow Google Certified Trainer asked the following:

“A teacher I am working with has created module tests in Google Forms. The forms are all linked to one Google Sheet which creates a different tab for each module test. She wants to be able to track progress for everyone on one tab. Ideally, she would like to be able to see how many module tests each student has completed and what their scores were to see a summary of each student’s progress without having to look in each tab (i.e. Johnny: module 1 75%, module 2 90%, etc). Is there a way to do this?”

Formulas you will need

For this solution (get your copy of the Spreadsheet here) I utilized the formulas ARRAYFORMULA(), if(), Query(), Sort() and Unique(). The use of {;} was used to combine the data from the multiple module tabs.

Steps to creating this solution

First of all I made two fake forms that feed into two module tabs. Each uses an if() statement wrapped by an arrayformula() [ =ARRAYFORMULA(if(A2:A<>””,1,””) for module 1 and =ARRAYFORMULA(if(A2:A<>””,2,””) for module 2 ] to automatically check if column A has a value, and if it does, place the module number in that column (I figured the assessment may not automatically say what module it is from).

Module 1 Tab
Module 2 Tab

The on a new Tab I use a combining statement { array1;array2} wrapped by a unique() statement and sort() statement to create a sorted list of the auto-collected email addresses which will allow you to sort by student in a later step. The formula used is =sort(unique({Module1!B2:B;Module2!B2:B})).

Combined student email list

In the 4th Tab I combined the two queries together using the statement {query(Module1!A2:G,”Select A,B,F,G”,0);query(Module2!A2:G,”Select A,B, E, F “,0)}. Additional modules would be added by insert insert ” ;array3″. You must be careful to make sure that the queries pull in the same values. I have pulled Timestamp, Email, Score and Module, and these are not necessarily in the same columns on the different assessment tabs. I also did not include the headers in the queries, they were manually inserted.

Combining Modules into one list

Suggestion: At this point I would suggest using my Google Sheet add-on “Student Name Import” which uses your Google Classroom People and inserts a new column with the students name. Great for schools that use the “” style email address. Click here to learn more.

The last step is to create a query page with a drop down menu. First, use Data Validation to make a drop down list of the students emails (or you could use the names if you utilize Student Name Import). Next, create your header row that will be above your query. Finally, use a query to pull the information from the combined report, but restrict the list to the selected student. The formula use in this solution is =query(‘Combined Modules’!A4:D, “Select A, B, C, D where B = ‘”&B1&”‘ “,0) which pulls Columns A, B, C and D only where B is equal to the value in the drop-down list. Typically you might use a query with “where B = ‘’ “, the trick is to replace ‘’ with ‘”&B1&”‘ (B1 represent the location for the drop-down list) allowing you to create a single query that isolates based on the student.

Google Sheets is amazingly powerful. I use these techniques for many of the resources on my website. Takes a little time to learn them, but you will truly benefit by learning these skills.

Leave a Reply

Your email address will not be published.

Name *
Email *