How to Check if a Student Filled Out a Form

The following is a guest post by Nick Lachen, Physics teacher at AHS.

Hello! The following will create a table to tell you whether a student has completed a google form with filters like the period. You will need the following:

• a full roster with student identifier (e.g., name, email, id #) -I use email
• that student identifier in your google form (can check in google form, see below)
• Full name (if not using already as identifier)
• The period the student is in
• A google form (and sheet response sheet)

Basically, ask for them to insert their first name, last name, period, and email in a google form. You can copy and paste this to use in future forms. (asking their first name and last name separate is okay, you can use the =concatenate(columnthathasfirstname, “ “, columnthathaslastname) function to make combine it into the full name)

The responses from a google form will be put into a google sheets. We will be using a function to identify whose name is on the google sheet, i.e., they completed the form. The function is the following: =if(isna(vlookup(H6,$F$2:$F$61,1,false)),"N","Y")

in other words:

=if(isna(vlookup(MASTER LIST IDENTIFIER,RANGE WHOSE IDENTIFIER IS ON THE FORM,1,false)),"No they didn’t do it","Yes they did do it")

This looks like the following:

The function is checking if the email is present in the area you select, in this case H6. Then it looks in the master list/range to see if if it is in that section, in this case $F$2:$F$61.

If the email is present, then it will output Y. If it is not, it will output N. You can change this to whatever you would like.

Highlight the student’s full name, period, did they do it (y/n) and select the pivot table option under Data. This will create a table in a new tab (located at the bottom of the sheet). Looks like this:

Now, add what you want to see, for example:

• row: Full name
• Column: did it?
• Values: doesn’t actually matter
• filter: period AND did it?

This will look like the following:

This table shows all of the people in all of my classes who did  AND DID NOT fill out the form.

We can edit the filters to show only one period and only who did not do it by clicking on show all items so we arrive at:

This is the list of all of my first period students who DID NOT fill out the form, and in alphabetical order.  This is useful for participation and to how many of a class did/did not look at a form.

Notes: I unchecked show totals in the row and column.

Adding a value to show will just show a zero or the name or something. The list that is there is what is important.

contact nick.lachen@ames.k12.ia.us or patrick.donovan@ames.k12.ia.us for questions