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:

• In early 2012, Kathryn Hurley -- then a member of the Google Fusion Tables team -- offered up a script for Google Apps that allowed a user to submit data to a Fusion Table from a form, using a Google spreadsheet as a conduit.

• Then, the aforementioned John posted an Apps script to GitHub that authenticates against a Google account and updates a Fusion Table from a spreadsheet.

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.

• Head to Google docs, create a spreadsheet and add some data to it. Simple enough right? Just make sure that Column A has data in it. It seems blank or null values will break the update function. Though if other columns are without data the script appears to work fine. At some point, I'll learn to add an error message if Column A is blank, but for now, Column A wants data.

• Now I'm going to create a Fusion Table based off my spreadsheet. I do this either by importing from Google Docs or downloading the spreadsheet as a csv and uploading it to Fusion Tables. It is important to note from the outset that the column names must match between the spreadsheet and the table. Remember, if you change a column name or add a column to the spreadsheet, be sure to change it/add it to the Fusion Table as well. For good measure I make sure any new columns are also in the same order.

• After my Fusion Table is created, I need to get the Encrypted Table ID in order to make sure the spreadsheet can access it. To find the Encrypted Table ID I click File --> About this table. I'll copy this somewhere in a text file until I need it.

• I'm almost ready to make this happen, but I'm going to need one other piece of information - a Google Fusion Tables API key. I need to turn on access to the Fusion Tables API in Google's API console, and get an authentication key. Don't worry, this is much easier than it sounds.

• First, head to the API Console dashboard and log in with your Google account if prompted. The first screen you see if an overview. If it's your first time here, you might not see a lot. On the left-side of the screen you'll see a dropdown where you can create a new project. Go ahead and create one and give it a name.

• Next click on Services on the left-side of the screen and you will see a heck of a lot of toggles that can turn on various Google APIs. Scroll down a bit until you find Fusion Tables API, and flip to toggle to the 'On' position.

• Finally, click on API Access on the left-side of the screen. You will see two main areas: Authorized API Access and Simple API Access. We're interested in the API Key shown under Simple API Access. I'll copy this somewhere in a text file next to my Encrypted Table ID.

• Now we're ready to add our script to our spreadsheet. Back at your spreadsheet, go to Tools --> Script Editor and paste the script code. I add my Fusion Table's encrypted table ID to the top of the script...

  // Add the encrypted table ID of the fusion table here
  var tableIDFusion = '17xnxY......';
  

• Then I add my API key.

  // key needed for fusion tables api
  var fusionTablesAPIKey = '17xnxY......';
  

• Click save. You will be prompted to give the project a name. "Update Fusion Tables" works. Click the save icon or go to File --> Save.

• Reload the spreadsheet and you will see a new menu item next to help. Mine says "Data Update Functions." Click the menu item and you will see three options, though the names may differ at this point: "Change Range of Data to be Sent (Include Headers)", "Update Fusion Table" & "Change Email Information."

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 https://www.googleapis.com/fusiontables/v1/query?key=ajhdndna8282n29& 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 https://www.googleapis.com/fusiontables/v1/query?key=AIzaSyCBbVMnJwhD5xPYJpcvOT8vOUlLs9jYv5U& 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 https://www.googleapis.com/fusiontables/v1/query?key=MY 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 https://www.google.com/accounts/ClientLogin returned code 403. Truncated server response: Error=BadAuthentication Url=https://www.google.com/accounts/ContinueSignIn?sarp=1&scc=1&plt=AKgnsbutVGIXKvt9Nhj84zteA6Qk3RgCkgkQzOPygJE4aWgRrWXJ8_N... (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() {

        UserProperties.deleteAllProperties();

        // 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)
    };