How to add an array to this google app script?

Question

I'd like to call this custom formula in Google sheets +100 times. Can I add an array to this script?

function GOOGLEMAPS(start_adress,end_adress) {
  
  
  var mapObj =  Maps.newDirectionFinder();
  mapObj.setOrigin(start_adress);
  mapObj.setDestination(end_adress);
  var directions = mapObj.getDirections();
  
 
  var meters = directions["routes"][0]["legs"][0]["distance"]["value"];
  var distance = meters/1000;
  
  return distance;
      
  
}

It's not clear what you mean. What do you want the array to do? — Cooper, Nov 09 '20 at 21:46
I made a custom function to calculate the distance between 2 locations. I want to make this calculation numerous times. In order to avoid exceeding the data quota I hoped to solve this with an array. Anyway my question is answer bij Rubén. Thanks for responding! — Jean-Paul Peek, Nov 10 '20 at 07:53

score 3 · Answer 1 · edited Nov 10 '20 at 08:18

You might add an array but this could easily lead to exceed the quota of the number calls being done in a short period of time, also it could exceed the maximum execution time for a custom function (30 seconds) so the advise is to not do that when using the Map Service.

Anyway, you could send an array to a custom function by useing a A1:B2 style reference justlimit the number of distances to be calculated in order to prevent the errors mentioned above.

function GOOGLEMAPS(arr) {
 var output = [];

 for(var i = 0; i < arr.length; i++){ 

  var start_address = arr[i][0];
  var end_adress = arr[i][1];

  var mapObj =  Maps.newDirectionFinder();
  mapObj.setOrigin(start_adress);
  mapObj.setDestination(end_adress);
  var directions = mapObj.getDirections();
  
 
  var meters = directions["routes"][0]["legs"][0]["distance"]["value"];
  var distance = meters/1000;
  
  output.push([distance]);
      
  }
  return output;
}

Resource

https://developers.google.com/apps-script/guides/sheets/functions#guidelines_for_custom_functions

Google Apps Script - How to get driving distance from Maps for two points in spreadsheet

How to add an array to this google app script?

1 Answers1