我嘗試獲取代碼并使其適應我的需求，但沒有成功，因為我是腳本新手。
此代碼允許在多個地址上生成帶有標記的谷歌地圖，然后通過電子郵件發送。在原始代碼中，必須標明地點或地址。我希望從選項卡的不同單元格中檢索地址。在我的代碼中，有問題的單元格在引號之間并以字母 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('[email protected]', '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)
}
uj5u.com熱心網友回復：
這是另一個社區的解決方案。如果它可以幫助其他人，我會讓代碼。
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)
}
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/469157.html