Synchronizing Fusion Tables with Google Forms
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
Apps Script tool to transfer new form input into your table.
The previous version of these
instructions required a role account and a publicly visible password;
this version automatically uses your credentials to safely connect to
Fusion Tables instead.
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.
Set up the script
Create the script project
-
Click on the browser tab with your form response spreadsheet and click
"Tools > Script editor..." to bring up the Apps Script project window.
-
If you see a dialog entitled "Google Apps Script", click "Blank Project"
in the "Create script for" section.
Set up your form's properties
-
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.
-
Click the "Project properties" tab.
-
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.
-
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
-
Your new script has some placeholder code. Replace it with
this code.
-
Click "File > Save".
Authorize the script
-
Click "Run > checkAuthorization" from the top menu.
You will see an "Authorization Required" dialog box.
-
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.
-
Click "Resources > Current project's triggers".
-
Choose these values from the dropdown menus:
Run: onFormSubmit, Events: From spreadsheet, On form submit
-
Click "File > Save".
-
Optional: You can set up a trigger to run the
sync
method periodically.
-
Click "Resources > Current script's triggers", then "Add a new trigger".
-
Choose these values from the dropdown menus:
Run: sync, Events: Time-driven, Hour timer, Every hour.
-
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.
-
Click "Resources > Current project's triggers".
-
Click on the "Notifications" link for the "onFormSubmit" or "sync" trigger.
-
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.
-
Click "Save".
Try it all out
-
Go to your live form: from the form editor click "View live form" in the gray bar at the
top of the page.
-
Enter some form values and click "Submit".
-
After a few seconds, check that the spreadsheet has the new values, and also
has a new column called "rowid".
-
Check that the Fusion Table also has the new values. You will have to refresh the
web page to get the latest data.
-
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.
-
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.
-
No worries! Open the spreadsheet and fix the data.
-
Click "Tools > Script editor..." to go to the Script Project window.
-
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."
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.