Is there a way to have Google forms only show one persons responses in sheets? Need to be confidential

Question

I work at a University running student programming. Students in the program are required to attend events on a regular basis, and submit a response through a form verifying their attendance.

This year we are having students track other information and metrics on a Google sheet. Each student will make a copy of our tracking template, and then share their copy with program staff.

I'd love to have their attendance reports connected to their Google sheet. For confidentiality purposes, I would only want students to see their own individual responses, not the responses of all other students in the program. Is there a way to set up each individual student's sheet so that only their form responses import to the sheet? I can't figure out how to do this without having the entire form connected to a student's sheet.

Answer 1

There is no turnkey solution to that.

You can use importrange() to pull data from the form response spreadsheet, and query() to choose the rows to show. That won't guarantee privacy though, because the spreadsheet where you authorize the import will be able to read any other row as well using a different formula.

Use a separate middleman spreadsheet per each student to ensure privacy. The middleman spreadsheet prevents the  use of importrange() to "peek around" the source spreadsheet that lets a determined user discover all of the original data.

Another perhaps simpler way to send students a copy of their form submissions is to use mail merge. You may want to try AutoCrat instead of writing your own code. For other options, choose Extensions > Add-ons > Get add-ons and search for mail merge.

See importrange() and query().