Maps.newDirectionFinder() in Google Sheets Shows Loading Error

Question

I have the following code:

function CALCULATE_DRIVING_DISTANCE(origin, destination) {
  var directions = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();
    
  var distance = directions.routes[0].legs[0].distance.value;

  distance /= 1000; // Convert meters to kilometers
  return Number (distance.toFixed(2));
}

I use it within Google Sheets to calculate the distance between two points. This worked for a few hundred rows but after that, I get an error that just says "Loading".

Google Sheets Error

As you can see from the image, some records before and after it are ok. Does anyone have any idea what could be happening and how to resolve this issue?

Sometimes the same origin/destination works, sometimes it fails. I ran the execution log but it only seemed to run when I ran the function when in Apps Script and not when the function ran within the worksheet.

How do I get debugging information when the function is run in the worksheet?

I have the paid version of workspace - how can I see the billing for it? I was well within the quota, even for consumer.

Thanks

Answer 1

The custom function Loading... issue is a longstanding bug in Google Sheets.

It usually helps to have a few less formulas that call the custom function. To do that, use a custom function that processes whole columns of origin and destination addresses in one go and uses caching. See GoogleMapsDistance() for one implementation.

An array-enabled custom function will show Loading... much less often, but does nothing to fix the underlying issue. You may want to star issue 233124478 in the issue tracker. Click the star icon ☆ in the top left-hand corner to vote for fixing the issue and get notified of status changes. Please do not post a "me too" or "+1" reply, but just click the star icon. Google prioritizes issues with the most stars.