Convert String Lat/Long to Number for Google Maps API

When sending Lat/Long values to the Google Maps API for something like setCenter(), it wants an number value. But the value we pull from an option menu like the one below is a string.

<select id="searchString" onchange="zoomToAddress(this.value);">
<option value="">--Select All--</option>
<option value="43.138092,-89.747988">Luckenbooth Cafe</option>
<option value="43.017218,-89.831479">Aunt Mary's Hooterville Inn</option>

So we want to convert the above strings to numbers that the Maps API can use. We’ll place the following inside a function called zoomToAddress.

First we get the value from the option menu as a string and set it to a variable.

var searchString = document.getElementById('searchString').value;

Then we set a variable to find the position of the comma in the string.

var commaPos = searchString.indexOf(',');

Use parseFloat to conver the string to a number while parsing out the Latitude value.

var coordinatesLat = parseFloat(searchString.substring(0, commaPos));

And we do the same for the Longitude.

var coordinatesLong = parseFloat(searchString.substring(commaPos + 1, searchString.length));

Those variables are combined into a new google maps LatLng pair.

var centerPoint = new google.maps.LatLng(coordinatesLat, coordinatesLong);

Then we can use the setCenter() method from the Maps API.


Link: Teaching Yourself to Code for the Web

via Noah Veltman

I’m not big on offering advice when it comes to learning how to code. Everyone learns differently and has different goals; my experience isn’t necessarily instructive. But I seem to be getting asked the same question more and more often: someone wants to be able to make cool things for the web, and they don’t know how to get started. Here are some thoughts on how to keep your head on straight while you’re trying to learn. Take them all with a big grain of salt.


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 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 = '';
        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

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 =;
            var lng = bestResult.geometry.location.lng;
            var latLng = lat + ',' + lng;
            var addressTargetCellData = ss.getRange(lastRow, 9).setValue(latLng);
        } else {
            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);

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

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(){




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.

  • I found the constraints to be helpful. Outside of the learning, the setup was a breeze. And because we could only do so much with this setup, 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.

Link: Meetings Are Toxic

via Getting Real

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.