Embedding a Map to a Cell using Custom Function on Spreadsheet

GasTips
Google Apps Script / spreadsheet / map

This sample script embeds a map to a cell using custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

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.

Solution

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.

Sample Script :

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() + '")'
  ]]);
}

Flow of Script :

embedMap()

  1. Input =embedMap("Tokyo station", 16) in cell B3. "Tokyo station" and 16 are a name of place and zoom, respectively.
  2. Using fetch(), sends data of "Tokyo station", 16 and the inputted coordinate to doGet().

doGet()

  1. Using doGet(), get the data.
  2. Creates the map from the name of place.
  3. Saves a map as an image. (in this case, saves as PNG)
  4. Updates a permission of the image file to use for =IMAGE().
  5. Embeds the address and image using =T() and =IMAGE() which was imported by setFormula().

Result :

By inputting =embedMap("Tokyo station", 16) in cell B3 as a custom function, following result can be obtained.

Note :

  1. When the custom function embedMap() is used, loading time is about 40 seconds. (I don’t know whether this occurs only my environment.)
  2. Permissions of the created image are ANYONE_WITH_LINK, VIEW.
  3. embedMap() is overwritten by =IMAGE(). So when the spreadsheet is reopened, the response of =IMAGE() is much faster than that of embedMap().

 Share!