Google Forms – Match Responses to OU Membership

google-forms

We have some forms that we send out Domain wide, and require the user to login with their Google credentials. I'd like to collect the responses and display OU membership of the user, so we can determine which users in which OU's have not responded.

It's more of a way to see who has acknowledged that they have received a policy, instead of sending out a truckload of papers. However, keeping track of who responded is a bit of a manual process since I have to sort based on email address, then match up each OU.

Best Answer

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

  1. Create a spreadsheet
  2. Add an additional sheet
  3. Rename the sheets as follow:
    • Responses
    • Roster

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.

Related Topic