Google Group user John M. posts script that brings Google spreadsheets and Fusion Tables closer together

Published 2012-01-31

tl;dr: I've edited this post to better clarify how to use a script based on one written by John McGrath that will allow you to update a Fusion Table from a Google spreadsheet.

I had modified John's original script for my needs, added a variable for an API key and adjusted it to use a new Fusion Tables API endpoint as the original version used the SQL API endpoint, which was phased out. I will admit that save for a few legacy projects that still use Fusion Tables I don't actively use this script anymore -- hence no real development or maintaining of it. Something changed on the authentication end a while back it seems, and I can't vouch for whether this script continues to work or not. I know that once I enabled two-factor authentication for my Google account this script stopped working. I've added a couple potential solutions here, but would welcome someone -- Google or otherwise.

Fusion Tables -- at least in the circles I pay attention to on the internet -- has become an ubiquitous method that beginners and advances users alike can use to map & visual information.

Though now part of Google Drive -- and certainly more advanced than it was just a year ago -- Fusion Tables still isn't as tightly integrated with Google spreadsheets as many would like to see.

In my work I have found two workaround methods that will allow me to add information to a Google spreadsheet and then POST it to a Fusion Table:

Both of these methods are more efficient than updating spreadsheet, downloading a csv of the spreadsheet, deleting Fusion Tables rows and then importing the csv to the Fusion Table. But the two methods do operate in different ways.

I prefer John's script, and here's an updated attempt to help you get the script up and running, and troubleshoot potential issues.

Google spreadsheet

Fusion Table Import

Fusion Table Import

Fusion Table Import

Fusion Table Import

  1. First choose "Change Email Information." This will authenticate your gmail account to access the Fusion Table. Note: I HAVE NOT had success using this with a Google Apps account going to a private Gmail account. Click the couple of confirmation buttons that appear.

  2. Second you should select all of the data -- columns and rows -- you wish to sync and choose Change Range of Data to be Sent (Include Headers)". I usually just click the rectangle in the upper-left corner to sync everything. The beauty -- in my experience -- is that blank rows and columns can be synced but they won't be reflected on the Fusion Table. Click the two confirmation buttons that appear.

  1. Now just add some information to your spreadsheet and click "Update Fusion Table." Your spreadsheet data should be synced with your Fusion Table.

  1. Sit back and enjoy this moment …

I've also put together this working demo that I can grant you access to in case it helps you spot where a difference might lie. You can add your API key and Table ID to test things out, and then make a copy and be off and running. Just be sure you delete your API and Table IDs...

This script can now be combined with other script functions to make the integration between Google spreadsheets and Fusion Tables more powerful. Or perhaps you want to add a trigger to sync data between the spreadsheet and the table. Such a trigger can be added to run minute by minute, or hourly and those changes will be reflected on the table.

Of course, unless the script didn't work. Perhaps you received an error message like:

    Request failed for returned code 403. Server response: { "error": { "errors": [ { "domain": "usageLimits", "reason": "accessNotConfigured", "message": "Access Not Configured" } ], "code": 403, "message": "Access Not Configured" } }
  1. So let's try to decipher some error messages.

If you get stuck and things just aren't working, let me know and I'll see what I can do. There are a lot of moving parts and little details to pay attention to, and I'm sure I've overlooked something in this walkthrough.

Trying to log error messages and the possible cause. These are not confirmed. Just best guesses


    Request failed for returned code 400. Truncated server response: { "error": { "errors": [ { "domain": "fusiontables", "reason": "badQueryCouldNotParse", "message": "Invalid query: Parse error ne... (use muteHttpExceptions option to examine full response)

The following is likely caused by your API key not being recognized.

    Request failed for API CODE& returned code 401. Truncated server response: { "error": { "errors": [ { "domain": "global", "reason": "authError", "message": "Invalid Credentials", "locationType": "head... (use muteHttpExceptions option to examine full response) (line 79, file "Code")

Which might just mean that your API key isn't active yet… Or you might receive an error because you didn't authenticate with the application when prompted. Be sure you have activated the Fusion Tables API on the Google API console and added the key to the script in the proper place.

The following is likely caused by either not authenticating or a corrupt authentication token

    Request failed for returned code 403. Truncated server response: Error=BadAuthentication Url= (use muteHttpExceptions option to examine full response

To sync things, Google needs to know you have permission to access the spreadsheet and Fusion Table. This is usually done by signing in the first time you run Update Fusion. It seems that this may have changed since this script was first created. A quick test showed that if I cleared out my email and password each time I ran the updateFusion function I was able to sync the spreadsheet. While not ideal, it seems to work.

To do this I added UserProperties.deleteAllProperties(); to the top of the updateFusion function, which I think starts around line 26.

The full function would looks like this:

    // main function
    function updateFusion() {


        // gets the user property 'email' out of project properties
        var email = UserProperties.getProperty('email');

        // gets the user property 'password' out of project properties
        var password = UserProperties.getProperty('password');

        // if either email or password is not saved in project properties this will store them there

        if (email === null || password === null) {

            // browser box to input email
            email = Browser.inputBox('Enter email');
            password = Browser.inputBox('Enter password');
            UserProperties.setProperty('email', email);
            UserProperties.setProperty('password', password);
        } else {
            email = UserProperties.getProperty('email');
            password = UserProperties.getProperty('password');

        var authToken = getGAauthenticationToken(email, password);
        deleteData(authToken, tableIDFusion);
        var updateMsg = updateData(authToken, tableIDFusion);
        var updatedRowsCount = updateMsg.split(/\n/).length - 2;
        SpreadsheetApp.getActiveSpreadsheet().toast("Updated " + updatedRowsCount + " rows in the Fusion Table", "Fusion Tables Update", 5)