How It Worked—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 do what you tell it to do. So I wanted to make sure each 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 Worked—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 I was at it, I created a function that allowed the use of one map marker if the voter experience is positive, and another if the experience is negative. I did 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, I opened a spreadsheet based on ID and sheet name, and used columns and rows to whittle down to the cell that holds the value I wanted 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;

I created an IF/ELSE formula for our evaluation:

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

…and added 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(), 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 here.

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. But I think I might be inclined to take this one again. Why?

• I found the constraints helpful. Outside of the learning, the set-up was a breeze. And because we could only do so much with this set-up, our project was focused pretty tightly.
• Once in place, the only hand-holding needed is to make sure the data being submitted makes sense and isn’t off topic. This is done in the spreadsheet, which means zero knowledge of code is required.
• The first time around—being new on the job and with only a day to work with—I didn’t want my first interactions to be about setting up databases and getting server access. I knew this would work and set off in that direction.
• As for this latest go-round, I kind of wanted to see just how far I could take Apps Scripts based on work others had done, and I’m glad I did. It allowed us to pull off an interactive crowdsourced map rather quickly and with minimal overhead.