Make a custom function in Google sheets script to work with "ArrayFormula"

Question

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

Tanaike · Accepted Answer · 2022-05-29 05:25:01Z

1

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()

answered May 29, 2022 at 5:25

Tanaike

204k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kal El Over a year ago

Amazing! kudos to you!

Tanaike Over a year ago

@Kal El Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Collectives™ on Stack Overflow

Make a custom function in Google sheets script to work with "ArrayFormula"

1 Answer 1

Modified script:

Reference:

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Modified script:

Reference:

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related