Custom script "invoked too many times" error

Question

Credit: Google Spreadsheets function to find distance between cities

function getDirection(city1, city2, unit) {
  var directions = Maps.newDirectionFinder()
    .setOrigin(city1).setDestination(city2)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .getDirections();
  var d = directions.routes[0].legs[0].distance.text;

  var distance, value = d.split(" ")[0].replace(",", ""), text = d.split(" ")[1];
  if(text == unit) {
    distance = value;
  } else if(text == "km" && unit == "mi") {
    distance = value / 1.6;
  } else {
    distance = value * 1.6;
  }
  return Math.round(distance);
}

I have used this code snippet in one of my sheets. I am working on a dataset with roughly 6000 entries and I am getting some errors because of the large volume.

Script invoked too many times per second for this Google user account. (line 0).

Service invoked too many times for one day: route. (line 4).

I considered purchasing a Google Apps business account to increase my quota, but that might not even help. Do you maybe have any advice for me in this regard?

Answer 1

Instead of using a Google Sheet's custom functions, consider to use Fusion Tables.

Also could be helpful to read Map your data with the Maps API and Fusion Tables - Google Geo Developers Blog

Why? or regarding errors, Google Sheet's custom functions, purchasing a Google Apps for Work account...

From Quotas for Google Services - Apps Script - Google Develpers

Script invoked too many times per second for this Google user account. This indicates that the script began executing too many times in a short period. It most commonly occurs for custom functions that are called repeatedly in a single spreadsheet. To avoid this error, code your custom functions so that they only need to be called once per range of data, as explained in the guide to custom functions.

From "guide to custom functions" Custom Functions in Google Sheets - Apps Script - Google Developers

Optimization

Each time a custom function is used in a spreadsheet, Google Sheets makes a separate call to the Apps Script server. If your spreadsheet contains dozens (or hundreds, or thousands!) of custom function calls, this process can be quite slow.

Pay special attention to the following excerpt from the same previous referred source

Consequently, if you plan to use a custom function multiple times on a large range of data, consider modifying the function so that it accepts a range as input in the form of a two-dimensional array, then returns a two-dimensional array that can overflow into the appropriate cells.

Again from Quotas for Google Services - Apps Script - Google Develpers (just replace Calendar by route)

Service invoked too many times: Calendar. This indicates that the script called the given service too many times in one day.