Plotting maps with overlays Sheets add-on starter

This isn’t a completed add-on but rather a proof of concept that may give you some hints on how to make your own.

You quite often see campus layouts, or site plans or some other drawn data that are an approximation of a real place. It would be good to be able to put markers on such a layout driven off Sheet data. I searched for a random map of this nature and found this one, which is a proposed (or actual) development somewhere in Utah.

The next step was to find the location on maps, and get the approximate coordinates of the south-west and north-east limits represented by the plan. That allowed me to describe the plan like this, where I’m hosting the image on Google Drive, and when I place it on top of a map. I want it to be somewhat transparent so I’m giving it an opacity of 60%. overlay: { sw: { lat:41.774000, lon:-111.779700 }, ne:{ lat:41.780400, lon:-111.776700 }, img:{ src:'https://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/image/lots.png', opacity:0.6 } }

Page Content hide

1 The data

2 Since this is using the Maps API live, if you prefer..

10 Share with your network

The data

In addition to overlaying this plan on maps, I also wanted to plot a few spots – in this case representing building lots, using regular and custom maps markers. The add-on in action looks like this. In addition to overlaying this plan on maps, I also wanted to plot a few spots – in this case representing building lots, using regular and custom maps markers. The add-on in action looks like this.

Since this is using the Maps API live, if you prefer..

Or you can drag the little yellow streetview guy over your marker to get a picture or even …

The data

The data for the points is read from the associated sheet and looks like this. Not specifying a marker will use the default one, otherwise you’ll get whatever image it links to. The map is automatically scaled to the overlay image, and centered around it.

latitude	longitude	title	markerUrl
41.7785	-111.77845	sold	cyan circle
41.7794	-111.77899	under construction	Yellow circle
41.77562	-111.77933	for sale	Green circle
41.7788	-111.77741	development of 52 houses in phase 2

The code

This is pretty straightforward, although you would need to clean it up a bit for a real add-on.

mapoverlay.js.html

var MapOverlay = (function(mapOverlay){
  'use strict';
  /**
  *called to initialize the map 
  *@param {object} userSettings the settings
  *@return {MapOverlay} self for chainging
  */
  mapOverlay.initialize = function (userSettings) {
    // clone because we might tweak
    mapOverlay.settings = JSON.parse(JSON.stringify(userSettings));
    // if there are margins around the overlay then we'll size the map to show info around the image
    if (mapOverlay.settings.overlay && !mapOverlay.settings.zoom) {
      // set the map boundaries using the margins
      mapOverlay.fitBounds = [ 
        // top left
        new google.maps.LatLng(mapOverlay.settings.overlay.ne.lat  , mapOverlay.settings.overlay.sw.lon ),
        // bottom right
        new google.maps.LatLng(mapOverlay.settings.overlay.sw.lat  , mapOverlay.settings.overlay.ne.lon )
      ].reduce (function (p,c){
        p.extend(c);
        return p;
      },new google.maps.LatLngBounds());
    }
    // center the image on the map if no center is given
    if (!mapOverlay.settings.map.center) {
      if (mapOverlay.settings.overlay.ne && mapOverlay.settings.overlay.sw) {
        mapOverlay.settings.map.center = { 
          lat: (mapOverlay.settings.overlay.ne.lat - mapOverlay.settings.overlay.sw.lat)/2 + mapOverlay.settings.overlay.sw.lat,
          lon: (mapOverlay.settings.overlay.ne.lon - mapOverlay.settings.overlay.sw.lon)/2 + mapOverlay.settings.overlay.sw.lon
        };
      }
    }
    return mapOverlay;
  };
  mapOverlay.addMarkers = function (data) {
      mapOverlay.data = data;
      mapOverlay.data.forEach(function(d){
        var marker = new google.maps.Marker({
          position: new google.maps.LatLng(d.lat,d.lon),
          title: d.title,
          icon:d.markerUrl,
          map:mapOverlay.map
        });
        if (d.markerUrlx) {
           marker.icon= d.markerUrl;
        }
      });
  }
  mapOverlay.addOverlay = function () {
    mapOverlay.overlay.setMap(mapOverlay.map);
  };
  mapOverlay.removeOverlay = function () {
    mapOverlay.overlay.setMap(null);
  }
  /**
   * set up map & overlay & render
   */
  mapOverlay.render= function() {
    var settings = mapOverlay.settings;
    // set up te options
    var mapOptions = {
      center: new google.maps.LatLng(settings.map.center.lat, settings.map.center.lon),
      mapTypeId: google.maps.MapTypeId.SATELLITE
    };
    if (settings.map.zoom) {
      mapOptions.zoom = settings.map.zoom;
    }
    // create a map
    mapOverlay.map = new google.maps.Map(document.getElementById(settings.map.canvas), mapOptions);
    // maybe there's amargin calulation
    if(mapOverlay.fitBounds) {
      mapOverlay.map.fitBounds(mapOverlay.fitBounds);
    }
    // if there's an overlay then show it
    if (settings.overlay) {
      // position of overlay
      var swBound = new google.maps.LatLng(settings.overlay.sw.lat, settings.overlay.sw.lon);
      var neBound = new google.maps.LatLng(settings.overlay.ne.lat, settings.overlay.ne.lon);
      var bounds = new google.maps.LatLngBounds(swBound, neBound);
      // show it
      mapOverlay.overlay = new google.maps.GroundOverlay(settings.overlay.img.src,bounds);
      mapOverlay.overlay.setOpacity (settings.overlay.img.opacity)  
      mapOverlay.addOverlay();
    }
  };
  return mapOverlay;
}) (MapOverlay || {});

