How do I receive the data from Google Form onto Google sheets and allow my students to edit it without other students seeing their work

Question

I am trying to feed data from a Google Form to a Google Sheet. I want to have it separate each student into their sheet. Then, I want to send that information back to the student in a Google Sheet so they can edit their work.

From what I understand, I can make A QUERY for each student on their own tab, but they will not be able to edit the tab. So I researched and found out that I also need to IMPORTRANGE in order to share the Sheet so they can edit it. EXAMPLE: I have tried this:

=Query(Importrange("1w7lTxbg933A36rk0mNdqzaJn7KHAzXrvqekVXhh_BKM","Class January Work Log!A2:k"), "Select * where B = 'student's email address'")

My error message is this:

Unable to parse query string for Function QUERY parameter 2: NO_COLUMN: B

How do I receive the data from Google Forms onto Google Sheets and allow my students to edit it without other students seeing their work?

Answer 1

You may want to try AutoCrat. It lets you "merge" data received in a form response to cells in a spreadsheet. Each spreadsheet is created as a copy of a template spreadsheet that contains <<tags>> to show where to put each piece of data. The generated spreadsheets can be automatically shared and a link emailed to the person whose email address is in the form response.

To answer your query() question, try this:

=query( 
  importrange("1w7lTxbg933A36rk0mNdqzaJn7KHAzXrvqekVXhh_BKM", "Class January Work Log!A2:K"), 
  "where Col2 = '[email protected]' ", 
  0 
)