Help with Google Maps Formula for Google Sheets

[Guillaume Lahoud]

Hi Guys, 

I am trying to calculate distance and driving time between locations in a google spreadsheet that I use for customer quotes. I have found this so amazing post from Amit Agarwal which explains precisely that and actually does it for me, all I needed was to copy the AppScript files. 

https://www.labnol.org/google-maps-sheets-200817

My only issue is that the functions returns the value and includes units in the cell, so I cannot use it in calculations. (ex: 5 hours 30 mins). 

Anyone knows how to update the AppScript file so that I only get a value ? (ex: 5.5   or   330). 

I have this issue with these two functions --> GOOGLEMAPS_DURATION  and   GOOGLEMAPS_DISTANCE

/**

 * 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("No 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("No route found!");

  }

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

  setCache(key, distance);

  return distance;

};

Thanks so much! I am not a developer and this is out of my competences.. 
Cheers, 

Guillaume

[Faustino Rodriguez]

Instead of duration: { text and distance: { text, ... use duration: { value and distance: { value

value would be a number in seconds and meters

https://developers.google.com/maps/documentation/javascript/reference/directions#Duration

[Guillaume Lahoud]

Thanks so much ! That solved it. 

[Nassos Perrotis]

I have a different question on the same topic.  I am using the same  amazing post from Amit Agarwal   and I receive an exception for the number of calls (Exception: Service invoked too many times for one day: route. (line 89).).    Is there a way to circumvent this, possibly by using a maps API key?   I am completely lost as to how, where, when. 

Thank you