README.md

Script to sync a Google SpreadSheet to a Fusion Table

• 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 … unless it 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" } }
    

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

If you get stuck and things just aren't working, let me know and I'll see what I can do.

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