Google Forms Interacting with Google Maps

The challenge as initially laid out was to let students log the miles they walk and have those miles show their progress from Richmond VA to Key West FL on a map.

I found quite a few ways not to do this- mostly because I was attacking the problem in the wrong way. I wanted to set a point of origin, a destination direction and then map a straight line for X amount of miles. It may be possible but it feels like you’re fighting the system the whole way. I took some time off and let it marinate in my head and a much easier solution appeared.

Goal

In the end I realized I wanted to get the path from Richmond to Key West and then be able to add a marker to that path at any mileage point along the way. The miles would be pulled from Google Form submissions.

One of the key helpers was epoly.js . It adds a number of tricks which come in handy- including the option to GetPointAtDistance along a path.

I used this example for find the midpoint of a route as the starting template. I figured I’d leave the midpoint as a goal and then all I needed to do was add another marker that was placed based on the sum of the miles entered via the Google Form.

Make a New Marker

Because the example already had a function (createMarker) for making markers I was able to add my marker with the following code. distanceCovered is the variable I’ll grab from the form. Initially, I just set it to 500 to make sure it’d all work.

markerCovered = createMarker(polyline.GetPointAtDistance(distanceCovered),Math.round(distanceCovered/1609.34)+" miles so far!", "Keep moving."); 

For those you playing along (and as clueless as me), this particular function has three variables- (latlng, label, html) – that is the latitude and longitude of the point, the title for the point, and the content of the info window. When I use it above, I get the lat/long using the GetPointAtDistance in combination with the distanceCovered. In my head, the scripts asks “Where do I put this marker?” I respond by saying “Ask GetPpointAtDistance. I told him to tell you know what the lat/long for 500 miles from here is . . . “¹

function createMarker(latlng, label, html) {
    var contentString = '<b>'+label+'</b><br>'+html;
    var marker = new google.maps.Marker({
        position: latlng,
        map: map,
        title: label,
        zIndex: Math.round(latlng.lat()*-100000)<<5
        });
        marker.myname = label;
        // gmarkers.push(marker);

    google.maps.event.addListener(marker, 'click', function() {
        infowindow.setContent(contentString+"<br>"+marker.getPosition().toUrlValue(6)); 
        infowindow.open(map,marker);
        });
    return marker;
}

Getting the Distance from Google Sheets

First make a form and have it log whatever but make at least one item the numbers you want.

Create another sheet in the form and make a formula to add up all those numbers. In my case, my numbers were in column B and so the formula was =sum(‘Form Responses 1’!B:B). That’s a nice formula to remember as it’ll add everything in column B no matter how many responses you get. So now that we have our

Getting json from Google sheets is different than how it once was. This Stack Overflow post helped me out.

My summary- publish your results sheet to the web (file>publish to web) AND share it with anyone who has the link (view only). The URL is https://spreadsheets.google.com/feeds/list/UNIQUEDOCID/PAGEOFTHESS/public/basic?alt=json
or in my case this . . .
https://spreadsheets.google.com/feeds/list/159pgfoFFNOlFDj_jnE6-St_QwkwIyBwlprJBOopWmlM/2/public/basic?alt=json

Now that we can see the json in all its glory, we just need to grab one tiny piece. How to do that is delineated below.

//GET GOOGLE SHEETS DATA POINT
var xmlhttp = new XMLHttpRequest();
//document ID
var gid = '159pgfoFFNOlFDj_jnE6-St_QwkwIyBwlprJBOopWmlM';
//page of the spreadsheet you want
var gpage = '2';
//build the url to get the json from your sheet
var url = "https://spreadsheets.google.com/feeds/list/"+gid+"/"+gpage+"/public/basic?alt=json";
xmlhttp.onreadystatechange=function() {
    if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
        myFunction(xmlhttp.responseText);
    }
}
xmlhttp.open("GET", url, true);
xmlhttp.send();

//returns json 
function myFunction(response) {
    var json = JSON.parse(response);
    //the particular json element you want
    distanceCovered = json.feed.entry[0].title.$t;
    //changing miles to meters because meters is how folks roll 
    distanceCovered = distanceCovered*1609.34;
    console.log(distanceCovered);
}

Sidebar Stuff

//turn the meters back to miles and make them a whole number
      totalDist = Math.round(totalDist/1609.34);
      distLeft = Math.round(totalDist-(distanceCovered/1609.34));
      document.getElementById("total").innerHTML = 'You need to walk <span class="highlight">'+ totalDist + ' miles</span> to get to Key West.</br></br>So far, your team has logged <span class="highlight">' + Math.round(distanceCovered/1609.34) + ' miles</span>.<br/></br> Only <span class="highlight">'+ distLeft +' miles</span> to go!<br/><a href="https://docs.google.com/forms/d/1HzObtk5xLbMe_BgbMw156xe0QZ9lWSVQu2--1z1Kj98/viewform">Go log some more</a>';
//      document.getElementById("totalTime").value = (totalTime/60.).toFixed(2);
      }

Here’s the result. I crammed the webpage in with an iframe but it’s here if you’d prefer. It updates fast but you’ll need to refresh the page.