#Generate Static Google Maps in Google Sheets - Add distinct colors and labels to markers

#Generate Static Google Maps in Google Sheets - Add place markers

Generate Static Google Maps in Google Sheets - Add distinct colors and labels to markers

Code.gs

/**
 * Create Static Google Maps with place markers from Google Sheets data
 * Add distinct colors and labels for each marker category
 * bpwebs.com
 */

function createStaticGoogleMap(){
  const ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const range = ss.getActiveRange();
  const data = range.getValues();
  const catCol = 0; //CHANGE: The column number of the data range that holds the marker categories.

  //CREATE AN UNIQUE CATEGORY LIST
  let categories = [];
  for(let i=0; i < data.length; i++){
    categories.push(data[i][catCol]);
  }
  categories = [... new Set(categories)]; //Create an array of uniqe values from category coloumn


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

  for(let j=0; j < categories.length; j++){
    let markerColor = getRandomColor();
    for(let k=0; k<data.length; k++){
      if(data[k][catCol]==categories[j]){
        map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, markerColor, String.fromCharCode(markerLetterCode));
        map.addMarker(data[k][3]);
        markerLetterCode++;
      }
    }
  }
  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();
}