0
343
ViewsScript Google Sheets error because cell is empty
I tried to take a code and adapt it to my needs but without success because I am a novice in scripting.
This code allows to generate an image of a Google Maps with markers for each address and a line that connects them. At first this code was for 2 addresses that we had to indicate in the code and the generated image was sent by email.
So I rewrote the code (my first one alone) to plot and mark the points of 6 addresses and then have the generated image pasted into a cell of my sheet.
The problem I have is that I get the error "Exception: Invalid argument: address" when at least one of the cells containing the addresses does not contain a value (so the value is an address). I need to add something after the .getValue() function but I can't.
I know that the answer to this question may seem simple to the advanced script users, but also that this code could be useful to others.
Thank you for your insight and indulgence.
My Sheets:
My Sript:
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)
}
2 answers
Answer question0
This will loop through all your addresses and check whether they are empty or not and add to the map only the ones with a non empty address:
function onOpen() {
SpreadsheetApp.getUi().createMenu('⇩ M E N U ⇩')
.addItem('👉 Générer les Cartes', 'myFunction')
.addToUi();
myFunction()
}
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('AVRIL 22');
var addresses = sheet.getRange('K121:K126').getValues().flat();
var colors = [ Maps.StaticMap.Color.GREEN,Maps.StaticMap.Color.BLUE,
Maps.StaticMap.Color.BLUE,Maps.StaticMap.Color.BLUE,
Maps.StaticMap.Color.BLUE,Maps.StaticMap.Color.RED];
var map = Maps.newStaticMap().setLanguage('fr')
.setSize(846,479)
.setMapType(Maps.StaticMap.Type.HYBRID);
addresses.forEach((address,i)=>{
if(address!=''){
map.addMarker(address)
.setMarkerStyle(Maps.StaticMap.MarkerSize.MID,colors[i],(i+1).toString())
.addMarker(address)
}
});
map = map.endPath().getBlob();
spreadsheet.insertImage(map,5,15)
}
Another reason it does not work now is that because you have invalid addresses:
put correct values and let me know if the code works.
0
The code of soMarios was working nice but markers colors was not correct. 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)
}
}