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.
We powered the map successfully using a bunch of Google tools — a form, a spreadsheet and a Fusion Table — some duct tape and some manual labor.
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 as I re-purposed the idea for Tuesday’s election to allow voters to submit their experience at the polls I was able to add a certain level of automation to the project.
I’ll still hold its hand to ensure accuracy — cause that only makes sense in journalism — but a lot of repetitive work from six months ago should be unnecessary.
How It Was Done
The duct tape and manual labor was necessary because even though forms and spreadsheets and Fusion Tables all exist within Google’s Drive application, the level of integration between a form and a spreadsheet and a spreadsheet and a Fusion Table remains basic.
There are methods — of course — to extend the integration. A script can send form data to a Fusion Table, but once each of them are created you’re locked in, so no adding another question to the form.
The manual labor was mostly my thing. While Google’s geocoder will do the best it can to find the latitude and longitude of a location, it will only what you tell it to do, so I wanted to make sure the address was geocoded properly before it appeared on the map. And besides, it was my second day at a new job and surely didn’t want one of my first projects to flop.
How It Will Be Done
I’m still using a Google form to populate a spreadsheet, but I’ve tied a lot of the automation to a function that runs when the form’s submit button is pressed.
This one — cribbed from an Andy Boyle walkthrough — will send an email with the information that was just submitted.
The script sets each column to a variable; e.values[0] in this case represents the first column in the spreadsheet…
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’ve piggybacked a generic runSpreadsheetFunctions() function, so that when a form is submitted, a whole host of actions take place.
Among them is a function to run a user-submitted address through the geocoder and add the latitude and longitude of that address to a separate spreadsheet cell:
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);
While we’re at it, let’s use one map marker if the voter experience is positive, and another if the experience is negative. We can do this by adding an =IF() spreadsheet formula 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);
};
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);
};