Script to bring Google spreadsheets and Fusion Tables closer together

spreadsheet-to-ft

/*######
John McGrath via the Google Fusion Tables group authored this script which creates a "sync" between a Google spreadsheet and a Fusion Table.

He has since posted it to GitHub here: https://github.com/jomcgrath2/Fusion-to-Spreadsheet

######*/

var tableID = ' ' // Add the table ID of the fusion table here
var sheetNumber = 1 //Sheet number goes here (the first sheet is number 1 second is number 2 etc.)

//create button
function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var menuEntries = [ {name: "Update Fusion Table", functionName: "updateFusion"}, 
                      {name: "Change Email Information", functionName: "fixEmail"},
                      {name: "Change Range of Data to be Sent" + "\r\n" + "(RANGE STARTS JUST BELOW HEADERS)", functionName: "setRange"}];
  ss.addMenu("Fusion Tables", menuEntries);
}

//main function
function updateFusion() {

  var email = UserProperties.getProperty('email');
  var password = UserProperties.getProperty('password');

  if (email === null || password === null) {
    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, tableID);
  var updateMsg = updateData(authToken, tableID);
  var updatedRowsCount = updateMsg.split(/\n/).length - 2;
  Browser.msgBox("Fusion Tables Update", "Updated " + updatedRowsCount + " rows in the Fusion Table", Browser.Buttons.OK);  
}

//authentication fusion API

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) {
  var URL = "http://www.google.com/fusiontables/api/query";
  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);
}

//insert new data in fusion table

function updateData(authToken, tableID) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var blockDataRange = ss.getRangeByName('nonheaders');
  var query = constructQuery(ss, blockDataRange, tableID);
  //Browser.msgBox(query);
  return queryFusionTables(authToken, query);
}

//this puts all the current information in the spreadsheet into a query

function constructQuery(ss, range, tableID, columnHeadersRowIndex) {
  var sheet = ss.getSheets()[sheetNumber - 1];

  var columnHeadersRowIndex = columnHeadersRowIndex || range.getRowIndex() - 1;
  var numColumns = range.getEndColumn() - range.getColumn() + 1;
  var headersRange = sheet.getRange(columnHeadersRowIndex, range.getColumn(), 1, numColumns);
  var headers = headersRange.getValues()[0];
  var data = range.getValues();
  
  //remove ' from headers
  for( var i in headers ) {
    if (isNaN(headers[i])){

      headers[i] = headers[i].replace(/'/g, "\\'");
    }
  }
  
  //remove ' from strings
  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 queryPrepend = "INSERT INTO " + tableID + " (" + "\'" +headers.join("\',\'") + "\'" + ") VALUES ('";

  var query = "";

  for (var i = 0; i < data.length; ++i) {
    var hasData = false;
    if (cellEmpty(data[i][0])) {
      continue;
    }
    query += queryPrepend + data[i].join("','") + "'); ";
  }
  return encodeURIComponent(query);
}

function cellEmpty(cellData) {
  return typeof(cellData) == "string" && cellData == "";
}

//change email if needed
function fixEmail() {
   var decision = Browser.msgBox("WARNING", "Are you sure you want to chage 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 setRange() {
   var decision = Browser.msgBox("WARNING", "Are you sure you want to chage the Update Fusion Range?", Browser.Buttons.YES_NO);
   if (decision == 'yes'){
     var ss = SpreadsheetApp.getActiveSpreadsheet();
     var range = SpreadsheetApp.getActiveRange()
     ss.setNamedRange("nonheaders", range);
     Browser.msgBox("WARNING", "The range 'nonheaders' used to send data to Fusion has been changed.", Browser.Buttons.OK);
   }
}