CODEWITHSUNDEEP
google-mapsgoogle-apps-scriptgoogle-sheetsgoogle-sheets-formulacustom-function
Jean-Paul Peek
Jean-Paul Peek

Reputation: 11

How to add an array to this google app script?

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

Upvotes: 1

Views: 116

Answers (1)

Wicket
Wicket

Reputation: 38180

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

Related

Other related

Upvotes: 3

Related Questions