Search Route and Embedding Map using Custom Function on Spreadsheet

GasTips
Google Apps Script / spreadsheet / map

This sample script is for searching route between place A and B and embedding a map by custom function on Spreadsheet.

I think that this method is one of various ideas.

Problem

When the map is embedded to a cell on spreadsheet as an image, the function =IMAGE() is suitable for this situation. However, Class Maps, 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. This sample uses UrlShortener API. So before use this, please enable UrlShortener API at Advanced Google Services and console API.

var folderId = '### Folder ID ###'; // Please input this.

function getroute(pos1, pos2, mode) {
  var ac = SpreadsheetApp.getActiveSheet().getActiveCell();
  var q1 = '?pos1=' + pos1;
  var q2 = '&pos2=' + pos2;
  var q3 = '&mode=' + mode;
  var q4 = '&row=' + ac.getRow();
  var q5 = '&col=' + ac.getColumn();
  UrlFetchApp.fetch(ScriptApp.getService().getUrl() + q1 + q2 + q3 + q4 + q5);
}

function doGet(e) {
  var data = createMap(e.parameters.pos1, e.parameters.pos2, e.parameters.mode);
  if (data) {
    try {
      var blob = UrlFetchApp.fetch(data[0]).getBlob().setName(e.parameters.pos1 + '_' + e.parameters.pos2);
      var file = DriveApp.getFolderById(folderId).createFile(blob);
      file.setSharing(DriveApp.Access.ANYONE_WITH_LINK, DriveApp.Permission.VIEW);
      SpreadsheetApp
      .getActiveSheet()
      .getRange(e.parameters.row, e.parameters.col, 1, 4)
      .setFormulas([[
        '=T("' + data[1].duration.value + '")',
        '=T("' + data[1].distance.value + '")',
        '=T("' + data[0] + '")',
        '=IMAGE("' + 'http://drive.google.com/uc?id=' + file.getId() + '")',
      ]]);
    } catch(m) {
      err(e.parameters.row, e.parameters.col);
    }
  } else {
    err(e.parameters.row, e.parameters.col);
  }
}

function createMap(pos1s, pos2s, mode) {
  var geo = Maps.newGeocoder();
  var pos1 = geo.geocode(pos1s).results[0];
  var pos2 = geo.geocode(pos2s).results[0];
  var map = Maps.newStaticMap().setSize(1000, 1000).setLanguage('en');
  var directions = Maps.newDirectionFinder()
  .setOrigin(pos1.geometry.location.lat, pos1.geometry.location.lng)
  .setDestination(pos2.geometry.location.lat, pos2.geometry.location.lng)
  .setMode(function(mode) {
    switch (mode) {
      case 'transit': return Maps.DirectionFinder.Mode.TRANSIT; break;
      case 'driving': return Maps.DirectionFinder.Mode.DRIVING; break;
      case 'walking': return Maps.DirectionFinder.Mode.WALKING; break;
      case 'bicycling': return Maps.DirectionFinder.Mode.BICYCLING; break;
      default : return Maps.DirectionFinder.Mode.DRIVING; break;
    }
  }(mode))
  .setDepart(new Date())
  .getDirections();
  map.setPathStyle(5, Maps.StaticMap.Color.GREEN, null);
  map.beginPath();
  directions.routes.forEach(function(e1) {
    e1.legs.forEach(function(e2) {
      e2.steps.forEach(function(e3) {
        map.addPath(Maps.decodePolyline(e3.polyline.points));
      });
    });
  });
  map.endPath();
  map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.RED, 'A').addMarker(pos1s);
  map.setMarkerStyle(Maps.StaticMap.MarkerSize.MID, Maps.StaticMap.Color.BLUE, 'B').addMarker(pos2s);
  try {
    return [UrlShortener.Url.insert({longUrl: map.getMapUrl()}).id, directions.routes[0].legs[0]];
  } catch(e) {
    return null;
  }
}

function err(row, col) {
  SpreadsheetApp
    .getActiveSheet()
    .getRange(row, col, 1, 2)
    .setFormulas([[
      '=T("Route was not found.")',
      '=T("Route was not found.")',
  ]]);
}

Flow of Script :

getroute()

  1. Input =getroute(A2, B2, C2) in cell D2.
  2. Using fetch(), sends data of "Tokyo station", "Ginza station", "transit" and the inputted coordinate to doGet().

doGet()

  1. Using doGet(), get the data.
  2. Searches the route from “Tokyo station” to “Ginza station”.
  3. Creates the map with the route.
  4. Saves a map as an image. (in this case, saves as PNG)
  5. Updates a permission of the image file to use for =IMAGE().
  6. Embeds the text data and image using =T() and =IMAGE() which was imported by setFormula().

Result :

By inputting =getroute(A2, B2, C2) in cell D2 as a custom function, following result can be obtained.

Although this demo movie seems short loading time, this loading time is edited. Actual loading time is about 40 seconds.

Note :

  1. When the custom function getroute() 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.

 Share!