Scrypt and google sheets with google maps

[Mikhailov Vladimir]

Hello all!

I have next task. Calculate the distance between start/end points.

So, I'm using google sheets where I have a data on the points. I added to that sheets some script, where I added some function which takes a data of points from google sheet and sends them to google maps service and returned a distance in miles. 

So, everything is working and it's cool except one moment!!!

When I calculate for many counts of ways(200...300), happens some mystics. For 1 way it's counted, for second no, but if restart a script, 1st which already returned a value of the distance, saying that it's error. some mistakes. I have a question - how I can calculate distance in many ways.

maybe I should to wrap it in a circle, like while won't calculate the 1st value, won't begin next???? I just don't know in which order count all functions from different cells(((

Please, see attachment, where I added some screenshot of my script and sheet.

my script:

+++++++++++++++++++++++++++++++++++++++

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('My Custom Menu')

      .addItem('Calculate Miles', 'GoogleDistance')

      .addToUi();

}

/**

*Get distance between 2 point

*@param startPoint Adress as String of First point

*@param endPoint Adress as String of Second point

*@param distance Return distance between 2 points in Miles. Type as String!

*@customfunction

*/

function GoogleDistance(startPoint, endPoint) {

  

  //startPoint = "Laredo, TX "

  //endPoint = " Purcell, OK"

  

  var mapObj = Maps.newDirectionFinder();

     mapObj.setOrigin(startPoint);

     mapObj.setDestination(endPoint);

     mapObj.setRegion('us');

     //mapObj.setMode(Maps.DirectionFinder.Mode.WALKING); // default mode - DRIVING

  var directions = mapObj.getDirections();

  

  Logger.log(directions["routes"][0]["legs"][0]["distance"]["value"]);

  

  var meters = directions["routes"][0]["legs"][0]["distance"]["value"];

  var distance = meters * 0.000621371;

   

  return distance;

}

+++++++++++++++++++++++++++++++++++++++

[Thea Vega (AdWords Scripts Team)]

Hi Mikhailov,

AdWords Scripts currently does not support Google Maps Service. For your concern, it's best to contact Apps Scripts Team support as they are more equipped with this.

If you haven't, you may follow our blog for updates and new releases.

Thanks,

Thea

AdWords Scripts Team