2

I have the following script which is being used in a spreadsheet to calculate the driving distance between two cities or a city and a zip code of another city. It is being run for approximately 25 locations simultaneously. To better explain, I have cell B3 in which I enter a new city every time. The script is then used in cells adjacent to my 25 plant locations to calculate the distance from each of my plants to the variable city.

It uses google sheets built in mapping api and works on 80% of the calculations but returns "TypeError: Can Not Read Property "legs" from undefined. (line 16). The plants that it fails on vary with every new city so its not like it is for certain locations. It is almost like the api times out before it completes some of them. I split it into two separate scripts with a varied name and that worked for a day but then 20% fail again.

To make things slightly more odd, I have another script that sorts the plants based on closest distance to the variable address. When you sort the plants, even the ones with errors go to their correct location based on distance. So it is like the distance script is obtaining the correct disance but displaying the error anyways.

Clear as mud? Would love any input I could get on how to correct the issue or an alternate mapping api that could solve my problems.

function distancecalcone(origin,destination) {

  var directions = Maps.newDirectionFinder()

    //Set the Method of Transporation. The available "modes" are WALKING,    DRIVING, BICYCLING, TRANSIT.
    .setMode(Maps.DirectionFinder.Mode.DRIVING)

    //Set the Orgin
    .setOrigin(origin)

    //Set the Destination
    .setDestination(destination)

    //Retrieve the Distance
    .getDirections();
    return directions.routes[0].legs[0].distance.value/1609.34;
}
geocodezip
  • 158,664
  • 13
  • 220
  • 245
jnohl
  • 67
  • 1
  • 6
  • I have still been unable to solve this problem. Does anyone else have any tips? I am wondering if it is not failing because it is calling too many times per second (even though that is not the error I am getting). Any advice on how I could layer the calls so that they didn't all call at the exact same time and had a second delay or something? The distance matrix also looks like something that may work but I do not know how to implement it. – jnohl Jun 23 '15 at 19:49

4 Answers4

2

Have you tried using a try-catch block around directions.routes[0].legs[0].distance.value ?

try{
    return directions.routes[0].legs[0].distance.value/1609.34;
} 
catch (e){
 console.log("error",e)
}

or you could try something like this

alert(directions);
alert(directions.routes[0]);
alert(directions.routes[0].legs[0]);
alert(directions.routes[0].legs[0].distance);
alert(directions.routes[0].legs[0].distance.value);

and so on...to find out which one comes up as undefined the first. That might help you to debug the issue.

melis
  • 1,145
  • 2
  • 13
  • 30
1

Enable Direction Api

1)Go to "google cloud platform" 2)go to "Api and services" 3)search for "direction api" and enable it

Syed Awais
  • 11
  • 1
0

The directions service is subject to a quota and a rate limit. Check the return status before parsing the result.

For lots of distances (or at least more than 10), look at the DistanceMatrix.

geocodezip
  • 158,664
  • 13
  • 220
  • 245
  • I don't believe it is running into the quota or rate limit as I have that happen a time or two and it states it in the error usually. Could you further explain your two suggestions though? – jnohl Jun 12 '15 at 14:17
0

I'm able to run the script from the Script editor, but not from spreadsheet. The error is "unable to read property legs" when the function is called from spreadsheet. But the property is in place when called from Script editor and contain correct values.

You probably need to use WEB API and have API KEY: Google Apps Script - How to get driving distance from Maps for two points in spreadsheet

Milan Kerslager
  • 404
  • 2
  • 6