Custom functions for Google Sheets to geocode addresses, calculate directions between addresses, and fetch place metadata.

GoogleMapsSheetsFunctions.js

/*
 *
 * Google Maps Formulas for Google Sheets
 *
 * Written by Amit Agarwal + Eric Domke
 *
 * Web: https://labnol.org/google-maps-formulas-for-sheets-200817
 *
 */

/**
 * Fetch the HTML <meta> tags from a URL.
 *
 * =FETCH_META_TAGS("https://www.google.com/maps/place/?q=place_id:ChIJKwu26ht19YgRQqtIZhg4tEI", "description")
 *
 * @param {String|String[][]} url The URL to load
 * @param {String|String[][]} name The name of the <meta> tag.
 * @return {String|String[][]} The tag content
 * @customFunction
 */
const FETCH_META_TAGS = (url, name) => permutate_([url, name], (url, name) => {
  const response = UrlFetchApp.fetch(url);
  const html = response.getContentText();
  const tags = extractMetaTags_(html);
  if (name === '*')
    return JSON.stringify(tags);
  return tags[name] ?? "";
});

/**
 * @param {string} html
 * @returns {Object<string, string>}
 */
const extractMetaTags_ = (html) => {
  const xml = `<root>${[...html.matchAll(/<meta[^>]+/gi)].map(m => m[0] + "/>").join('')}</root>`;
  return XmlService.parse(xml).getRootElement().getChildren('meta')
    .reduce((dict, elem) => {
      const content = elem.getAttribute('content').getValue();
      const name = elem.getAttributes().filter(a => a.getName() !== 'content')[0].getValue();
      dict[name] = content;
      return dict;
    }, {});
}

const md5_ = (key = "") => {
  const code = key.toLowerCase().replace(/\s/g, "");
  return Utilities.computeDigest(Utilities.DigestAlgorithm.MD5, key)
    .map((char) => (char + 256).toString(16).slice(-2))
    .join("");
}

const getCache_ = (key) => {
  return CacheService.getDocumentCache().get(md5_(key));
};

const setCache_ = (key, value) => {
  const expirationInSeconds = 6 * 60 * 60; // max is 6 hours
  CacheService.getDocumentCache().put(md5_(key), value, expirationInSeconds);
};

/**
 * Handle arguments as either single values or cell ranges (array of arrays)
 * @template T
 * @param {any[]} args The function arguments
 * @param {(...args: any[]) => T} callback The callback to apply to each permuation
 * @returns {T|T[][]}
 */
const permutate_ = (args, callback) => {
  if (args.length < 1 || args.every(a => !Array.isArray(a)))
    return callback.apply(null, args);

  /**
   * Given a set of arguments which could be arrays or values, return an array of arrays
   * @param {any[]} array 
   * @returns {any[][]}
   */
  const normalizeArrays = (array) => {
    let arraySizes = new Set(array
      .map(a => Array.isArray(a) ? a.length : 1)
      .filter(l => l > 1));
    if (arraySizes.size > 1)
      throw new Error('All arrays must have the same length.');
    let maxSize = arraySizes.values().next().value;
    return array.map(a => Array.isArray(a) 
      ? (a.length < maxSize ? Array(maxSize).fill(a[0]): a) 
      : Array(maxSize).fill(a));
  }

  const argsAsArrays = normalizeArrays(args);
  return [...Array(argsAsArrays[0].length).keys()]
    .map(i => {
      const rowArgs = argsAsArrays.map(a => a[i]);
      const asArrays2 = normalizeArrays(rowArgs);
      return [...Array(asArrays2[0].length).keys()].map(j => callback.apply(null, asArrays2.map(a => a[j])));
    });
}

/**
 * Get the time and distances for the route directions between an origin and destination
 * @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 {{time: number, distance: number}} The time in seconds and distance in meters
 */
const getDirections_ = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  
  const key = ["directions.5", origin, destination, mode].join(",");
  const value = getCache_(key);
  if (value !== null) return JSON.parse(value);
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration: { value: time } } = {}] = [] } = data;
  const { legs: [{ distance: { value: distance } } = {}] = [] } = data;

  const result = { time, distance };
  setCache_(key, JSON.stringify(result));
  return result;
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String|String[][]} origin The address of starting point
 * @param {String|String[][]} destination The address of destination
 * @param {String|String[][]} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {number|number[][]} The time in seconds
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => 
  permutate_([origin, destination, mode], (o, d, m) => getDirections_(o, d, m).time);

/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 *
 * @param {String|String[][]} origin The address of starting point
 * @param {String|String[][]} destination The address of destination
 * @param {String|String[][]} mode The mode of travel (driving, walking, bicycling or transit)
 * @return {number|number[][]} The distance in meters
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode = "driving") => 
  permutate_([origin, destination, mode], (o, d, m) => getDirections_(o, d, m).distance);

/**
 * Get the full address of any zip code or
 * partial address on Google Maps.
 *
 * =GOOGLEMAPS_ADDRESSPART("10005")
 *
 * @param {String|String[][]} address The zip code or partial address to lookup.
 * @return {String|String[][]} The full address from Google Maps
 * @customFunction
 */
const GOOGLEMAPS_ADDRESSPART = (address, part = "formatted_address") => 
  permutate_([address, part], (address, part) => {
    if (!address) {
      throw new Error("No address was specified.");
    }
    
    const keyPrefix = "address.5";
    const key = [keyPrefix, address].join(",");
    let value = null; //getCache_(key);
    if (value === null)
    {
      const allResults = Maps.newGeocoder().geocode(address);
      if (allResults.status !== 'OK') {
        let message = `Error ${allResults.status} finding ${address}`;
        if (allResults.error_message)
          message += `: ${allResults.error_message}`;
        throw new Error(message)
      }
      const { results: [data = null] = [] } = allResults;
      if (data === null) {
        throw new Error(`Address ${address} was not found.`);
      }
      const result = {};
      if (data.formatted_address)
        result.formatted_address = data.formatted_address;
      if (data.place_id) {
        result.place_id = data.place_id;
        result.place_url = `https://www.google.com/maps/place/?q=place_id:${data.place_id}`;
      }
      if (data.geometry && data.geometry.location > 0) {
        result.longitude = data.geometry.location.lng;
        result.latitude = data.geometry.location.lat;
      }

      data.address_components.forEach(component => {
        result[component.types[0]] = component.long_name;
      });

      value = JSON.stringify(result);
      setCache_(key, value);
      if (data.formatted_address)
        setCache_([keyPrefix, data.formatted_address].join(","), value);
    }

    if (part === '*')
      return value;
    const parsed = JSON.parse(value);
    return part.replaceAll(/(\W*)(\w+)/gi, (all, prefix, name) => parsed[name] ? `${prefix}${parsed[name]}`: '');
  });