[Google Maps Formulas for Google Sheets] #googlescript #googlesheets https://www.labnol.org/google-maps-sheets-200817

goog-maps-formulas.js

/*
 *
 * Google Maps Formulas for Google Sheets
 *
 * Written by Amit Agarwal
 * Updates by Eric Reyes
 */

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

/**
 * Calculate the travel time between two locations
 * on Google Maps, accounting for traffic conditions.
 *
 * =GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "driving")
 *
 * @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)
 * @param {Date} [departureTime] The departure time (optional, defaults to now)
 * @return {String} The time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving", departureTime = new Date()) => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }

  const getPlaceAddress = (place) => {
    if (place && typeof place.getPlace === "function") {
      return place.getPlace().formatted_address;
    }
    return place;
  }

  origin = getPlaceAddress(origin);
  destination = getPlaceAddress(destination);

  const key = ["duration", origin, destination, mode, departureTime.getTime()].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .setDepart(departureTime)
    .getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ duration_in_traffic: { text: time } } = {}] = [] } = data;
  setCache(key, time);

  return time;
};


/**
 * Calculate the distance between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
 * =GOOGLEMAPS_DISTANCE(A1, B1, "driving", "4/23/2023 9:00 AM")
 *
 * @param {String | Object} origin The address of starting point or a place smart chip
 * @param {String | Object} destination The address of destination or a place smart chip
 * @param {String} mode The mode of travel (driving, walking, bicycling or transit)
 * @param {String} departureTime The departure time as a date-time string in "MM/DD/YYYY hh:mm AM/PM" format
 * @return {String} The distance in miles
 * @customFunction
 */
const GOOGLEMAPS_DISTANCE = (origin, destination, mode = "driving", departureTime) => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }

  const getPlaceAddress = (place) => {
    if (place && typeof place.getPlace === "function") {
      return place.getPlace().formatted_address;
    }
    return place;
  }

  origin = getPlaceAddress(origin);
  destination = getPlaceAddress(destination);

  const key = ["distance", origin, destination, mode, departureTime].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  let directionFinder = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode);

  if (departureTime) {
    const departureDateTime = new Date(departureTime);
    if (isNaN(departureDateTime)) {
      throw new Error("Invalid departure time format!");
    }
    directionFinder = directionFinder.setDepart(departureDateTime);
  }

  const { routes: [data] = [] } = directionFinder.getDirections();
  if (!data) {
    throw new Error("No route found!");
  }
  const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
  setCache(key, distance);

  return distance;
};



/**
 * Get the latitude and longitude of any
 * address on Google Maps.
 *
 * =GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
 *
 * @param {String} address The address to lookup.
 * @return {String} The latitude and longitude of the address.
 * @customFunction
 */
const GOOGLEMAPS_LATLONG = (address) => {
  if (!address) {
    throw new Error("No address specified!");
  }
  if (address.map) {
    return address.map(LATLONG);
  }
  const key = ["latlong", address].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
  if (data === null) {
    throw new Error("Address not found!");
  }
  const { geometry: { location: { lat, lng } } = {} } = data;
  const answer = `${lat}, ${lng}`;
  setCache(key, answer);
  return answer;
};

/**
 * Get the full address of any zip code or
 * partial address on Google Maps.
 *
 * =GOOGLEMAPS_ADDRESS("10005")
 *
 * @param {String} address The zip code or partial address to lookup.
 * @return {String} The full address from Google Maps
 * @customFunction
 */
const GOOGLEMAPS_ADDRESS = (address) => {
  if (!address) {
    throw new Error("No address specified!");
  }
  if (address.map) {
    return address.map(LATLONG);
  }
  const key = ["address", address].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
  if (data === null) {
    throw new Error("Address not found!");
  }
  const { formatted_address } = data;
  setCache(key, formatted_address);
  return formatted_address;
};

/**
 * Use Reverse Geocoding to get the address of
 * a point location (latitude, longitude) on Google Maps.
 *
 * =GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude)
 *
 * @param {String} latitude The latitude to lookup.
 * @param {String} longitude The longitude to lookup.
 * @return {String} The postal address of the point.
 * @customFunction
 */

