Synchronizing Fusion Tables with Google Sheets

With a simple script, you can replace the contents of a Fusion Table with the rows of a Google spreadsheet. The script takes advantage of Google's Apps Script tool to run the appropriate replaceRows call.

Table of Contents

Steps
  1. Import your spreadsheet into Fusion Tables
  2. Set up the script
  3. Set up triggers

Troubleshooting

Import your spreadsheet into Fusion Tables

  1. From the Fusion Tables home page click the blue "Create a Fusion Table" button.
  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. Click "File > About this table" and note the table ID at the bottom.
If you later add, remove, or reorder columns in your sheet, you should make the equivalent changes in the table using "Edit > Change columns".

Set up the script

Create the script project
  1. Click on the browser tab 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.
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. Change this line to put your table ID in where it says "YOUR TABLE ID":

    var TABLE_ID = 'YOUR TABLE ID';

  3. Optional: if you have multiple header rows, put the row number of the first data row on this line:

    var FIRST_DATA_ROW = 2;

  4. Optional: if you want to allow the spreadsheet to have different columns than the table, change this value to "false":

    var REQUIRE_SAME_COLUMNS = true;

  5. Click "File > Save".
Authorize the script
  1. Click "Run > sync" 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.

Set up triggers

Spreadsheet triggers run Apps Script code based on various events. You can update your Fusion Table from your spreadsheet periodically or after every change. In this example the script is set up to run once an hour.

  1. Click "Resources > Current project's triggers".
  2. Choose these values from the dropdown menus: Run: sync, Events: Time-driven, Hour timer, Every hour
  3. 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. Make a change to your spreadsheet that you will easily be able to spot in your Fusion Table.
  2. Run the sync script from the script edtor as described above.
  3. Check that the Fusion Table has the new values. You will have to refresh the web page to get the latest data. If your spreadsheet is large, the changes may not appear immediately; the previous version of the table continues to be available until the table contents are completely replaced.
  4. If you don't see the new values, or you get an execution error, 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.
  5. If you do see the new values, you're done!

Troubleshooting

"Content has a different number of columns than the table"

This means your table and spreadsheet no longer match up. You can either edit your script and change REQUIRE_SAME_COLUMNS to false, or use "Edit > Change columns" in your table to add, remove, or reorder the columns to match your spreadsheet.

"ReferenceError: "FusionTables" is not defined."

Ensure that you enabled the Fusion Tables Service.

Log message: "Skipping row replacement because of N active background task(s)"

This means the table schema was being changed or its rows were in the process of being deleted, either from the web app or an API call.

Something else is wrong.

From the script editor check the script logs by selecting "View > Logs" and "View > Execution transcript". If you see errors and they aren't self-explanatory, follow the suggestions in this help article for getting help. Code-related questions are usually best asked on StackOverflow.