This sample script embeds a map to a cell using custom function on Spreadsheet.
I think that this method is one of various ideas.
When the map is embeded to a cell on spreadsheet as an image, the function =IMAGE()
is suitable for this situation. However, setFormula()
for importing =IMAGE()
and DriveApp.createFile()
for creating images from maps also cannot be used for custom functions.
In order to avoid these limitations, I used Web Apps. From previous research, it has been found that Web Apps can avoid various limitations. Also in the case of this situation, Web Apps could avoid the above limitations.
To use this sample script, please deploy Web Apps as follows.
On the Script Editor,
- File
- -> Manage Versions
- -> Save New Version
- Publish
- -> Deploy as Web App
- -> At Execute the app as, select "your account"
- -> At Who has access to the app, select "Anyone, even anonymous"
- -> Click "Deploy"
- -> Copy "Current web app URL"
- -> Click "OK"
When it deploys Web Apps, the approval required authorization can be done, simultaneously.
Please copy and paste this script to a bound script of spreadsheet.
var folderId = "### Folder ID ###";
function embedMap(range, zoom) {
var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
var q1 = "?address=" + range;
var q2 = "&zoom=" + zoom;
var q3 = "&row=" + ac.getRow();
var q4 = "&col=" + ac.getColumn();
UrlFetchApp.fetch(ScriptApp.getService().getUrl() + q1 + q2 + q3 + q4);
}
function doGet(e) {
var data = Maps.newGeocoder().geocode(e.parameters.address).results[0];
var file = DriveApp.getFolderById(folderId).createFile(
Maps.newStaticMap()
.setSize(800, 600)
.setCenter(data.geometry.location.lat, data.geometry.location.lng)
.setZoom(parseInt(e.parameters.zoom, 10))
.setLanguage("en")
.getBlob()
.getAs('image/png')
.setName("map.png")
);
file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
SpreadsheetApp
.getActiveSheet()
.getRange(e.parameters.row, e.parameters.col, 1, 2)
.setFormulas([[
'=T("' + data.formatted_address + '")',
'=IMAGE("' + "http://drive.google.com/uc?id=" + file.getId() + '")'
]]);
}
embedMap()
- Input
=embedMap("Tokyo station", 16)
in cellB3
."Tokyo station"
and16
are a name of place and zoom, respectively. - Using
fetch()
, sends data of"Tokyo station", 16
and the inputted coordinate todoGet()
.
doGet()
- Using
doGet()
, get the data. - Creates the map from the name of place.
- Saves a map as an image. (in this case, saves as PNG)
- Updates a permission of the image file to use for
=IMAGE()
. - Embeds the address and image using
=T()
and=IMAGE()
which was imported bysetFormula()
.
By inputting =embedMap("Tokyo station", 16)
in cell B3
as a custom function, following result can be obtained.
- When the custom function
embedMap()
is used, loading time is about 40 seconds. (I don't know whether this occurs only my environment.) - Permissions of the created image are ANYONE_WITH_LINK, VIEW.
embedMap()
is overwritten by=IMAGE()
. So when the spreadsheet is reopened, the response of=IMAGE()
is much faster than that ofembedMap()
.