Synchronizing Fusion Tables with Google Forms

Table of Contents

Before you begin

1. Create your form, spreadsheet, and Fusion Table
2. Set up the script
3. Send the form to your users

Troubleshooting

Before you begin

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 cannot make any changes to the form or Fusion Table.

Create your form, spreadsheet, and Fusion Table

Your form will be backed by a spreadsheet, to which you will add triggering code to copy the responses to a Fusion Table.

Form and spreadsheet

1. From Google Drive click "Create > Form" to create a new form. Set up your desired form questions and give it a useful title.
2. Click "Choose response destination" from the toolbar at the top of the form editor. In the resulting dialog select:
   1. Send responses to: Spreadsheet
   2. New spreadsheet
   Then click "Create".
3. Click on the "View responses" link in the gray bar at the top of the page to see the newly created spreadsheet. Note that the form automatically creates a Timestamp column.

Table

1. From Drive click "Create > Table (Experimental)" to create a new Fusion Table. If you don't see that choice:
   1. Click "Connect more apps" at the bottom of the list
   2. Type "fusion tables" in the search box followed by the Enter key.
   3. Click the blue "+ Connect" button to add the app.
   4. You should see a dialog box saying "Google Tables was connected to Google Drive." Click the OK button to continue.

   If you can't find Fusion Tables in the list of apps, try Fix: Not in Google Drive menus.

2. In the "Import new table" dialog box, select Google Spreadsheets in the left-hand column. Select your spreadsheet from the list and import.
3. If you collect location data, choose "Edit > Add column" in the table UI to include a column with type "Location" to hold your geocoded coordinates.

Set up the script

Create the script project

1. Click on the browser tab with your form response spreadsheet and click "Tools > Script editor..." to bring up the Apps Script project window.
2. If you see a dialog entitled "Google Apps Script", click "Blank Project" in the "Create script for" section.

Set up your form's properties

1. Click "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.
2. Click the "Project properties" tab.
3. Click "+ Add row" to start adding your custom properties. This displays a row with "(name)" and "(value)" placeholders.

   Add the docid row, replacing the name and value placeholders:

   Name	Value to use	Notes
   docid	The identifier of your table.	To find this value, select "File > About" in the table menu. Copy the "Id" value.

   If you collect location data, also add these properties:

   Name	Value to use	Notes
   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 is submitted, the script geocodes the value in the addressColumn, and saves the lat/lng coordinates in this column.

   Make sure you have added the location column to your table and have added these properties before you run the script. You cannot change your table once you start using the script.

4. Click "Save" to save the custom properties.

Enable the Fusion Tables service

Follow these instructions to enable the use of the Fusion Tables service for Apps Script and in the Developer Console.

Add the code

1. Your new script has some placeholder code. Replace it with this code.
2. Click "File > Save".

Authorize the script

1. Click "Run > checkAuthorization" from the top menu. You will see an "Authorization Required" dialog box.
2. Click the "Continue" button. This brings up another window spelling out what your access the script needs. Click the blue "Accept" button to authorize the script; this adds a token to your project properties so you won't have to do this step again. See this help article for more information on authorization, including how to revoke access.

Add the triggers

Spreadsheet triggers run Apps Script code based on various events. Here you add triggers to run the "onFormSubmit" method when the form is submitted with a new answer, and optionally to run a general synchronization periodically.

1. Click "Resources > Current project's triggers".
2. Choose these values from the dropdown menus: Run: onFormSubmit, Events: From spreadsheet, On form submit
3. Click "File > Save".
4. Optional: You can set up a trigger to run the sync method periodically.
   1. Click "Resources > Current script's triggers", then "Add a new trigger".
   2. Choose these values from the dropdown menus: Run: sync, Events: Time-driven, Hour timer, Every hour.
5. Click "Save".

Set up error notifications

Triggers can notify you of problems by email. When you are first getting set up, it's a good idea to set up notifcations so you can see what's going on. This help page has more details at the bottom of the page in the "Errors in Triggers" section.

1. Click "Resources > Current project's triggers".
2. Click on the "Notifications" link for the "onFormSubmit" or "sync" trigger.
3. Choose the appropriate time to email. When you are first getting set up, choosing "immediately" will make it easier to see what's going wrong.
4. Click "Save".

Try it all out

1. Go to your live form: from the form editor click "View live form" in the gray bar at the top of the page.
2. Enter some form values and click "Submit".
3. After a few seconds, check that the spreadsheet has the new values, and also has a new column called "rowid".
4. Check that the Fusion Table also has the new values. You will have to refresh the web page to get the latest data.
5. If either of these is not working, from the script editor click "View > Execution transcript" and "View > Logs" to see if anything shows up. You might also get an error notification email.
6. You're done! Now you need some real data.

Send the form to your users

From the Google Form, click the blue "Send Form" button and choose one of these sharing options:

• Send form via email: enter your users' email addresses
• Send a link: click the URL under the "Link to share" label, copy it, and send
• Embed the form: Click the "Embed" button and follow the instructions

Once people start submitting form entries and the synchronization has run, you will see the "rowid" column in your spreadsheet filling up with the unique numbers used by Fusion Tables to identify rows. These are used by the script to apply updates and deletions correctly.

Don't change the rowids — that could cause the synchronization to have unexpected results.

Troubleshooting & FAQs

My users entered incorrect location data.

1. No worries! Open the spreadsheet and fix the data.
2. Click "Tools > Script editor..." to go to the Script Project window.
3. Click "Run > sync" from the top menu. This updates your table with the corrected 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.

• Unfortunately adding another question isn't currently supported. You can't modify the table's columns once the script has been used.

"ReferenceError: "FusionTables" is not defined."

• Ensure that you enabled the Fusion Tables Service.

Something else is wrong.

• From the script editor check the script logs by selecting "View > Logs" and "View > Execution transcript". Review the errors to see what line of code the issue is occurring on and submit a bug report in the issue tracker.