In a previous post we described how to setup your FormAssembly form to pass data into a Google Spreadsheet. For this post, we’d like to show you how you can use Google Spreadsheet as a powerful reporting tool.
Note: Google Spreadsheet integration is available on the Professional Plan only.
“Little Pet Shop” Example:
To illustrate this post, let’s take a sample form, “My Little Pet Shop Survey”. If you already have a FormAssembly account, you can click here to open the template in the Form Builder and click ‘save’ to save your copy.
In this form, respondents can choose the type (dog or cat) and price they’d pay to adopt that animal.
With the connector enabled (see instructions here), we can submit some responses and see the results in the Google spreadsheet:
Customizing Column Headers
Our data is in the worksheet, but some of the column headings are a bit verbose. Since the FormAssembly connector is smart enough to only append new rows with new responses, we can go back and edit the column names to something more readable:
Using Worksheets and Formulas
With the headers updated, the worksheet looks a bit better, but we’re not really interested in all this raw data. We can get a more useful report by adding a new worksheet to the document and using formulas and the built-in analytic tools to process our data.
For instance, we can calculate the number of each type of animal ordered, the average offer per type, or the average offer per pet, using standard spreadsheet formulas:
Number of Dogs =COUNTIF('Revision: 2'!D:D,"Dog") Number of Cats =COUNTIF('Revision: 2'!D:D,"Cat") Ave Dog Offer =SUMIF('Revision: 2'!D:D,"Dog",'Revision: 2'!E:E)/COUNTIF('Revision: 2'!D:D,"Dog") Ave Cat Offer =SUMIF('Revision: 2'!D:D,"Cat",'Revision: 2'!E:E)/COUNTIF('Revision: 2'!D:D,"Cat") Average Offer =AVERAGE('Revision: 2'!E:E)
Two points to note in the above formulas:
- Reference other worksheet cell ranges within a spreadsheet by using the syntax: ‘WorksheetName’!Range .
- In this case we used a column range reference (D:D) to run our formula over the entire column range. The nice thing about this syntax is that as new responses are received, your formulas will automatically be updated with the new data.
Creating Graphs and Charts
Now that we’ve created some interesting analysis fields, let’s make a graph that we can share with other people.
Highlight the fields “Number of Dogs”,”Number of Cats”, and their results and insert a pie chart. You should see something like this:
This is pretty cool, but to see why Google spreadsheet is great for this, tab over to your form (while leaving the spreadsheet open) and submit a new response. By the time you tab back to your Google spreadsheet, it should have updated with the new response.
Even better, Google Docs allows you to embed your charts in any web page. For this example, we added it to the form itself. You could also embed it in a ‘thank-you’ page, or in a separate report that you share with your colleagues or clients.
Please note: Once a form is revised and saved, the next submission will start a new tab at the bottom of your spreadsheet.
We hope you find many fun uses for this new feature and if you’d like to share those with us, please leave a comment, or let us know via Twitter.