Maybe this has changed in new Google Sheets because in the second and subsequent Forms it seems possible to select Responses, Choose response destination
:
New sheet in an existing spreadsheet...
, Choose:
find the Response Sheet for the first Form, Select and for this to work.
Overview
The built-in response summary of Google Forms is not customizable, but you could set your form to send responses to a spreadsheet and use it to do assist you to count the responses by organizational units. You could create a "master" spreadsheet to use as template to do the follow up of each Google Form.
Example of the "master" spreadsheet setup
- Create a spreadsheet
- Add an additional sheet
- Rename the sheets as follow:
Responses
This sheet will be used as the destination for the responses of one form.
Roster
Use this sheet to hold the list of email addresses and the OU that each of them belongs to. This sheet also will be used to check if each email address sent at least one response. Columns:
- Column A: email addresses
- Column B: organizational unit
- Column C: response
In the response column use the following formula
=ISERROR(MATCH(A2,Responses!B:B,0))<>NA())
Report
Insert a pivot table using the range of data in the Roster sheet.
1. Click the Data menu, then Pivot Table.
2. Write Roster!A:C, then click in the OK button
A third sheet will be created showing a blank pivot table and its settings panel. In the settings panel set the following:
Rows: Add the Organizational Unit field.
Columns: Add the Response field.
Values: Add the email address field. Summarize by COUNTA.
Rename the sheet as Report.
Next steps
Now the spreadsheet is ready as a "master" spreadsheet. You could use it to create a copy of for each of form that you require to do a follow up by organizational unit. After that, set Responses sheet as the destination of the responses of the corresponding form.
Best Answer
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
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.