Get numerical values from Google Maps Routing (distance / Duration)-CodePudding

I have limited experience with using functions to get drive time and distance from Google Maps.

Using a post by Amit Agarwal, I am able to obtain the distance and drivetime from point A to point B. It works great!!

I am unable to get just the numerical values in the sheet.

I would like to get the following current values:

Google Distance Kilometers Google DriveTime

"166 km" "2 hours 56 mins"

As:

Google Distance Kilometers Google DriveTime

"166" "176"

Thanks everyone!! Paul

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


/*
 * Function to use PEM's API Key just below this...
 */
function directionsAPI(origin, destination) {
 var Your_API_KEY = "A...s";
 var serviceUrl = "https://maps.googleapis.com/maps/api/directions/json?origin=" origin "&destination=" destination 
"&mode=" Maps.DirectionFinder.Mode.DRIVING "&alternatives=" Boolean(1) "&key=" Your_API_KEY;

 var options={
  muteHttpExceptions:true,
  contentType: "application/json",
 };

 var response=UrlFetchApp.fetch(serviceUrl, options);
  if(response.getResponseCode() == 200) {
   var directions = JSON.parse(response.getContentText());
    if (directions !== null){
     return directions;
     }
    }
  return false;
 }



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 = 8760 * 60 * 60; // max is 6 hours // changed by PEM to be 1 year...
  CacheService.getDocumentCache().put(md5(key), value, expirationInSeconds);
};

/**
 * Calculate the travel time between two locations
 * on Google Maps.
 *
 * =GOOGLEMAPS_DURATION("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 time in minutes
 * @customFunction
 */
const GOOGLEMAPS_DURATION = (origin, destination, mode = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["duration", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;
  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("XNo route found!");
  }
  const { legs: [{ duration: { text: time } } = {}] = [] } = data;
  setCache(key, time);
  return time; 
};

/**
 * 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 = "driving") => {
  if (!origin || !destination) {
    throw new Error("No address specified!");
  }
  if (origin.map) {
    return origin.map(DISTANCE);
  }
  const key = ["distance", origin, destination, mode].join(",");
  const value = getCache(key);
  if (value !== null) return value;

  const { routes: [data] = [] } = Maps.newDirectionFinder()
    .setOrigin(origin)
    .setDestination(destination)
    .setMode(mode)
    .getDirections();
  if (!data) {
    throw new Error("YNo 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;
};

CodePudding user response：

Change

const { legs: [{ distance: { text: distance } } = {}] = [] } = data;
setCache(key, distance);

return distance;

to:

const { legs: [{ distance: { value: meters } } = {}] = [] } = data;
// TODO convert meters to desired unit
const distance = meters;

setCache(key, distance);

return distance;

See

to force meters into km use:

={INDEX(QUERY(,"select "&REGEXREPLACE(REGEXREPLACE(
  GOOGLEMAPS_DISTANCE("pryp'yať", "chornobyl", "walking"), 
  "\sm$", "*0.001"), "\skm", IF(,,))), 2), 
  INDEX(QUERY(, "select "&REGEXREPLACE(REGEXREPLACE(REGEXREPLACE(
  GOOGLEMAPS_DURATION("pryp'yať", "chornobyl", "walking"), 
  " day.?", "*1440 "), " hour.?", "*60 "), " min.?", " ")&0), 2)}