Syncing a Google Form with a Fusion Table
With a simple script, you can set up a Google Form that your users can use to input data to a Fusion Table. The script takes advantage of Google's AppsScript tool to transfer new form input into your table.
There are just a few steps to get the script up and running:
- Create a form and set up an empty table.
- Set up the script and activate it.
- Send the form out to your users.
If you run into a problem, there's a troubleshooting section at the end.
Table of Contents
Before you begin
For security reasons, make sure you have a "role" gmail account to use to complete these instructions. A role account is an account that is not your personal or work email address, but one that is created for a specific task. Your password will be shown in clear in the Script Editor, so make sure you use a role account to run this script.
If you don't have a role account, create one now.
Plan out your data. If you are collecting geographic data that contains a street address such as "1600 Amphitheatre Parkway, MV, 94043" or a text location string such as "Yosemite Park" and you need Fusion Tables to geocode them for you, there are some additional steps to follow (noted below).
Also note that once you have started collecting data, you can not make any changes to the form or Fusion Table.
Create a form and set up an empty table
- Choose Create > Form in Google Docs to create a new form. Set up your desired form questions, give it a useful title, and Save it.
- Go to the main Google Docs page and open the form's spreadsheet file. Note that the form automatically creates a Timestamp column.
- Return to Google Docs and choose Create > Table (Beta) to create a new Fusion Table. In the dialog box, select Google Spreadsheets in the left-hand column. Select your form from the list and import.
- If you collect location data, choose Edit > Add column in the table UI to include a column (Type: location) to hold geocoded lat-long values.
- When you're done, close the table.
Set up the script and activate it
- Return to the form's spreadsheet in Google Docs and choose Tools > Script editor... This displays a project window for your form.
Set up your form's properties:
- Choose File > Project properties... The first time you choose this command, the Rename Project dialog box is displayed. After that, the main project properties dialog box is displayed.
- Enter a name for your project, such as "FT Form." This displays the Project Properties dialog box.
- Select the Project properties tab.
Click the +Add row link. This displays a row with
"(name)" and "(value)" placeholders.
Replace the placeholders as follows:
Property Value username The username of the account that owns the table. Use a role account. This does not work with gmail accounts that have 2-step verification enabled. password The password of the account that owns the table. This shows up in clear, so use a role account. docid
The identifier of your table. To find this value, select File > About in the table menu. Copy the value next to Encrypted id.
- If you collect location data, two additional project properties are required to allow the script to correctly geocode your data and place the coodinates in your table. Note: Make sure the location column is added and the properties are mapped before you run the script. You cannot modify your table once you start using the script.
Property Value Description addressColumn The exact name of the column in your table that contains the address. Do not use quotes. If your data is already geocoded, you do not need this property. latlngColumn The exact name of the location column you added to your table. When the form data is submitted, the script geocodes the data in the addressColumn, and saves the lat/lng coordinates in this column.
- Save the Project Properties.
- Replace the few lines of placeholder code in the script editor with the code here.
- Save the project.
Authorize the script:
- Choose Select Function > onFormSubmit from the drop-down menu in the gray menu bar.
- Click the Run button (small triangle) in the gray menu bar. This displays the Authorization Required dialog box.
- Click the Authorize button. This adds an authorization token to your project properties and briefly displays a small dialog box that says "You can run the script."
Set up a trigger to run the onFormSubmit method when the
form is submitted:
- Choose Triggers > Current script's triggers...
- Click the Add a new trigger link
- Choose these values from the dropdown menus: Run: onFormSubmit, Events: From spreadsheet, On form submit --> does geocoding for you
- Save your project again and close the tab.
[Optional] You can set up a trigger to run the
syncmethod every hour. This syncs any changes from the script editor menu.
- Choose Triggers > Current script's triggers...and click the Add a new trigger link
- Choose these values from the dropdown menus: Run: sync, Events: Time-driven, Hour timer, Every hour
- You're done. Now you need some data.
Send it out to your users
From the Google Documents spreadsheet, choose one of these
options to share your form:
- Email users: Choose Form > Send form, enter your users' email addresses, and click Send.
- Send a link: Choose Form > Go to live form and copy the URL in the address bar.
- Embed the form: choose Form > Embed form in a webpage and paste the HTML code provided into your website or blog.
Note: Once users start entering data, the spreadsheet adds a column to hold rowIDs. These nonsequential numbers do not appear in the table, but they are available to API developers.
Troubleshooting & FAQs
My users entered incorrect location data.
- No worries! Open the spreadsheet and fix the data.
- Choose Tools > Script editor... to go to the Script Project window.
- Choose Select Function > sync from the drop-down menu in the gray menu bar.
- Click the Run button (small triangle) in the gray menu bar. This syncs your table with the new spreadsheet data.
My data is not being saved to Fusion Tables.
- Double check that the column names in the spreadsheet and Fusion Table match exactly. By default, they should match, but if for any reason they do not, go in and change the spreadsheet columns to match the table's columns.
- Make sure that you saved your properties in the Project Properties tab, not the User Properties tab.
I want to add another question to my form now that the form is up and running and collecting data.
- Sorry, adding another question isn't supported at this time. You can't modify the table's schema once the script is being used.
Something else is wrong.
- Check the script logs by selecting View > Logs.... Review the errors to see what line of code the issue is occurring on and submit a bug report at http://code.google.com/p/kh-samples/issues/list