Comments

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

Link: Meetings Are Toxic

A NICAR thread about methods of project management and product development led me to some posts on Agile development which led me to find this gem. Like any piece of knowledge that could be deemed as inherent in some people, it’s always good to be reminded of proper meeting rules.

For those times when you absolutely must have a meeting (this should be a rare event), stick to these simple rules:

  • Set a 30 minute timer. When it rings, meeting’s over. Period.
  • Invite as few people as possible.
  • Never have a meeting without a clear agenda.

via Getting Real

Comments

Fresh From Posterous

This is my first post using Octopress, which comes after folks finally decided to pull the plug on Posterous. It was announced yesterday that it will be shutting down on April 30, some 13 months after Twitter acquired the platform, or rather the talent that built the platform.

As I wrote in my last post on Posterous, I enoyed using it because it stayed out of my way… I didn’t feel the need to fiddle with the layout, or adjust the admin or mess around with plugins. It let me just write posts… Maybe not as many as I’d like to, but posts nonetheless. And I stuck with it to a certain degree.

I spent some time considering going back to Tumblr, and I found this promising platform called Snipt. Heck I even installed WordPress for spell.

But all in all I like the idea of Octopress, of something that will continue to stay out of my way and just let me write, of something that will lets write in markdown which is then compiled into static HTML.

Time will tell.

I can say Posterous served me well at a certain point in my life, a time when I was learning to learn, code, program and teach/show others, and I’ll have an archive of that. Now it’s time to find something new.

I did take some steps to prepare for a day I figured was coming.

Comments

Last Posterous Post… Time to Find a New Method…

Looks like folks finally decided to pull the plug on Posterous. It will be shutting down on April 30, some 13 months after Twitter acquired the platform, or rather the talent that built the platform.

I enjoyed using Posterous because it stayed out of my way… I didn’t feel the need to fiddle with the layout, or adjust the admin or mess around with plugins. It let me just write posts… Maybe not as many as I’d like to, but posts nonetheless. And I stuck with it to a certain degree.

Sadly, along with the blogging platform being gone, seems any Google juice I accumulated for things like Fusion Tables walkthroughs and examples will be gone as well… or at least pointing to dead links.

But then that might be for the better. I can say Posterous served me well at a certain point in my life, a time when I was learning to learn, code, program and teach/show others, and I’ll have an archive of that. Now it’s time to find something new.

I did take some steps to prepare for a day I figured was coming.

  • I formatted my projects page a bit so it’s a little more organized.

  • I grabbed an export of all of my content that Posterous housed. The Posterous backup made is really easy for me, and I think they way the structured the export should be a model for all content platform companies when they shutdown – ahem, Everyblock.

  • I started to move some learning resources and tutorials to Github and Gists.

  • And I started to research some other potential platforms. Wordpress is always there, but it’s too easy for me to get bogged down in how the site looks. Again, more writing and less fuss. Dave Stanton & Robert Hernandez just turned me on to Snipt, which looks promising as well.