const GOOGLEMAPS_REVERSEGEOCODE = (latitude, longitude) => {
  if (!latitude) {
    throw new Error("No latitude specified!");
  }
  if (!longitude) {
    throw new Error("No longitude specified!");
  }
  const key = ["reverse", latitude, longitude].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { results: [data = {}] = [] } = Maps.newGeocoder().reverseGeocode(
    latitude,
    longitude
  );
  const { formatted_address } = data;
  setCache(key, formatted_address);
  return formatted_address;
};

/**
 * Get the country name of an address on Google Maps.
 *
 * =GOOGLEMAPS_COUNTRY("10 Hanover Square, NY")
 *
 * @param {String} address The address to lookup.
 * @return {String} The country of the address.
 * @customFunction
 */
const GOOGLEMAPS_COUNTRY = (address) => {
  if (!address) {
    throw new Error("No address specified!");
  }
  if (address.map) {
    return address.map(COUNTRY);
  }

  const key = ["country", address].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { results: [data = null] = [] } = Maps.newGeocoder().geocode(address);
  if (data === null) {
    throw new Error("Address not found!");
  }
  const [{ short_name, long_name } = {}] = data.address_components.filter(
    ({ types: [level] }) => {
      return level === "country";
    }
  );
  if (!short_name) {
    throw new Error("Country not found!");
  }
  const answer = `${long_name} (${short_name})`;
  setCache(key, answer);
  return answer;
};

/**
 * Find the driving direction between two
 * locations on Google Maps.
 *
 * =GOOGLEMAPS_DIRECTIONS("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 driving direction
 * @customFunction
 */
const GOOGLEMAPS_DIRECTIONS = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  const key = ["directions", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { routes = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!routes.length) {
    throw new Error("No route found!");
  }
  const directions = routes
    .map(({ legs }) => {
      return legs.map(({ steps }) => {
        return steps.map((step) => {
          return step.html_instructions
            .replace("><", "> <")
            .replace(/<[^>]+>/g, "");
        });
      });
    })
    .join(", ");
  setCache(key, directions);
  return directions;
};

Google Maps Formulas for Google Sheets

This Google Sheets script provides a collection of custom functions to work with Google Maps data in your spreadsheets. These functions allow you to calculate travel time, distance, retrieve latitude and longitude, reverse geocoding, and more.

Functions

Here's an overview of the custom functions available in this script:

1. GOOGLEMAPS_DURATION(origin, destination, mode, departureTime): Calculates the travel time between two locations considering traffic conditions.
2. GOOGLEMAPS_DISTANCE(origin, destination, mode, departureTime): Calculates the distance between two locations.
3. GOOGLEMAPS_LATLONG(address): Returns the latitude and longitude of an address.
4. GOOGLEMAPS_ADDRESS(address): Returns the full address for a given zip code or partial address.
5. GOOGLEMAPS_REVERSEGEOCODE(latitude, longitude): Returns the address for a given latitude and longitude.
6. GOOGLEMAPS_COUNTRY(address): Returns the country name of an address.
7. GOOGLEMAPS_DIRECTIONS(origin, destination, mode): Returns the driving directions between two locations.

Installation

To use this script in Google Sheets, follow these steps:

Open a new or existing Google Sheet.
Click on "Extensions" in the menu, then select "Apps Script."
Remove any existing code and paste this script into the editor.
Save the script by clicking the floppy disk icon or pressing Ctrl + S (Cmd + S on Mac).
Return to your Google Sheet, and you can now use the custom functions as you would any other function in your sheet.

Note: Some functions may require you to enable the Google Maps API and set up billing in the Google Cloud Console.

Usage

After installing the script, you can use the custom functions in your Google Sheet cells like any other built-in function. For example:

=GOOGLEMAPS_DURATION("NY 10005", "Hoboken NJ", "driving")
=GOOGLEMAPS_DISTANCE("NY 10005", "Hoboken NJ", "walking")
=GOOGLEMAPS_LATLONG("10 Hanover Square, NY")
=GOOGLEMAPS_ADDRESS("10005")
=GOOGLEMAPS_REVERSEGEOCODE(40.704234, -74.009412)
=GOOGLEMAPS_COUNTRY("10 Hanover Square, NY")
=GOOGLEMAPS_DIRECTIONS("NY 10005", "Hoboken NJ", "walking")

License

This script is provided under the MIT License.