Automating Crowdsourced Maps With Apps Scripts
Back in November, on my second day at KPCC, I collaborated with Kim Bui on a crowd-sourced map that displayed the experience that voters had at the polls.
The map – powered using a Google Form to submit data to a spreadsheet which was then uploaded to Fusion Tables – required some hand-holding on my part. OK alot. I had to:
- Make sure the address was geocoded properly.
- Make sure we added the correct marker based on the experience.
- Sync the data to Fusion Tables using a handy script.
- Make sure the map markers appear correctly.
But it was my second day, and I didn’t have much on my plate, though I know it could go better.
Some six months later I’ve become more comfortable here, and more comfortable with Google Apps Scripts thanks to some great examples from others. So for a map for Tuesday’s election that will allow voters to submit their experience at the polls I’ve been able to automate a lot of the process. I’ll still hold its hand – cause that only makes sense – but a lot of the manual tweaking should be unnecessary.
Here are some of the scripts I’m using.
Do something when a form is submitted
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();
};
I’m still using a Google form to populate a spreadsheet, but I’m using an a script to trigger some additional functions when the submit button is pressed. This one – cribbed from an Andy Boyle walkthrough – will send an email with tje information that was just submitted. The script sets each column to a varible; e.values[0] in this case represents the first column in the spreadsheet…
I’ve also piggybacked a generic runSpreadsheetFunctions() function, so that when a form is submitted, a whole host of actions take place. Among them:
Geocode a user-submitted address
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';
}
};
This script will find a column in the last row of the spreadsheet – var addressCellData = ss.getRange(lastRow, 2).getValue(); – and run that value through Google’s geocoder and output a latitude/longitude pair – var latLng = lat + ',' + lng;. That latitude/longitude pair is assigned to a variable and added to “geocoded_address” column in that last row – var addressTargetCellData = ss.getRange(lastRow, 9).setValue(latLng);
Add a function to a cell
function evaluateVotingExperience(){
var sss = SpreadsheetApp.openById(spreadsheetID);
var ss = sss.getSheetByName(workingSheetID);
var projectTypeColumn = "G";
var projectMarkerIdColumn = "H";
var lastRow = ss.getLastRow();
var projectTypeCell = projectTypeColumn + lastRow;
var projectMarkerIdCell = projectMarkerIdColumn + lastRow;
var projectMarkerIdFormula = "=IF(" + projectTypeCell + "=\"Negative\", \"large_red\", IF(" + projectTypeCell + "=\"Positive\", \"large_green\"))";
var ssCellToGetFormula = ss.getRange(projectMarkerIdCell);
ssCellToGetFormula.setFormula(projectMarkerIdFormula);
};
This script is similar to the geocodeAddressFromCell() function in that it finds a value in a specific column in the last row of a spreadsheet. But instead of geocoding the value, it will insert an IF formula into the cell to evaluate whether the voter’s experience was positive of negative and add a map marker type depending on the result.
Then of course there is the series of functions that allows you to sync a Google spreadsheet with a Fusion Table.
Tie it all together
The update script is incorporated into runSpreadsheetFunctions(), and which is run when a form is submitted, so the Fusion Table is updated automatically when a voter clicks the submit button.
To be safe, I set a “timeout” to make sure the previous functions have run before the Fusion Table is updated.
// run functions that will process the data
function runSpreadsheetFunctions(){
geocodeAddressFromCell();
Utilities.sleep(1000);
evaluateVotingExperience();
Utilities.sleep(1000);
updateFusion();
Utilities.sleep(1000);
};