main.js

 spinCursor();
google.maps.event.addDomListener(window, 'load', function () {
   google.script.run
    .withFailureHandler(function(err) {
      document.getElementById('error').innerHTML = err;
      resetCursor();
    })
    .withSuccessHandler(function (data) {
        // initialize
        MapOverlay.initialize( {
          // change this stuff to describe your overlay 
          map:{
            zoom:0,
            canvas:'map-canvas'
          },
          overlay: {
            sw: {
              lat:41.774000,
              lon:-111.779700
            },
            ne:{
              lat:41.780400,
              lon:-111.776700
            },
            img:{
              src:'https://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/image/lots.png',
              opacity:0.6
            }
          }
        });
          // show the map
        MapOverlay.render();
        // show the markers
        MapOverlay.addMarkers (data);
        resetCursor();
       })
      .getData();
      // flip the overlay
      document.getElementById('option-overlay').addEventListener ('change', function (e) {
        return e.target.checked ? MapOverlay.addOverlay() : MapOverlay.removeOverlay(); 
      });
 });
 function resetCursor() {
   document.getElementById ('spinner').style.display = "none";
 }
 function spinCursor() {
   document.getElementById ('spinner').style.display = "block";
 }

addon.js

'use strict';
/**
 * Adds a custom menu with items to show the sidebar and dialog.
 *
 * @param {Object} e The event parameter for a simple onOpen trigger.
 */
function onOpen(e) {
  SpreadsheetApp.getUi()
      .createAddonMenu()
      .addItem('Show map overlay', 'showMapOverlay')
      .addToUi();
}
/**
 * Runs when the add-on is installed; calls onOpen() to ensure menu creation and
 * any other initializion work is done immediately.
 *
 * @param {Object} e The event parameter for a simple onInstall trigger.
 */
function onInstall(e) {
  onOpen(e);
}
 /**
 * Opens a sidebar. 
 */
function showMapOverlay() {
  var ui = HtmlService.createTemplateFromFile('index.html')
      .evaluate()
      .setSandboxMode(HtmlService.SandboxMode.IFRAME)
      .setTitle('Map overlay');
  SpreadsheetApp.getUi().showSidebar(ui);
}

server.js

/**
* called to return latest active sheet data
* @return {object} object the data
*/
function getData () {
    var sheet = SpreadsheetApp.getActiveSheet();
    var range = sheet.getDataRange();
    var data = range.getValues();
    // change it to an object
    var headings = data.shift();
    // need some error handling here
    return data.map(function(d) {
        var ob = {};
        d.forEach (function (e,i) {
            ob[headings[i]] = e;
        });
        return ob;
    });
}

index.html

<!-- This CSS package applies Google styling; it should always be included. -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<meta name="viewport" content="initial-scale=1.0, user-scalable=no">
<meta charset="utf-8">
<style>
.spinner {  
    position:absolute;
    top:50%;
    left:50%;
    width:28;
    height:28;
    z-index:1000;
    margin-left: -14px;
    margin-top: -14px;
    display:none;
}
</style>
<!-- Page content -->
<input type="checkbox" id="option-overlay" checked>
<label for="option-overlay">
    Show overlay
</label>
For more info on how to do this see <a href="http://ramblings.mcpher.com">Desktop liberation</a>
    <img src="https://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/image/ajax-loader.gif"/>
<?!= HtmlService.createHtmlOutputFromFile('mapoverlay.js').getContent(); ?>
<?!= HtmlService.createHtmlOutputFromFile('main.js').getContent(); ?>

Let me know if you build something nice with this. You can try it out here, but try not to screw up the test data for others… The code has been automatically posted to github using Getting your apps scripts to Github.
Subpages