Use a custom formula with arrayformula for distances between two columns of cities in Google Sheets with App Scripts

Question

I have a custom formula in Google Apps Scripts and Google Sheets that gives the distance between two cities. EXAMPLE SHEET

=MILEAGE(A2,B2)

function MILEAGE(origin,destination) {
  Utilities.sleep(0);
var directions = Maps.newDirectionFinder()
  .setRegion('US')
  .setOrigin(origin)
  .setDestination(destination)
  .getDirections();

var route = directions.routes[0].legs[0];
var distance = (route.distance.value) * 0.000621371;

return distance
}

The formula works great for two cells, but I need it to work like an arrayformula for two rows of cities for each row independently. I have weighed the pros and cons of running the script once with an array and returning an array and it is not effective in this application because I do not want the entire range to re-calculate every time one row is added/changed.

The objective is for it to work like this:

=arrayformula(if(A2:A<>"", MILEAGE(A2:A, B2:B), ""))

Any suggestions on how to do this, or better ways to accomplish the same goal will be appreciated.

Answer 1

The issue is that, when you use pass a range in a custom function, it doesn't pass the range, but the values.

What I recommend is that you use a trigger specifically onEdit so when you are editing a specific cell, it only recalculates that row. I also made it so that if you remove or add multiple location, they update the corresponding rows.

Script:

function onEdit(e) {
  var sheet = e.source.getActiveSheet();
  var range = e.range;
  var row = range.getRow();
  var column = range.getColumn();
  var lastRow = range.getLastRow();

  for(var i = 0; i <= lastRow - row; i++)
    if (row + i > 1 && (column == 1 || column == 2) && sheet.getName() == "Sheet1") {
      var origin = sheet.getRange(row + i, 1).getValue();
      var destination = sheet.getRange(row + i, 2).getValue();

      if(origin && destination)
        sheet.getRange(row + i, 3).setValue(calculateMileage(origin, destination));
      else 
        sheet.getRange(row + i, 3).clearContent();
    };
}

function calculateMileage(origin, destination) {
  var directions = Maps.newDirectionFinder()
    .setRegion('US')
    .setOrigin(origin)
    .setDestination(destination)
    .getDirections();

  var route = directions.routes[0];
  if (route) {
    var legs = route.legs[0];
    var distance = (legs.distance.value) * 0.000621371;

    return distance;
  }
  else {
    return "no route found"
  }
}

Output:

Note:

• I found a cell that returns error due to invalid location or no route found so I tried catching it.
• If a row doesn't have a value on either cell, it will delete remove the distance column.