Google-sheets – How to get multiple driving times in Google sheets without “Service invoked error”

google apigoogle mapsgoogle sheetsgoogle-apps-script

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

Best Answer

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

Related Solutions

Google-sheets – How to avoid “invoked too many times per second” error

Don't use a custom formula--instead process all the values into an array then have the array written to the sheet. This will not adjust as your spreadsheet changes, but you should be able to modify this to look for the last value using a combination of the match and len functions in the sheet to id the starting and ending rows.

function straightToText() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var sheet = ss[0].getRange(1, 1, 9999, 1).getValues(); // get first sheet, a1:a9999
  var results = [];
  for (var i = 0; i < sheet.length; i++) {
    var hash = hash256(sheet[i][0]); // hash256 is the custom formula in this case
    results.push(hash);
  }
  for (var j = 0; j < results.length; j++) {
    var row = j+1;
    ss[0].getRange(row, 3).setValue(results[j]);  // write output to c1:c9999 as text
  }  
}

This works for a sheet processing 10k rows with no errors, where use of the custom formula in the sheet itself (i.e., =hash256(a1)) produces an error after ~500 rows.

Google-sheets – Prevent “invoked too many times per second” error in Google Apps Script

Better is to prepare a result for a range in stead of dealing with individual cell. When you open the file, it needs to process all the cells individually. This, in the end, evoked the error. Therefore I created the below code.

Code

function catExp_1(range) {
  var output = [], arr = JSON.parse(createArr());
  for(var j=0, jLen=range.length; j<jLen; j++) {
    var result = "Not Sure";
    for(var i=0, iLen=arr.length; i<iLen; i++) {      
      for(var k=0, kLen=arr[i][1].length; k<kLen; k++) {        
        if(range[j][0].indexOf(arr[i][1][k]) != -1) {
          result = arr[i][0];
        }
      }      
    }
    output.push([result]);
  }
  return output;
}

function createArr() {
  // check for availability
  var cache = CacheService.getPublicCache();
  var cached = cache.get("accounts");
  if (cached != null) {  
    return cached;
  }

  // create array
  var arr = [    
    [["5070 - Bank Charges"],["PURCHASE INTEREST", "xxxx"]],
    [["5530 - Postage Expense"],["CPC SCP","xxxx"]],
    [["9999 - xxxxxxx"],["xxxxxxxx"]]
  ];

  // add to cache
  var jsonData = JSON.stringify(arr);
  cache.put("accounts", jsonData, 21000);
  return jsonData;
}

Explained

The first script will create an output array and collect the lookup data. This is either stored in the cache or prepared on the fly (and stored in the cache for the next time). The iteration start with a category Not Sure. If an match is found, then the corresponding value will be push into the output array. Else the Not Sure will be added.

Usage

=catExp_1(B2:B133)

Note

You might want to consider adding the categories, rather than looking them up. This eliminates any delay from the script. You can easily create a menu item that does this.