Using Google Spreadsheet Form Tool for Free Online Surveys

// mentioned in my earlier post, I created an online survey using the new form tool in Google Documents asking the questions “where should I move” and “where do you think I will move?”

In Google Documents, create a new spreadsheet, click share, and you will see a window that looks like the one on the left. Select “to fill out a form” and you will then be able to create your own survey.

Google Documents allows you to invite people to fill out the form via email, get a public URL that you can share online or on mailing lists, or even implement with an iframe on your website (however, iframe implementation does not work on blogs like this one, which is why I can’t embed it for you as an example). It is important to note that anybody can fill out the form, not just those with Google accounts, and there is no need for anyone to sign in to anything.

Using a survey site such as Survey Monkey costs money to create a survey for more than 100 responses (Google Spreadsheets allow 10,000 responses) and limits the number of questions you may ask, while Google Documents is completely free. While the Google Forms tool does not currently have certain questions types available that Survey Monkey does (specifically multiple choice matrices), pretty much every question you would want to ask is supported. The only real negatives are that you currently are unable to require fields and that there is no way to prevent someone from voting multiple times.

Responses to your survey are populated live into your password-protected spreadsheet, and this enables dynamic data visualization and the creation of Google Gadgets.

Here are the results of my survey. I would like to point out that the graphic below is dynamic and will continue to update as new responses come in.

I chose the question I did for two reasons. First, it enabled me to show a data comparison of a single variable in two questions. The graph first shows how many people voted that I should go to a city, and second shows how many people voted for where they think I will go. Second, by allowing a text input field for “other” it forced me to create a spreadsheet that could interpret dynamic text responses.

You can view my spreadsheet and visualization here. If you would like to use the spreadsheet as a template, go to file->copy spreadsheet. This will add a copy to your Google Documents so you can examine the formulas and structure.

One setback for using this tool is that it is necessary to have familiarity with spreadsheet formulas and logic to interpret your results. Here is how I programmed my results page:

The response data is propagated live onto Sheet 1 of the spreadsheet. I added a second sheet to interpret the data. My first need was to automatically count the number of votes each response received. I did this using the COUNTIF formula. Here is the example: =COUNTIF(Sheet1!B:B,”Oklahoma City, OK”). COUNTIF gives a numerical result for the number of times something occurs. In this case, it is when the field contains “Oklahoma City, OK.” I repeated this for each answer choice for both questions (though for the second question it searched Sheet1!C:C).

Calculating the other responses was a little more difficult. Since all other responses will contain different text, there is no way to use COUNTIF, so I had to hack a little formula together to get the desired result. Here it is: =COUNTA(Sheet1!B:B)-SUM(B2:B10)-1. Not very elegant, but it does the trick. The COUNTA function counts the number of fields that have something in them. This gives me the total number of responses plus the header. I then SUM the results on my second sheet of all the votes given for answer choices and subtract the result from the total responses. This gives me the number of responses that are not one of the provided answer choices plus the header. The -1 at the end accounts for the header and leaves me with the total number of votes for other places.

These formulas allowed me to create a dynamic sheet that I could use to create charts and Google gadgets that will update as new responses come in.

So go take the Google Forms tool for a spin and see how your organization can take advantage of this powerful and free tool.

If you have any questions, ideas, or have used Google forms and want to share your experience, please leave a comment.