#Generate Static Google Maps in Google Sheets - Add paths

#Generate Static Google Maps in Google Sheets - Add paths

Generate Static Google Maps in Google Sheets - Add paths

Code.gs

/**
 * Create Static Google Maps with place markers from Google Sheets data
 * Create a Static Google Map with paths
 * bpwebs.com
 */

function createStaticGoogleMap(){
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const range = ss.getActiveRange();
  const data = range.getValues();

  let map = Maps.newStaticMap().setSize(600,400);
  let markerLetterCode = 'A'.charCodeAt();

  for(let i=0; i<data.length; i++){
    let directions = Maps.newDirectionFinder()
              .setOrigin(data[i][1])
              .addWaypoint(data[i][2])
              .setDestination(data[i][3])
              .setMode(Maps.DirectionFinder.Mode.DRIVING)
              .getDirections();

    let route = directions.routes[0];

    for(let j=0; j<data[i].length; j++){
      let markerColor = getRandomColor();
      map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, markerColor, String.fromCharCode(markerLetterCode));
      map.addMarker(data[i][j]);
      markerLetterCode++;
    }
    map.addPath(route.overview_polyline.points);
  }
  ss.insertImage(map.getBlob(),5,3);
}

/**GET RANDOM COLOR IN RGBA FORMAT */
function getRandomColor() {
  var r = Math.floor(Math.random() * 256); // Random value for the red component
  var g = Math.floor(Math.random() * 256); // Random value for the green component
  var b = Math.floor(Math.random() * 256); // Random value for the blue component
  var a = Math.random().toFixed(2); // Random value for the alpha channel (opacity)

  return '0x' + ('00' + r.toString(16)).slice(-2) + 
          ('00' + g.toString(16)).slice(-2) +
          ('00' + b.toString(16)).slice(-2) + 
          ('00' + Math.round(a * 255).toString(16)).slice(-2);
  }

/**CREATE CUSTOM MENU TO RUN THE SCRIPT */
function onOpen(){
  SpreadsheetApp.getUi().createMenu('My Menu')
    .addItem('Create Static Map','createStaticGoogleMap')
    .addToUi();
}