The main requirement is to find travel time with traffic data between two locations or zip-code.

Inputs parameters would be from Location, destination, arrival time(this is between 6AM 8AM), mode of transportation, traffic model.

Based on the above input parameters in my google script function it should return travel time but I'm getting an error that

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

and I'm using the custom function below and there are 40 rows in my sheet:

  function GetDurationNormal(location1, location2, mode) {
  //var arrive = new Date(new Date().getTime() + (10 * 60 * 60 * 1000));//arrive in ten hours from now
 // var arrive=new Date(2019, 09, 07, 06);// 7th of September 2019 06:00 am
  var arrive = new Date();
  var directions  = Maps.newDirectionFinder().setArrive(arrive)
  // Get a script lock, because we're about to modify a shared resource.
  var lock = LockService.getScriptLock();
  // Wait for up to 30 seconds for other processes to finish.
 return directions.routes[0].legs[0].duration.text;

The problem is that each formula that use your custom function triggers the execution of a new instance of the related script when the spreadsheet is recalculated.

The obvious way to avoid to trigger so much script execution instances is to reduce the number of formulas that use the custom function, the way to get the required number of results is to make the custom function to be able to get as argument / return as result an array of values.

For further details see Using Custom Functions in Google Sheets


  • Could you modify the script ? Is it something you can help with ? Unfortunately I'm not so good with scripting skills – Heisenberg Sep 16 '19 at 17:10
  • 2
    @PathumAnjana I don't think that I will be able to do it today. I think that this is a frequent question so maybe there could be another post with the code you are looking for on this site or on Stack Overflow. – Rubén Sep 16 '19 at 17:13

Not the answer you're looking for? Browse other questions tagged or ask your own question.