The code below won't work as with "ArrayFormula" for example:

"=ArrayFormula(GOOGLEMAPS_DISTANCE(a2:a,b2:b.,"driving"))"

isn't working, how can I convert this custom function to work with "ArrayFormula"?

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String} origin The address of starting point
 * @param {String} destination The address of destination
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {String} The distance in miles
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();

  if (!data) {
    throw new Error('No route found!');
  }

  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  return distance;
};

CodePudding user response：

I believe your goal is as follows.

• You want to use your script with ARRAYFORMULA.

In this case, how about modifying your script using the sample script of this thread?

Modified script:

const GOOGLEMAPS_DISTANCE_ = (origin, destination, mode) => {
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error('No route found!');
  }
  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  return distance;
};

const GOOGLEMAPS_DISTANCE = (origin, destination, mode) => {
  if (origin.map) {
    return origin.map((b, i) => GOOGLEMAPS_DISTANCE(b, destination[i], mode));
  } else if (origin && destination) {
    return GOOGLEMAPS_DISTANCE_(origin, destination, mode);
  }
  return null;
}

• When this modified script is used, please put a function of =ARRAYFORMULA(GOOGLEMAPS_DISTANCE(A2:A,B2:B,"driving")) to a cell. I think that in this modification, you can use both =ARRAYFORMULA(GOOGLEMAPS_DISTANCE(A2:A,B2:B,"driving")) and =GOOGLEMAPS_DISTANCE(A2:A,B2:B,"driving").
  • In your showing formula of =ArrayFormula(GOOGLEMAPS_DISTANCE(a2:a,b2:b.,"driving")), I think that . of b2:b. is required to be removed. Please be careful this.

Reference:

• map()