0

Is there way to create a direct link for only the duration data of a driving route?

I'm attempting to use the times for numerous routes simultaneously, to update based on current driving conditions, but I don't know if there's a way to obtain ONLY the drive-time/duration data from a route.

I'm dealing with too many routes to keep looking the individual maps up.

I don't care which source it is, as long as it:

  1. updates for real-world/current driving conditions (as opposed to only "normal" for that time of day)
  2. is free (or CHEAP)
0

Use Google Sheets with a custom function (GOOGLEMAPS).

There are many precedents on the net but I can't find a specific example of travel time on webapps so I've used this script from Chicago Computer Classes (they have a YouTube video and a link to their script)

The formula in cell C2 is:

  • =GOOGLEMAPS(A2,B2,"hours")

The formula in cell D2 is:

  • =GOOGLEMAPS(A2,B2,"minutes")

Their custom formula provides options for "Minutes" or "Hours". I've shown both options. I also converted this to "Hours: Minutes" using this formula in Column E

Note: there is a quota limit on Google Map Direction query of 1,000/day for free GSuite accounts.


Snapshot


/**
* Get Distance between 2 different addresses.
* @param start_address Address as string Ex. "300 N LaSalles St, Chicago, IL"
* @param end_address Address as string Ex. "900 N LaSalles St, Chicago, IL"
* @param return_type Return type as string Ex. "miles" or "kilometers" or "minutes" or "hours"
* @customfunction
*/

function GOOGLEMAPS(start_address,end_address,return_type) {

  // https://www.chicagocomputerclasses.com/
  // Nov 2017
  // improvements needed
  
  var mapObj = Maps.newDirectionFinder();
  mapObj.setOrigin(start_address);
  mapObj.setDestination(end_address);
  var directions = mapObj.getDirections();
  
  var getTheLeg = directions["routes"][0]["legs"][0];
  
  var meters = getTheLeg["distance"]["value"];
  
  switch(return_type){
    case "miles":
      return meters * 0.000621371;
      break;
    case "minutes":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to minutes and return
        return duration / 60;
      break;
    case "hours":
        // get duration in seconds
        var duration = getTheLeg["duration"]["value"];
        //convert to hours and return
        return duration / 60 / 60;
      break;      
    case "kilometers":
      return meters / 1000;
      break;
    default:
      return "Error: Wrong Unit Type";
   }
  
}
3
  • I don't know if we're supposed to say these things here, but this was truly awesome! Thank you! I'm not familiar with Scripts, and even though the times were already included, it looked simple enough to mod for time-only that I decided to try it (just trying to learn, I guess) Any chance you already happen to know off the top of your head, how to accommodate the daily limit? Eg. Does Maps have an option to pay for more, for example? Else, I can contact them to find out. (Sorry for the repeated/deleted Comments; still trying to get used to Stack/formatting, etc) Apr 19 at 18:31
  • Good luck contacting anyone at Google:) Paid Google accounts are now called Google Workspace and the quota for Google Maps requests is 10,000/day. The number of records that you are tracking is important since every refresh of the spreadsheet will count as a request for every GOOGLEMAPS formula - so quota requests could add up quickly.
    – Tedinoz
    Apr 20 at 4:18
  • With the greatest respect, your previous question and master plan were hard to follow. Since you need to come to grips with Apps Script, you might try and do one thing at a time, rather than 'split the atom' at your first attempt. By all means have a plan, but build it in stages and assess the overheads as you go. For example, quota is one aspect but recalculation time is another factor. Alternatively, you might elect to do the "driving time" calculation but save the result as a value (just one request).
    – Tedinoz
    Apr 20 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.