Loading...

Tech Talk Live Blog

Using Google Forms and Sheets to Collect Questions

Greg Hess


Google has many useful tools available in Drive, including the Forms App.  Most of the time we use Forms to collect responses to a survey, then several days later we save them to an Excel file for further use or analysis.  What if we want to collect and display audience questions on the fly?

We recently needed a tool to do just that for a state-wide video conference.  Along with receiving questions as we went, we also wanted to provide the ability to ask questions ahead of time.  To further complicate matters, the questions could be in o​ne of several categories, and we wanted to review questions before they were displayed.

First some background: this is for a monthly Data Quality Network video conference that is broadcast to 20+ locations across the state of Pennsylvania.  Several hundred people participate by going to their regional host site.  Each site has a point of contact to facilitate; the job of managing questions rotates among that group from month to month.

The Solution – Collecting Questions and Data

We looked to Google Drive for a solution since first, it is free and second, it is something we were familiar with and were already using.  The questions are collected via a Google Form. We collect some general information including an attendee’s name and email, his/her site location, the question, and the category for the submitted question.  These responses are then recorded in a Google Sheet.

Adding Control Columns

The following control columns are​ added: Ask, Notes, and Answered.  The “yes” or “no” placed in the Ask column will control if the question will be asked.  A “yes” in the answered column will display the question with a green background to show that the question has been answered.  These columns get added to the right of the existing ones; don’t change the order, because Forms may change it back to the original order.

Creating a Sheet for the Formulas

When a new response is recorded, it happens as an insert on the “Form Responses 1” sheet, and formulas are not copied.  To work around this create a new sheet and name it “Query Sheet.”  In cell A1 put the following query formula:

=query(‘Form Responses 1’!A:I)

This formula will return all columns A through I from the “Form Responses 1” sheet.  Adjust the range as needed.  Essentially, it creates a copy of the first sheet which updates any time the first sheet is changed, but it is not subject to the same insert issue so we can use formulas.  Add two columns to the right, one for line number: =row() and another to join the location and the question: =B2 & ” – ” & F2.  Copy the formula in these columns as far as you think you will need.  This sheet can now be hidden.

Until now we were laying the groundwork, now we will build the display page.

Building the Display Page

Add a new tab for the first question category, in my case that is “Special Ed Reporting 101.”  In cell A1 put the following formula:

=query(‘Query Sheet’!A:K, “SELECT J,K,I WHERE E = ‘Special Ed Reporting 101’ AND UPPER(G) = ‘YES'”)

This formula grabs the entire range on the “Query Sheet” and selects certain columns; in this example the line number, question, and answered columns.  It only displays questions for the selected category where the Ask column has “yes.”

Conditional Formatting and Word Wrap

Select columns A through C, right-click, and select “Conditional Formatting.”  Add a custom formula rule for:

=$C:$C = “Yes”

Set the formatting style; this example used the green background.  Select column B and set the text mode to “Wrap” so long questions will be displayed.

Adding Additional Categories

Now that the first category is set up, the sheet can be duplicated for the additional categories.  Remember to edit the formula to match word-for-word with the category options from the Form.

During the video conference Q & A, the sheet for a question category can be displayed and scrolled through so that the audience can also see the questions.  (Note: View – Full Screen and F11 will allow the sheet to be viewed on the entire screen.  Click on Esc to exit Google Sheets full screen mode.)  All the management of the questions takes place on the “Form Responses 1” page.  Ideally, there is another person(s) curating the questions for display, adding any notes, and marking the questions as answered.  As questions are added and updated the display page will automatically refresh.  Questions will always be shown in the order they are entered.

Tech Talk Live Blog Comment Guidelines:

One of our main goals at Tech Talk Live is to build a community. It is our hope that this blog can be a forum for discussion around our content. We see commenting as an integral part of this community. It allows everyone to participate, contribute, connect, and share relevant personal experience that adds value to the conversation. Respect counts. We believe you can disagree without being disagreeable. Please refrain from personal attacks, name calling, libel/defamation, hate speech, discriminatory or obscene/profane language, etc. Comments should keep to the topic at hand, and not be promotional or commercial in nature. Please do not link to personal blog posts, websites, or social media accounts that are irrelevant to the conversation. This is considered self-promotion. We welcome links that help further the conversation and reserve the right to delete those we deem unnecessary. The appearance of external links on this site does not constitute official endorsement on behalf of Tech Talk Live or Lancaster-Lebanon Intermediate Unit 13. You are solely responsible for the content that you post – please use your best judgment. We reserve the right to remove posts that do not follow these guidelines.

Leave a Reply

Your email address will not be published. Required fields are marked *

CONTACT

Tech Talk Live is the only conference of its kind in the region specifically designed for IT pros in education.


techtalklive@iu13.org
1020 New Holland Avenue, Lancaster, PA 17601

(717) 606-1770