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

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

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.

CodePudding user response：

To make this function array compatible,

Check if it's a array and map it's elements
If it's not a array, directly pass the elements
Cache service may also be used to avoid making repeated calls to maps.

/**
 * @param {string} origin
 * @param {string} destination
 */
function MILEAGE_(origin, destination) {
  try {
    const sCache = CacheService.getScriptCache();
    const key = `${origin}_${destination}`;
    const cached = sCache.get(key);
    if (cached) return Number(cached);
    Utilities.sleep(1);
    const directions = Maps.newDirectionFinder()
      .setRegion('US')
      .setOrigin(origin)
      .setDestination(destination)
      .getDirections();
    const route = directions.routes[0].legs[0];
    const distance = route.distance.value * 0.000621371;
    sCache.put(key, String(distance), 21600);
    return distance;
  } catch {
    return '#ERROR';
  }
}

/**
 * @param {A1:D1} arr
 * @param {A1} param1
 * @param {C1} param2
 * @customfunction
 */
const mileage = (...arr) =>
  Array.isArray(arr[0]) ? arr[0].map(e => MILEAGE_(...e)) : MILEAGE_(...arr);

Usage:

=MILEAGE("origin","destination")
=MILEAGE(A1,B1)
=MILEAGE(A1:B100)