Google Script as Webservice

Recently I had the need to calculate the driving time between several locations dynamically in an Excel sheet. Luckily there’s teh Google Maps API available. Unfortunately it returns JSON and thus needs to be parsed to get the result in minutes only.

So I needed to implement a parser in between which should read the JSON result of the Google Maps API and return only the pure minutes. The possibilities are endless but I wanted to be fast (I’m coding for results and not its own sake) and robust without utilizing an overloaded rest-framework or having to take care for the maintenance and hosting in future. So I decided to test Google Script which is offering the use as standalone Web-App.

With implementing a function doGet you can create a simple webservice which can be called via GET. All parameters are accessible via the variable e:

function doGet(e) {
  var format    = "json";

  var url = 'http://maps.googleapis.com/maps/api/distancematrix/'
    + format
    + '?origins=' + e.parameter.origins
    + '&destinations=' + e.parameters.destinations;

  var response = UrlFetchApp.fetch(url);  
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  return ContentService.createTextOutput(data.rows[0].elements[0].duration.text);
}

After only 14 lines of code (okay, there is no exception handling implemented) the app is ready for being published:

Now the App es exposed to the web. Via access control you have the chance to limit the access only to yourself (when singned to your Google Account), to other authenticated users or even to everyone anonymously.

Consumption is than fairly easy. You can call the URL attaching the GET-parameters via „?param1=value1&param2=value2“ and so on. But if you remember my initial problem, I needed to calculate several distances in a spreadsheet. For that Google Spreadsheets makes it even more awesome:

Google offers the functionality „IMPORTDATA“ as formula to import data from a given URL into your cells.

IMPORTDATA(URL)

With that and concatenation you can easily build up the URL for the webservice call including the origin and destination as paramaters.

---

 

By the way: You can do even more fancy stuff with Google Script. E.g. by setting triggers, you can easily build your own Cronjobs.