Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
A series of Google Apps Scripts I use to automate some aspects of making crowdsourced maps...
// 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);
}
};
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.