Google-forms – How to connect different questions in forms to one column in the responses

google-forms

I created a form to take the attendance of my students and created one page for each class and one main page that has all the grades that enables me to move to the other pages. The responses sheet has all the questions in separate columns. What I need is to make e.g. "name question" from the different pages appear in one column in the responses and the grades as well to show in one column.

I hope I explained what I need well.

I created one page with grades list that takes me to different pages with the names of the students and their attendance. When I checked the responses, I found out that all the questions are exported into that sheet. So I have a column for grade 1 and a column for the name and a column for the attendance. And the same thing with the other grades. Is there any way or an add on to take the attendance or any other way to make the answers for the grades to show in one column and the same thing with the other questions.

Best Answer

The form will always record responses to separate questions in separate columns. But you can add a column with a formula that joins them together. For example, if you enter =join(", ", B2:K2) in cell L2 and drag down the column, the column L will have the data from columns B-K, separated by commas for readability.

A more complex example is =B2&" "&C2&": "&join(", ", D2:K2) which turns a row such as

 | 2/7/2016 | Jane |  Doe  |  3  |  1  |  4  |  1  |  5  |  9  |  2  |

into the string Jane Doe: 3, 1, 4, 1, 5, 9, 2.

Filter view

One tool that can be used for this purpose is a filter view. Create a view in which the responses are sorted by column A in reverse order (Z to A). Copy the URL of this view and share it with the users who are supposed to view the response sheet.

A drawback of filter view is that if a new response arrives while a user is looking at the response sheet, the new response is added at the bottom. One would need to reload the page for the sorting to include the new responses.

Another sheet

Create another sheet with the command in cell A1 such as

=sort('Form Responses 1'!A:H, 1, False)

This means sorting by the 1st column, descending order.

Then share that sheet with the viewers. It will update as soon as new data is submitted, without a need for page reload.

Google-forms – How to we NOT record, but on the fly disregard responses not matching an already existing roster

Short answer

Once the response is submitted it's stored and there isn't a way to avoid this. By the other side, it's not possible to delete individual responses on the responses container bounded to the Google Form.

Explanation

At this time Google Forms responses are stored in the Google form file and optionally could be set to automatically send them to a Google spreadsheet by using a built-in feature.

It's worth to say that Google Forms also could be used as a script container and that it have the event on form submit that could be used to do some things with the submitted responses like to discriminate which responses should be sent to a Google spreadsheet, but not for delete individual responses from the responses container bounded to the Google form file.

Remarks

As was already mentioned in a comment to the question by Canadian Luke, relying on students typing their ID in a Google Form and automatically discriminating their responses based on it could cause "problems". One way to minimize this is to send to each student the URL to a pre-filled Google Form with their ID already filled up.

Best Answer

Related Solutions

Google-sheets – Auto sort of Forms responses so that the most recent one is on top