I am supporting CandidateX

CandidateX is a startup that focuses on creating inclusion-focused hiring solutions, designed to increase access to job opportunities for underestimated talent. Check them out if you have a few minutes to spare. They need visibility!

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            }          }


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.


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){
        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;
        var marker = new google.maps.Marker({
          position: new google.maps.LatLng(d.lat,d.lon),
          title: d.title,
        if (d.markerUrlx) {
           marker.icon= d.markerUrl;
  mapOverlay.addOverlay = function () {
  mapOverlay.removeOverlay = function () {
   * 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) {
    // 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)  
  return mapOverlay;
}) (MapOverlay || {});


google.maps.event.addDomListener(window, 'load', function () {
    .withFailureHandler(function(err) {
      document.getElementById('error').innerHTML = err;
    .withSuccessHandler(function (data) {
        // initialize
        MapOverlay.initialize( {
          // change this stuff to describe your overlay 
          overlay: {
            sw: {
          // show the map
        // show the markers
        MapOverlay.addMarkers (data);
      // 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";


'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) {
      .addItem('Show map overlay', 'showMapOverlay')
 * 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) {
 * Opens a sidebar. 
function showMapOverlay() {
  var ui = HtmlService.createTemplateFromFile('index.html')
      .setTitle('Map overlay');


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


<!-- 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">
.spinner {  
    margin-left: -14px;
    margin-top: -14px;
<!-- Page content -->
<input type="checkbox" id="option-overlay" checked>
<label for="option-overlay">
    Show overlay
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