Synchronizing 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.

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 your form, spreadsheet, and Fusion Table

  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.
  3. From the resulting dialog, click the Create button to send the form responses to a new spreadsheet.
  4. Back on the Google Drive page click on the newly created spreadsheet. Note that the form automatically creates a Timestamp column.
  5. 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 these steps: Fix: Not in Google Drive menus.

  6. In the "Import new table" dialog box, select Google Spreadsheets in the left-hand column. Select your spreadsheet from the list and import.
  7. If you collect location data, choose Edit > Add column in the table UI to include a column (Type: location) to hold your geocoded coordinates.

Set up the script

  1. Return to the form response spreadsheet and click Tools > Script editor... to bring up the Apps Script project window.
  2. You may see an dialog entitled "Google Apps Script" that asks what to create a script for. If so, select "Blank Project".
  3. 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. Enter a name for your project, such as "FT Form." This displays the Project Properties dialog box.
    3. Click the Project properties tab.
    4. Click + Add row to start adding your custom properties. This displays a row with "(name)" and "(value)" placeholders.

      Add each of the following rows, replacing the name and value placeholders:

      Name Value to use Notes
      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, 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 data is submitted, the script geocodes the data 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.

      Click Save to save the custom properties.

  4. Your new script has some placeholder code. Replace it with this code.

    The code uses the Fusion Tables API, for which you need an API key. Follow these instructions and then put the key in near the top of the code:

  5. Click File > Save.
  6. 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.
  7. Set up a trigger to run the onFormSubmit method when the form is submitted.
    1. Click Resources > Current script's triggers.
    2. Click Add a new trigger. You will see a dialog telling you there are no triggers set up. Click the link to add one.
    3. Choose these values from the dropdown menus: Run: onFormSubmit, Events: From spreadsheet, On form submit.
    4. Click File > Save.
  8. 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.
  9. You're done! Now you need some 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 a new column in your spreadsheet called "rowid". This holds the unique numbers used by Fusion Table 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.

Something else is wrong.

  • From the script editor 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 in the issue tracker.