I keep getting this error.

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

Here is the link to my sample sheet. With script attached

https://docs.google.com/spreadsheets/d/1RZWMcI1vJW551VmoqYj_Orpv5Cqvgi99Ot53J4hTMj4/edit?usp=sharing

This is the basic setup of my function. Is there any way to make this an array formula? Would that even help? Also once it has calculated the time I don't need it to recalculate.

function DrivingSeconds(origin, destination) {
  var directions = Maps.newDirectionFinder()
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();
  return directions.routes[0].legs[0].duration.value;  
}

I think it might be important to note that the sheet receives input from a form, which receives 10-30 responses a day. I also have to run about 16 separate sheets with the same setup. That really burns through the API allowance.


I added a counter to the function and it still seems to be running once every minute. Shouldn't this only count if "if" is true?

function DrivingSeconds() {
   var sheet = SpreadsheetApp.getActive().getSheetByName('ETA');
   var input = sheet.getRange("G2:G").getValues();
   var counter = sheet.getRange("K3").getValue();
   var output = sheet.getRange("J2:J").getValues();

     for (var i = 1; i < output.length; i++) {
       if (!output[i][0] && input[i-1][0] && input[i][0]) {
          output[i][0] = Maps.newDirectionFinder()
          .setOrigin(input[i-1][0])
          .setDestination(input[i][0])
          .getDirections()
          .routes[0].legs[0].duration.value;
          sheet.getRange("K3").setValue(counter++);
       }
     }
     sheet.getRange("J2:J").setValues(output);
 }
  • 1
    Don't be sorry about formatting -- fix it. You have an example right above yours in the post. You have a button "code" in the editor. There is a shortcut Ctrl-K for it. There is a page on formatting help. Don't be sorry; fix things. – user79865 Oct 21 '15 at 17:30
  • 1
    I checked in my spreadsheet with a counter: the Maps API is invoked only 3 times at the first run, and it stays at 3 after that (no additional calls). – user79865 Oct 21 '15 at 17:36
  • I have the function firing on change. It seems to be running even if the duration has been found. Can you explain this part to me? if (!output[i][0] && input[i-1][0] && input[i][0]) { – Sumguyinabox Oct 22 '15 at 17:22
  • 1
    Double index refers to ith row, 0th column (JavaScript has 0-based indices, unlike Sheets API itself). A string value is interpreted as "false" if the string is empty, and "true" otherwise. So, (!string1 && string2 && string3) means string 1 must be empty, strings2 and string3 nonempty. – user79865 Oct 22 '15 at 17:37
  • 1
    For debugging purposes, consider using Logger.log(output[i][0]), etc. This records those values in the log, accessible through the script editor menu or by pressing Ctrl-Enter – user79865 Oct 22 '15 at 17:38
up vote 2 down vote accepted

I suppose the error message refers to the Maps service. Then converting to array formula won't help since you will still be calling Maps as many times. Here are some things you can do:

Don't call the Maps service unnecessarily. Right now, you are calling it a dozen times with empty destination and origin. Check that those are nonempty strings beforehand.

Switch from a custom function to a function that's triggered by form submission. That is, instead of putting =DrivingSeconds(...) in the spreadsheet, add a trigger that will call it on form submission. Here is how this function can look like

function DrivingSeconds() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var input = sheet.getRange("A2:A").getValues();
  var output = sheet.getRange("B2:B").getValues();
  for (var i = 1; i < output.length; i++) {
    if (!output[i][0] && input[i-1][0] && input[i][0]) {
      output[i][0] = Maps.newDirectionFinder()
      .setOrigin(input[i-1][0])
      .setDestination(input[i][0])
      .getDirections()
      .routes[0].legs[0].duration.value;
    }
  }
  sheet.getRange("B2:B").setValues(output);
}

Notice that Maps API is invoked only if we have both origin and destination but do not have duration yet. Output:

+---+-------------------------------------------------+-----+
|   |                        A                        |  B  |
+---+-------------------------------------------------+-----+
| 1 | Address                                         | ETA |
| 2 | 5020 IMPERIAL AVENUE, SAN DIEGO, CA,92113       |     |
| 3 | 4699 EL CAJON BLVD, SAN DIEGO, CA,92115         | 604 |
| 4 | 8620 LA MESA BLVD, LA MESA, CA,91942            | 674 |
| 5 | 2820 VIA ORANGE WAY #Q, SPRING VALLEY, CA,91978 | 638 |
+---+-------------------------------------------------+-----+

Another option, if you want to eliminate spreadsheet range references inside your function, is to use caching, e.g.:

function DrivingSeconds(origin, destination) {
  if (origin && destination) {
    var cache = CacheService.getDocumentCache();
    var key = origin + '|' + destination;
    var value = cache.get(key);
    if (!value) {
      value = Maps.newDirectionFinder()
        .setOrigin(origin)
        .setDestination(destination)
        .getDirections()
        .routes[0].legs[0].duration.value;
      Utilities.sleep(1000);
      cache.put(key, value);
    }
    return value;
  }
  return -1;
}

The CacheService.getDocumentCache() will keep track of values that have already been calculated, and the Utilities.sleep(1000) will avoid overloading the service if a number of the values change at once.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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