Get Shift Done: Tips and Tricks
Google Forms is an extremely handy application in the Google Drive suite. You can use Forms to collaborate on documents, transfer files to other users, and create web forms that submit the data right into a Google Spreadsheet. But Google Forms are even more powerful when you integrate them with Google Sheets.
For example, you can create a Google Form that automatically saves the data collected into a Google Spreadsheet. The obvious benefit is collecting the results into a single spreadsheet for your own analysis or what-have-you. However, the form/spreadsheet integration can help you create a dynamic chart that updates each time with every new form entry.
Let’s say, for example, we are managing a charity fundraising website that includes a request for the site visitor to make a pledge for a donation. Each sponsor can fill out the fields in the Google Form, pledging the amount of money to donate. So far, so good. However, in addition to a list of sponsors and their contact information, we would like to track how much money has been donated, and what regional location is raising the most — ideally with some sort of visual to show progress.
Start by creating a Google Chart that links to a spreadsheet, as detailed in Create fast forms that dump into the Google Sheet you have open. In this case, we asked contributors to provide contact information, the amount of money they are donating to the nonprofit, and which region they hail from, whether it be from the U.S. East Coast or West Coast.
In this case, we added a form field (and Column) as a drop down for the user to select which region they reside in. When the user selects the region in which he resides, the data is automatically gathered into the spreadsheet. We now want to see the region that is doing the best as far as raising the most money, and to embed that on our website for all to see.
Highlight the columns that include the data to use in the chart:
From the “Insert” menu item, select “Chart:”
When the Chart editor pops up, you can choose the chart type and the options that fit what you’re trying to convey. Change the title, color, and other options in “Customization,” then save the chart by clicking the “Insert” button.
Once your chart is saved, you can insert it into a Google doc.
There’s additional ways to take advantage of this Google Form data. If you want to share the fundraising progress publicly, perhaps on the non-profit’s website, click the little arrow in the top right corner of your chart and select “Publish chart.”
Choose “Embed.” Be sure to select the “Interactive” option, not “Image,” so that the chart updates when more people donate using your form.
Click “Publish.” Google Sheets displays the embed code, which you can copy and paste into the website where you would like it to display.
Now every time someone pledges a donation using your form, the chart updates, and everyone can see which region has raised the most money.
The possibilities of charts from your Google Forms are endless. Now you can create dynamic charts from surveys as people answer the most important questions. Teachers can use forms for many reasons, from surveying students to self-grading multiple-choice quizzes. And then the charts can be used to update administrators, parents, and students.
Get stories like this one delivered to your in-box weekly: Subscribe to the GSD newsletter.
GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.