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 one 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.
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.
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.
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.
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.
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 is the only conference of its kind in the region specifically designed for IT pros in education.
1020 New Holland Avenue, Lancaster, PA 17601