Automating crowdsourced maps with apps scripts

Published 2013-05-15

Back in November, on my second day at KPCC, I collaborated with Kim Bui on a crowd-sourced map that attempted to gather information from voters who wanted to share their experience at the polls.

We successfully powered the map using a bunch of Google tools -- a form, a spreadsheet and a Fusion Table -- along with 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 an election back in May, I wanted to see if there wasn't a certain level of automation that I could add to the project.

I didn't want automation for the sake of automation, but rather to eliminate the repetitive and inefficient parts of the process, while still ensuring accuracy -- cause that only makes sense in journalism.

How It Was Done - Nov. 2012

The duct tape 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.

Forms can send data to spreadsheets. Fusion Tables can be created from spreadsheets. But a straight data pipeline from a submitted form to a Fusion Table doesn't explicitly exist. There are methods and scripts that can send form data to a Fusion Table, but in this method once the form and table are created you're locked in; you can't add additional fields on the form or table.

The need to perform simple calculations, comparisons and geocoding of submitted data meant there was a level of manual labor.

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 because we wanted different colored markers to distinguish between voting experiences, we need to perform a comparison on the submitted data.

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 Was Done - May 2013

I still used a Google form to populate a spreadsheet, but 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 runSpreadsheetFunctions() function inside this piece of code. I don't know if it's right and proper, but I want a whole bunch of actions to take place in succession when a form is submitted.

One 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 open a spreadsheet based on ID and sheet name, find the last row on the specified sheet and find a value in a specific column

    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 create a function that will allow us use of one map marker if the voter experience is positive, and another if the experience is negative. We'll do this by adding an =IF() spreadsheet formula to a cell.

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

Again, we'll open a spreadsheet based on ID and sheet name, and use our columns and rows to whittle our way down to the cell that holds the value we want to evaluate, and the cell that will hold the result of that evaluation, which will be a Fusion Tables marker type.

        var voterExperienceColumn = "G";
        var voterMarkerIdColumn = "H";
        var lastRow = ss.getLastRow();
        var voterExperienceCell = voterExperienceColumn + lastRow;
        var voterMarkerIdCell = voterMarkerIdColumn + lastRow;

We'll create an IF/ELSE formula for our evaluation

        var voterExperienceFormula = "=IF(" + voterExperienceCell + "=\"Negative\", \"large_red\", IF(" + voterExperienceCell + "=\"Positive\", \"large_green\"))";

and add that formula to the cell that will hold the result.

        var ssCellToGetFormula = ss.getRange(voterMarkerIdCell);
        ssCellToGetFormula.setFormula(voterExperienceFormula);

Finally, I added an update script, a series of functions that allows you to trigger a sync between a Google spreadsheet and a Fusion Table.

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

The full script -- minus API keys and such -- is below.

Tie it all together

I know what you're probably thinking at this point: that's a heckuva lot of work and scripting to do something you could do with PHP, MySQL and a bit of Javascript.

You. Are. Right.

If I had to do it over again I might consider other approaches to this kind of project. But I think I might be inclined to take this approach again.