Quicklinks and Categories

Services‎ > ‎Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation‎ > ‎Integrating Excel with Maps and Earth‎ > ‎Mapping and adding markers to maps‎ > ‎

Navigating using quicklinks and categories get it now

This article will cover 3 main topics

How to add a quicklink navigation combo box
How to add a category filter to make points visible or not
How to create a bounding box

Try it out first ?

For a summary step by step explanation see How to add quickLinks to a Map
If you just want a to try it, then you can download an example application (googleMapping.xlsm) and come back here later. First you should take a look at Adding markers to Google Maps from Excel since this is just an enhancement of that.

See also Zooming and Bounds for how to change the scale of the map automatically on selecting particular categories

Category Filter

The javascript necessary for these enhancements is created from Excel as usual, and a new column is available to categorize your placemarkers. You can have as many categories as you want, and they can be whatever text you want. You can also have multiple columns of categories. Here is the example setup in the VenueMaster Tab of googleMapping.xlsm. Im using two category filters - venue size and country.

You will also see that the geoCoding Parameter Sheet needs to know the name of these columns if you are going to use filtering. You can change the column name to a list of columns that you want to filter on.

This generates this on the map

Selecting a category will hide all the markers apart from those belonging to the filters selected. The filters are additive.

If you dont want or need this, you can modify the body code in the geoCoding parameters sheet to remove the <DIV> - combocategory

<body onload="initialize()">
<div id="comboquicklink" class = "mcquicklink">Quick Links</div>
<div id="combocategory" class = "mccategory"></div>
<div id="heading" class ="mcheading">Excel and Google Mapping Integration</b>

<a href="http://rambings.mcpher.com">
ramblings.mcpher.com</a></div>
<div id="map_canvas" style="width: 100%; height: 90%"></div>

</body>

QuickLink

The quicklink combox box contains a shortcut to each marker. Selecting a marker will navigate to that marker and bring up its infowindow

If you dont want or need this, you can modify the body code in the geoCoding parameters sheet to remove the <DIV> - comboquicklink

Starting on a particular category

If you want to start with a particular category selected, you can add a column to your data that looks like this. The first category name it comes across in this column will be the one on which it opens the map/ Note that if this applies to only the first of your category filters (you may have multiple).

Bounding Box

A bounding box attempts to set an appropriate zoom level for the data being shown - here is the code.

     var cj = mcpherData.cJobject;
       var bounds = new google.maps.LatLngBounds();
     for ( var i = 0 ; i < cj.length;i++) {
      bounds.extend (new google.maps.LatLng(cj[i].lat,
      cj[i].lng));
     }

     if (!qparams['zoom']) qparams['zoom'] = 2;
     myOptions['zoom'] = parseInt(qparams['zoom']);
    // create the map
     gMap = new google.maps.Map(document.getElementById("map_canvas"),
         myOptions);
     gMap.fitBounds(bounds);

Complete Code

Here is the code, including the data, that is generated from Excel for the VenueMaster Example

<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
<style type="text/css">
html { height: 100% }
body {
    height: 100%;
    margin: 0;
    padding: 0 ;
    color: Gray ;
    background-color: WhiteSmoke;
}
#map_canvas { height: 100%}

