crowdsourced-map-functions

// Add the ID of the spreadsheet here
var spreadsheetID = '0An8W....';

// Add the name of the sheet here
var workingSheetID = 'working_spreadsheet';

// Add the table ID of the fusion table here
var tableIDFusion = '0An8W....';

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

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

// create menu buttons
function onOpen(){
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var menuEntries = [{
        name: "Process New Row of Data",
        functionName: "runSpreadsheetFunctions"
    }, {
      name: "Update Map With New Data From Spreadsheet",
      functionName: "updateFusion"
    }, {
        name: "Backup Spreadsheet Data",
        functionName: "syncMasterDataSheet"
    }, {
        name: "Set Range of Data For Map (Include Headers)",
        functionName: "setRangeFusion"
    }, {
    	name: "Change Email Information",
    	functionName: "fixEmail"
    }];
    ss.addMenu("Update Spreadsheet and Map", menuEntries);
};

// function to run on a submitted form
function onFormSubmit(e) {
    var emailToAddress = 'ckeller@scpr.org';
    var submissionTimeStamp = e.values[0];
    var submissionPollingPlace = e.values[1];
    var submissionExperience = e.values[6];
    var submissionNarrative = e.values[2];
    var submissionFullName = e.values[3];
    var emailSubject = 'Voter issues map submission from' + submissionPollingPlace;
    var emailBody = submissionTimeStamp + '\n' +
        'Voting at ' + submissionPollingPlace +
        ' was ' + submissionExperience + '.\n' + submissionNarrative +
        ' -- submitted by' + submissionFullName;
    MailApp.sendEmail(emailToAddress, emailSubject, emailBody);

    // run the data processing functions
    runSpreadsheetFunctions();
};


// run functions that will process the data
function runSpreadsheetFunctions(){

    geocodeAddressFromCell();
    Utilities.sleep(1000);

    evaluateVotingExperience();
    Utilities.sleep(1000);

    updateFusion();
    Utilities.sleep(1000);

};

// geocode the address field in the last row of the spreadsheet
function geocodeAddressFromCell(){
    var sss = SpreadsheetApp.openById(spreadsheetID);
    var ss = sss.getSheetByName(workingSheetID);
    var lastRow = ss.getLastRow();
    var addressCellData = ss.getRange(lastRow, 2).getValue();
    var results = Maps.newGeocoder().geocode(addressCellData);
    if (results.status == 'OK') {
        var bestResult = results.results[0];
        var lat = bestResult.geometry.location.lat;
        var lng = bestResult.geometry.location.lng;
        var latLng = lat + ',' + lng;
        var addressTargetCellData = ss.getRange(lastRow, 9).setValue(latLng);
    } else {
        Logger.log(results.status);
        return '0,0';
    }
};

// evaluate voting experience and set identifier
function evaluateVotingExperience(){
    var sss = SpreadsheetApp.openById(spreadsheetID);
    var ss = sss.getSheetByName(workingSheetID);
    var voterExperienceColumn = "G";
    var voterMarkerIdColumn = "H";
    var lastRow = ss.getLastRow();
    var voterExperienceCell = voterExperienceColumn + lastRow;
    var voterMarkerIdCell = voterMarkerIdColumn + lastRow;
    var voterExperienceFormula = "=IF(" + voterExperienceCell + "=\"Negative\", \"large_red\", IF(" + voterExperienceCell + "=\"Positive\", \"large_green\"))";
    var ssCellToGetFormula = ss.getRange(voterMarkerIdCell);
    ssCellToGetFormula.setFormula(voterExperienceFormula);
};

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