Collate Google Form responses into Google Docs

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

• For which session are you leaving feedback? (A select box with all the possible talks)
• What did you learn?
• What did you find confusing?
• What’s one thing the speaker(s) can improve their talk for next time?

We then read through the responses, summarized them into a personal repository 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.)

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:

• The spreadsheet ID, which is that string of characters in the URL:

• The spreadsheet range, which is the tab name and cell ranges you want to read. For this example we want Vegetable Feedback!B:D:

• The name of the column header that holds the rows that will be the titles of your Google docs:

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.

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

Configure the collator

After you’ve cloned the spreadsheet-collator repository, 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.

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

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

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 text or calculate and then display the average for a quantitative question.

Happy collating!