Leaflet marker data from google spreadsheet

Show data fetched from a google spreadsheet. I've made an example spreadsheet with the tallest 10 mountains. You can fetch such data with a JSONP URL (requires 'publish to web'). The code loops through the JSON, pulls out some named columns (including latitude and longitude), and arranges markers on a leaflet map. Changes made to the spreadsheet are reflected here. Easily maintainable map markers data!

<html>
<head>
   <title>Leaflet marker data from google spreadsheet</title>
   
   <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
		
   <style>
   html, body, #map {
      height:100%;
      width:100%;
      padding:0px;
      margin:0px;
   } 
   </style>

// JSONP formatted "feed" URL from the google spreasheet
       var url = "https://spreadsheets.google.com/feeds/list/" +
                 sheetID + "/od6/public/values?alt=json-in-script" + 
                 "&callback=processJSONPData";
          
       console.log("Calling JSONP URL: " + url);
     
       //Make a JSONP request by loading it as a <script>
       var script = document.createElement('script');
       script.src = url;
       document.getElementsByTagName('head')[0].appendChild(script);
       //(should result in a call to processJSONPData) 
     }
     
     function processJSONPData(data) {
       console.log("Got Response");  
       
       var group = new L.featureGroup();
       var entries = data.feed.entry;
       
       //Loop through spreadsheet entries
       for (var i=0; i<entries.length; i++) {
         var entry = entries[i];
         
         //Assuming we have columns with these names
         var rank = entry['gsx$rank']['$t'];
         var lon = entry['gsx$longitude']['$t'];  
         var lat = entry['gsx$latitude']['$t'];
         var title = entry['gsx$name']['$t'];
         var height = entry['gsx$heightm']['$t'];
         var linkURL = entry['gsx$linkurl']['$t'];
         var imageThumbnail = entry['gsx$imagethumbnail']['$t'];

// Do what we want to do with the data.
         // So let's build some HTML for the marker popup
         if (title=="") title = "no name";
         var popupText = "<a href='" + linkURL + "' target='_top'>" + title + "</a><br>\n" +
                         height + " metres<br>" +
                         "<img src='" + imageThumbnail + "' width='150'>";
           
         var markerLocation = new L.LatLng(lat, lon);
         var marker = new L.Marker(markerLocation);
         group.addLayer(marker); 
        
         marker.bindPopup(popupText);
        
       }
       map.addLayer(group);

//Zoom to encompase all markers
       map.fitBounds(group.getBounds().pad(0.05));
     }
   </script>
   
</head>

view directly

Home
Blog
About
Maps
Other
Contact

Harry Wood

Leaflet marker data from google spreadsheet