README.md

Script to sync a Google SpreadSheet to a Fusion Table

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 -- to offer a long-term maintained solution.

Thanks goes out to John McGrath who did the bulk of the work in making this script happen.

• 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."

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

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

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

12. 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" } }

13. 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)
	}; 

spreadsheet_to_fusion_tables.js

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

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

// the name of the range used in the program
var rangeName = 'updateFusion';

// create menu buttons
function onOpen() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Change Range of Data to be Sent (Include Headers)",
        functionName: "setRangeFusion"
    }, {
    	name: "Update Fusion Table",
    	functionName: "updateFusion"
    }, {
    	name: "Change Email Information",
    	functionName: "fixEmail"
    }];
    ss.addMenu("Data Update Functions", menuEntries);
};

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

// Google Authentication API this is taken directly from the google fusion api website
function getGAauthenticationToken(email, password) {
    password = encodeURIComponent(password);
    var response = UrlFetchApp.fetch("https://www.google.com/accounts/ClientLogin", {
        method: "post",
        payload: "accountType=GOOGLE&Email=" + email + "&Passwd=" + password + "&service=fusiontables&Source=testing"
    });

	var responseStr = response.getContentText();
	responseStr = responseStr.slice(responseStr.search("Auth=") + 5, responseStr.length);
	responseStr = responseStr.replace(/\n/g, "");
	return responseStr;
};

// query fusion API post
function queryFusionTables(authToken, query) {

    // location to send the infomation to
    var prefix = "https://www.googleapis.com/fusiontables/v1/query?key=";
    var suffix = fusionTablesAPIKey + '&';
    var URL = prefix + suffix;
      
    // sends the the authentication and the query in url format
    var response = UrlFetchApp.fetch(URL, {
        method: "post",
        headers: {
            "Authorization": "GoogleLogin auth=" + authToken,
        },
        payload: "sql=" + query
    });
    
    return response.getContentText();
};

// delete old data in fusion table
function deleteData(authToken, tableID) {
    var query = encodeURIComponent("DELETE FROM " + tableID);
    return queryFusionTables(authToken, query);
};

// puts all the current information in the spreadsheet into a query
function updateData(authToken, tableID) {
    
    //find sheets with ranges that will be sent
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var range = ss.getRangeByName(rangeName);
    var data = range.getValues();
    
    // format data
    for (var i in data) {
        for (var j in data[i]) {
            if (isNaN(data[i][j])) {
                data[i][j] = data[i][j].replace(/'/g, "\\'");
            }
        }
    }

	var headers = data[0];
	//var queryPrepend = "INSERT INTO " + tableID + " (" + "\'" + headers.join("\',\'") + "\'" + ") VALUES ('";
	var queryPrepend = "INSERT INTO " + tableID + " (" + headers.join(",") + ") VALUES ('";
	var query = "";
	
	for (var i = 1; i < data.length; ++i) {
	   if (typeof (data[i][0]) == "string" && data[i][0] == "") {
	       continue;
	   }

   query += queryPrepend + data[i].join("','") + "'); ";
    
    }
	return queryFusionTables(authToken, encodeURIComponent(query));
};

// change email if needed
function fixEmail() {
    var decision = Browser.msgBox("WARNING", "Are you sure you want to change your email?", Browser.Buttons.YES_NO);
    if (decision == 'yes') {
        var email = Browser.inputBox('Enter email');
        var password = Browser.inputBox('Enter password');
		UserProperties.setProperty('email', email);
		UserProperties.setProperty('password', password);
	}
};

// set range
function setRangeFusion() {
    var decision = Browser.msgBox("WARNING", "Are you sure you want to change the Update Fusion Range?", Browser.Buttons.YES_NO);
    if (decision == 'yes') {
        var ss = SpreadsheetApp.getActiveSpreadsheet();
        var check = ss.getRangeByName(rangeName)
            if (check != null) {
                ss.removeNamedRange(rangeName);
            }
		var range = SpreadsheetApp.getActiveRange()
		ss.setNamedRange(rangeName, range);
		Browser.msgBox("WARNING", "The range \'" + rangeName + "\' used to send data to Fusion has been changed.", Browser.Buttons.OK);
	}
};