.mcquicklink {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mccategory {
margin-left: 20px;
margin-right: 20px;
display: inline;
font-family: sans-serif;
float: right;
}
.mcheading {
display: inline;
font-family: sans-serif;

}
</style>
<script type="text/javascript"
    src="http://maps.googleapis.com/maps/api/js?sensor=false">
</script>
<script type="text/javascript">

function mcpherDataPopulate() {
var mcpherData =   {
   "cJobject":[      {
         "title":"Rosemount theatre",
         "content":"\<b\>Rosemount theatre\</b\>\<br\>Rosemont\<br\> IL\<br\> USA\<br\>",
         "lat":"41.9891",
         "lng":"-87.871474",
         "color":"#20FF00",
         "size":"500000",
         "category":"big",
         "image":"http://maps.google.com/mapfiles/marker_orange.png"
      },
      {
         "title":"UNO Lakefront Arena",

         "content":"\<b\>UNO Lakefront Arena\</b\>\<br\>6801 Franklin Ave\<br\> New Orleans\<br\> LA 70122\<br\> USA\<br\>",

         "lat":"30.029015",
         "lng":"-90.0522531",
         "color":"#0000FF",
         "size":"200000",
         "category":"small",
         "image":""
      },
      {
         "title":"Intex",

         "content":"\<b\>Intex\</b\>\<br\>Nankokita\<br\> Suminoe Ward\<br\> Osaka\<br\> Osaka Prefecture 559-0034\<br\> Japan\<br\>",

         "lat":"34.6381228",
         "lng":"135.4253848",
         "color":"#00609F",
         "size":"300000",
         "category":"small",
         "image":""
      },
      {
         "title":"olympic park",

         "content":"\<b\>olympic park\</b\>\<br\>Olympic Park\<br\> 88 Bangi-dong\<br\> Songpa-gu\<br\> Seoul\<br\> South Korea\<br\>",

         "lat":"37.5198209",
         "lng":"127.1227901",
         "color":"#80FF00",
         "size":"600000",
         "category":"big",
         "image":""
      },
      {
         "title":"aktiv square",

         "content":"\<b\>aktiv square\</b\>\<br\>Ban Mai\<br\> Bang Yai\<br\> Nonthaburi 11140\<br\> Thailand\<br\>",

         "lat":"13.8448335",
         "lng":"100.3610179",
         "color":"#FF0000",
         "size":"1000000",
         "category":"big",
         "image":""
      },
      {
         "title":"red square",
         "content":"\<b\>red square\</b\>\<br\>Red Square\<br\> Moscow\<br\> Russia\<br\>",
         "lat":"55.7546345",
         "lng":"37.6214955",
         "color":"#0000FF",
         "size":"200000",
         "category":"small",
         "image":""
      },
      {
         "title":"heineken hall",

         "content":"\<b\>heineken hall\</b\>\<br\>Bullewijk\<br\> Arena Boulevard 590\<br\> 1101 Zuidoost\<br\> The Netherlands\<br\>",

         "lat":"52.3123574",
         "lng":"4.9441509",
         "color":"#00609F",
         "size":"300000",
         "category":"small",
         "image":""
      }
    ]
};
return mcpherData; }

function initialize() {
    mcpherData= mcpherDataPopulate();
    if (mcpherData.cJobject.length > 0) {
     var myOptions = {
        center: new google.maps.LatLng(mcpherData.cJobject[0].lat, mcpherData.cJobject[0].lng),
        mapTypeId: google.maps.MapTypeId.ROADMAP
     };

    // get parameters if any
     var qparams = mcpherGetqparams();

     var cj = mcpherData.cJobject;
       var bounds = new google.maps.LatLngBounds();
     for ( var i = 0 ; i < cj.length;i++) {
      bounds.extend (new google.maps.LatLng(cj[i].lat,
      cj[i].lng));
     }

     if (!qparams['zoom']) qparams['zoom'] = 2;
     myOptions['zoom'] = parseInt(qparams['zoom']);
    // create the map
     gMap = new google.maps.Map(document.getElementById("map_canvas"),
         myOptions);
     gMap.fitBounds(bounds);

    // add the excel data

     for ( var i = 0 ; i < cj.length;i++) {
       mcpherAddMarker ( cj[i]);
     }

    // Set up combox
     quickLink();
    category();
    }
}

function mcpherAddMarker(cj) {
var p = new google.maps.LatLng(cj.lat,cj.lng);

if (cj.size) {
      var circle = {
         strokeColor: cj.color,
         strokeOpacity: 0.8,
         strokeWeight: 1,
         fillColor: cj.color,
         fillOpacity: 0.20,
         map: gMap,
         center: p,
         radius: parseFloat(cj.size),
      };
     var drawCirle =
          new google.maps.Circle(circle);
}

var marker = new google.maps.Marker({
      position: p,
      map: gMap,
      title: cj.title
   });
   cj.marker = marker;
   infowindow=null;
   if(cj.image)marker.setIcon(cj.image);
   if (cj.content){
      infowindow = new google.maps.InfoWindow({
          content: cj.content
     });
     google.maps.event.addListener(marker, 'click', function() {
         infowindow.open(gMap,marker);
     });
   }
   cj.infowindow =infowindow;
   return marker;
}

function mcpherGetqparams(){
     var qparams = new Array();
var htmlquery = window.location.search.substring(1);
var htmlparams = htmlquery.split('&');
for ( var i=0; i < htmlparams.length;i++) {
   var k = htmlparams[i].indexOf('=');
   if (k > 0) qparams[ htmlparams[i].substring(0,k) ] = decodeURI(htmlparams [i].substring(k+1));
   return qparams;
}
   }

function dealWithQuickLink(selValue) {
    var cj = mcpherData.cJobject[selValue];
     if (cj.infowindow) {
     cj.infowindow.open(gMap,cj.marker);
     }
    return cj.infowindow;
}
function dealWithCategories(selValue) {
     var cats = createCategories();
    var cj = mcpherData.cJobject;
      for (i=0; i < cj.length;i++ ) {
        cj[i].marker.setVisible (selValue ==0 || cats[selValue]== cj[i].category);
      }
    return cats[selValue];
}
function quickLink() {
var comboElem = document.getElementById('comboquicklink');
if (comboElem) {
        var selElem = document.createElement('select');
    var cj = mcpherData.cJobject;
    mcpherAddEvent (selElem,"change",function(){
        dealWithQuickLink(selElem.value);
        },false ,true) ;
    for ( var i=0; i < cj.length;i++){
        var o = document.createElement('option');
           o.text = cj[i].title;
        o.value = i;
        try {
            selElem.add(o,null);
        }
        catch(error) {
            selElem.add(o);
        }
    }
    comboElem.appendChild(selElem);
}
}
function createCategories() {
    var cats =[];
     cats.push('all categories');
    var cj = mcpherData.cJobject;
    for ( var i=0; i < cj.length;i++){
        if (cats.indexOf(cj[i].category) == -1 ) cats.push(cj[i].category);
    }
    return cats;
}
function category() {
var comboElem = document.getElementById('combocategory');
if (comboElem) {
    var selElem = document.createElement('select');
     var cats = createCategories();
    mcpherAddEvent (selElem,"change",function(){
        dealWithCategories(selElem.value);
        },false ,true) ;
    for ( var i=0; i < cats.length;i++){
        var o = document.createElement('option');
           o.text = cats[i];
        o.value = i;
        try {
            selElem.add(o,null);
        }
        catch(error) {
            selElem.add(o);
        }
    }
    comboElem.appendChild(selElem);
}
}
function mcpherAddEvent (o,e,f,b,complain){
// because IE is different
if(o.addEventListener)
   return(o.addEventListener(e, f, b));

else
   if (o.attachEvent)
    return(o.attachEvent('on' + e, f));
   else
    if (complain)
     alert ('browser doesnt support events');

return(null);

}
var mcpherData;
var gMap;
</script>
</head>

<body onload="initialize()">
<div id="comboquicklink" class = "mcquicklink">Quick Links</div>
<div id="combocategory" class = "mccategory">Category Filter</div>
<div id="heading" class ="mcheading">Excel and Google Mapping Integration</b>

<a href="http://rambings.mcpher.com">
ramblings.mcpher.com</a></div>
<div id="map_canvas" style="width: 100%; height: 90%"></div>

</body>

In the meantime why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available, or read more about Overlaying circles and heatmaps or Mapping and adding markers to maps

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly. 
If you prefer Video style learning I also have two courses available. also published by O'Reilly.
 Google Apps Script for Developers and Google Apps Script for Beginners.

Comments

Desktop Liberation

Navigation

Other stuff

Site owners

License

Recent site activity