脚本Google Sheets生成Google Maps图片并粘贴到单元格中

Question

我试图采取一个代码，并使其适应我的需要，但没有成功，因为我是新的脚本。

这个代码允许在几个地址上生成一个带有标记的谷歌地图，然后通过电子邮件发送它。在原来的代码中，有必要注明地点或地址。我想从标签的不同单元检索的地址。在我的代码中，有问题的单元格在引号之间，以字母k开头。然后我希望将映射插入到合并的单元格E15:M37或简单地E15的选项卡中，而不是通过电子邮件接收它。

我还在代码中指出了选项卡的名称，但我更希望onOpen()函数仅在活动选项卡中运行。

我的表:

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

我代码:

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('[e-mail]',  'Carte Groupe Ghena', '', {
attachments: [map],
})
}

我修改了代码，它工作，但只有当包含地址的单元格不是空的。只是碰巧它们是空的，所以脚本不执行。

下面是修改后的代码:

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

这是另一个社区的解决方案。如果代码能帮到别人，我就让它用。

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