Collate Google Form responses into Google Docs

code × January 16, 2018

For Ela Conf 2016 and 2017, we created a Google Form to collect speaker feedback. The form had the following fields:

We then read through the responses, summarized them into a personal report for each speaker, and then sent the feedback to each speaker after the conference.

Reading through a spreadsheet with 290 rows with feedback for 20 sessions wasn’t going to be fun, so I wrote spreadsheet-collator that would:

  1. Read the spreadsheet.
  2. Group the data based on the talk title.
  3. Format the data.
  4. Write the data to its own Google Doc file.

The script worked beautifully and saved us loads of time. Here’s how you can use it:

Create your form

Create your form in Google Sheets. (For this example, I made a vegetable feedback form.)

Screenshot of a Google Form

Once you create the form, click the “Responses” tab and then the Google Sheets icon to create a Google spreadsheet to store your data.

While you have your spreadsheet open, you’ll want to jot down a few credentials:

  1. The spreadsheet ID, which is that string of characters in the URL: Screenshot of Google spreadsheet ID
  2. The spreadsheet range, which is the tab name and cell ranges you want to read. For this example we want Vegetable Feedback!B:D: Screenshot of Google spreadsheet range
  3. The name of the column header that holds the rows that will be the titles of your Google docs: Screenshot of Google spreadsheet header row

Create a Google Drive folder

Create a folder in Google Drive that will ultimately be the home for the docs. You can also store your form and form response spreadsheet here.

Screenshot of a Google Drive folder

While you’re here, you also want to jot down the folder’s ID, which is that string of characters in the URL:

Screenshot of Google Drive Folder ID

Configure the collator

After you’ve cloned the spreadsheet-collator repo, open up config.js, and update the config with the values you collected above.

Run the collator

Following the steps in the README, you’ll need to first authenticate your account so that script can access your spreadsheet and create files. Then you can run the collator.

Screenshot of Terminal

Open up your Google Drive folder to see your freshly created documents:

Screenshot of Google Drive folder with docs

And then open up a doc to see your collated data:

Screenshot of Google doc

Customize the collator

This script is great for sorting comments and with a little editing it can process other types of data.

Try editing this section to change the output the your docs. For example, you can add additional text or calculate and then display the average for a quantitative question.

Happy collating!

Create emails from a Google spreadsheet GitHub as a talk proposal review system