Created
August 5, 2013 20:07
-
-
Save chrislkeller/6159121 to your computer and use it in GitHub Desktop.
A series of Google Apps Scripts I use to automate some aspects of making crowdsourced maps...
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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