How do I get multiple driving times in Google sheets without "Service invoked error"?

Question

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);
 }

Answer 1

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 |
+---+-------------------------------------------------+-----+

Answer 2

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.