Script Google Sheets to generate Google Maps picture and paste it into a cell

Question

I tried to take a code and adapt it to my needs but without success as I am new in script.

This code allows to generate a google map with markers on several addresses and then to send it by email. In the original code, it was necessary to indicate the places or the addresses. I would like the addresses to be retrieved from different cells of the tab. In my code, the cells in question are between quotation marks and begin with the letter K. Then I would like the map to be inserted in the merged cells E15:M37 or simply E15 of the tab instead of receiving it by email.

I have also indicated the name of the tab in the code but I would prefer that the onOpen() function only runs in the active tab.

My Sheets:

https://docs.google.com/spreadsheets/d/1eZUlQK3-4WZmhQAIw5BTLsKkbTUheCTsdKEhdG0k87E/edit#gid=1047347094

My code:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Générer les Cartes', 'myFunction')
    .addToUi();
  myFunction()
}

function myFunction() {
  const ss = SpreadsheetApp.getActive();

  const map = Maps.newStaticMap()
  .setSize(882,500)
  .setMapType(Maps.StaticMap.Type.SATELLITE)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN,'1')
  .addMarker("K121")
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'2')
  .addMarker("K122")
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'3')
  .addMarker("K123")
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'4')
  .addMarker("K124")
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'5')
  .addMarker("K125")
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.RED,'6')
  .addMarker("K126")
  .setPathStyle(3, Maps.StaticMap.Color.BLACK, Maps.StaticMap.Color.BLACK)
  .beginPath()
  .addAddress("K121")
  .addAddress("K122")
  .addAddress("K123")
  .addAddress("K124")
  .addAddress("K125")
  .addAddress("K126")
  .endPath()
  .getBlob()

  GmailApp.sendEmail('[email protected]',  'Carte Groupe Ghena', '', {
    attachments: [map],
  })
}

I modified the code and it works but only when the cells containing the addresses are not empty. Only it happens that they are empty and so the script does not execute.

Here is the modified code:

function onOpen() {
  SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
    .addItem('👉 Générer les Cartes', 'myFunction')
    .addToUi();
  myFunction()
}

function myFunction() {
  var sheet = SpreadsheetApp.getActive();
  var address1 = sheet.getRange('K121').getValue();
  var address2 = sheet.getRange('K122').getValue();
  var address3 = sheet.getRange('K123').getValue();
  var address4 = sheet.getRange('K124').getValue();
  var address5 = sheet.getRange('K125').getValue();
  var address6 = sheet.getRange('K126').getValue();

  var map = Maps.newStaticMap().setLanguage('fr')
  .setSize(846,479)
  .setMapType(Maps.StaticMap.Type.HYBRID)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN,'1')
  .addMarker(address1)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'2')
  .addMarker(address2)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'3')
  .addMarker(address3)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'4')
  .addMarker(address4)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,'5')
  .addMarker(address5)
  .setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.RED,'6')
  .addMarker(address6)
  .beginPath()
  .addAddress(address1)
  .addAddress(address2)
  .addAddress(address3)
  .addAddress(address4)
  .addAddress(address5)
  .addAddress(address6)
  .endPath()
  .getBlob()
  sheet.insertImage(map,5,15)
}

Answer 1

Here is the solution from another comunity. I let the code if it can help someone else.

function onOpen() {
  SpreadsheetApp.getUi().createMenu('? M E N U ?')
    .addItem('? Générer les Cartes', 'myFunction')
    .addToUi();
  myFunction()
}

function myFunction() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  //var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Testing")

   // Deletes all images in sheet
    var images = sheet.getImages();
    images.map(function(img){img.remove();});

  var cards = []

  //gets the addresses of each card into an array
  for(i=0; i<16; i++)
  {
    cards.push(sheet.getRange(121, 11 + (15*i), 6, 1).getValues().flat())
  }

  //loop through the array of cards
  for(c=0; c < cards.length; c++){

   //create a new map
   var map = Maps.newStaticMap().setLanguage('fr')
  .setSize(846,479)
  .setMapType(Maps.StaticMap.Type.HYBRID)

  //remove blanks from card addresses
  var card = cards[c].filter(j => j)

  //begin a new path for the map
  map.beginPath()

  //loop through card addresses
  for(n=0; n < card.length; n++){

    //add the nth address to the map
    map.addAddress(card[n])

    //if first address, create new green marker (note n +1 due to array starting from 0 not 1)
    if(n == 0){

      map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.GREEN,n+1)
      var marker = map.addMarker(card[n])
    }
    //if last address, create new red marker
    else if(n == card.length - 1){
      map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.RED,n+1)
      var marker = map.addMarker(card[n])
    } 
    //if any other address create blue marker
    else{
      map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE,n+1)
      var marker = map.addMarker(card[n])
    }
  } 
  
  //end the path and insert the map image to the sheet
  map.endPath()
  map.getBlob()
  sheet.insertImage(map,5+ (15*c),15)
  }
}