Hello everyone, it is Ryan here on the Syntax Byte and in this video we're going to have a quick look at using the Google Maps Directions API and Visual Basic for Applications to bring some travel time and travel distance data between two points into Microsoft Excel. Specifically, we're going to make a function that you can call from the cell here. to pull in the travel time and travel distance based on two points.
So you can use an address. The directions API is pretty dynamic, so we have a place name here. So we're going to try and find this information between the U.S. Capitol and the White House here, which I've given with an exact address. So the directions API should be able to handle that quite easily for us.
and we should be able to get the travel time and distance by car. So to get started here, the first thing you're going to need is an API key to use the directions API. So why don't we go ahead and get one of those. So first of all, you can go to this Get Started page on the Google Developers.
I'll have it below. You can click this Get Started with the Google Maps platform. Already have an account and stuff set up. So I'm just gonna create a new API key I have a project here called Excel macros and we're just gonna create an API key Okay Then we're gonna go close Okay, that should show up over here under the directions API now Perfect so it's asking me to secure it.
I'm not gonna bother with that. I'm just gonna delete it after the video anyway But we can go ahead and copy that now so the first thing we want to do is get a bit of an understanding of how the API works and then we can jump into Creating the macro so why don't we copy this test URL they have go paste and go Oh, it doesn't my API key is invalid Thank you Okay, you probably shouldn't paste the entire URL again as the API key let's try that again Okay, so this gives us this was between like Disneyland and Hollywood Studios or something Universal Studios and Disneyland, okay So you can see here it's got this routes object so there's geo coded waypoints. We don't really care about that We have routes here.
It gives us a single route There could be multiple routes. For the sake of the function, I'm just going to always take the first route and assume that's okay, that that's representative. It gives us this array called legs here.
We can see there's only one leg, but we will, in the function, incorporate adding together legs in case there were multiple legs. And then it just gives us the distance. This is in meters. time in seconds.
So that's what our Excel function is going to target to get from the API. We're going to put in a request with the addresses from Excel and look for these two values and spit those back out. So the first thing we need to do is you'll notice this is a JSON API.
To make it a little bit easier to work with the JSON API, we're going to go ahead and download. the VBA tools JSON. I've already got it downloaded, but I'll have this link in the description for anyone who may not. Once we get back into Excel here, I'm going to actually go ahead and paste that API key in there right now just so we have that.
Let's go ahead and get that copied. And that will be an input to our function as well. You could hard code the API key in VBA, but I just think it's a little bit nicer to throw it in Excel. itself there a little bit more flexible if you're gonna be sending this to someone who isn't as familiar with VBA let's go ahead and open up the visual basic editor here okay so the first thing that we want to do is want to go file import file we want to find where we have VBA JSON and import this JSON converter dot BAS go open Perfect that will allow us to use that shows up on your modules.
Next thing we're going to do is create a new module Insert module we can call this like Google Maps or something Okay, and then within this module is where we're going to create our functions. So the first function that we're going to create is the travel time function, and it is going to get the travel time for us. So we can go function, not sub, no, function, travel time. We're going to take an origin, the starting place, the destination, and that API key.
We're going to start off with declaring a strurl as a string. And we're going to use this just to store our url. And so we need that url. We need to build sort of that url out. So we can start with this portion of it right here.
copy that into there then we're gonna go and sorry and origin now we'll concatenate it for us and then we're gonna do and and then so that will plop the origin right in here and I'm gonna do and destination equals oh not at all what I want to do I really don't like the VBA editor to be honest It's got to be one of the worst code editors around. Okay, and then we can do and destination. So this is just going to build us one long string that is the URL, but it's going to allow us to pass the destination and origin to the function and have those inserted in the URL by VBA.
Okay, so... And then we also need and key equals and then to throw our API key in there. Perfect.
And so now this is the URL, and now we need to make, of course, a web request. And so in order to do that, we need to add some references to Visual Basic here. So we can go Tools, References. And the references that you want to add are the Microsoft Scripting Runtime. and Microsoft WinHTTP services that one there just click OK and so that should allow us to make the web request here so in order to do that we're going to make this http request object with create object and that is going to be an msxml2.xml http and then with that object we are going to open It's going to be a get request, so that's the type of request to str.
Url Passing false which I don't know it means right now unfortunately and then dot send Then we can end So this is going to fire off that web request Then of course we need to store the response so the JSON all of this that it gets back. We're gonna store that in a string so response equals HTTP rec response text Okay, so now We have a string containing our JSON file. We need to use vba.json to parse it and to find those integers that we were trying to pull out of this.
So in order to do that we can simply do a response, sorry, dim parsed as dictionary. So VBA JSON is going to return a dictionary and we're going to call it parsed. So we'll do set parsed equal to JSON converter and remember that is from VBA JSON that we added. .parse json, it's auto-completing, so we know we've added that correctly. We're going to pass in that response.
Remember, response has already been converted to a string representing HTTP rec response text. We now dim seconds as an integer. We're going to use this because we're going to loop any of these legs that it has.
So usually there's just one leg, but we're going to loop any of them and add the... the values together in case there were multiple legs to get the total trip length. Okay, so we're going to go for each leg in parsed. So parsed is our JSON file. We first need to go under routes.
And like I said, we're just going to assume the first route is correct. Okay, so we're going to go into that route. Sorry, parsed. We're going to use that routes array. We're going to get the first item.
Remember, VBA isn't always zero indexed, so that's why it's one, even though it is zero, represented in Firefox there. And then we're going to get the lengths. And then so for each of those lengths, we're going to say seconds equals seconds plus leg duration.
And then the value. Under duration, so that's of course gonna be We're getting right here duration and then that value right there perfect and we can go ahead and do next leg And then we set the value of the function to that second So that is what is going to be returned by the travel time. So the travel time Should be working now and unfortunately, I seem to have saved this as a macro fee free workbook. We're just going to re-save that.
Excellent. So now our travel time function should work. So let's go ahead and try it. Travel time between US Capitol, this address, which represents the White House, and pass in our API key. Let's see what we get.
So we get 622. That should be the number of seconds. If we just do that, divided by 60, oh it's like 10.3 minutes. And I have it actually pulled up here. Okay, that's interesting.
It's not quite on, but seems to be about right. Seems to be reasonable anyway. It could just be, let's go ahead and refresh. Now this is 11 minutes. Okay, so.
It's probably just some sort of a variation in traffic or route that it's doing. But 10.3 and this is 11, I can definitely buy that. So that's the travel time function.
What we could do now is we can go ahead and copy this and just basically do essentially the same function for travel distance. Of course, you can make a separate function that sort of does the web request because that part is the same for both. and then just returns this dictionary and then have another function that takes this dictionary and finds what you want within it but this is just the quickest and dirtiest way to get both functions but if you're planning to create a number of these functions that would probably be a far cleaner way to do it um so then what we want to change here is we're going to call this one call travel distance and Instead of getting the time we're gonna get the distance so all we're gonna do we're gonna rename seconds to meters Because the API always returns the distance in meters even for American trips So then you just have to convert to kilometers or miles as you prefer In Excel or you could do it in VBA, but I recommend doing it in Excel because it just is more flexible that way So where we have duration here, we're going to do, I believe it's distance.
So distance. Routes is still fine. We can still take the first leg, and we want this to be travel distance equals seconds.
Perfect. Okay, so we can go ahead and save that. And then right here, we're going to go ahead and do travel distance. We'll use the same origin, same destination, and the same API key. Go ahead and hit enter.
Okay, we're getting zero, quite interestingly. Why is that the case? Those are all correct.
meters is meters ah seconds doesn't actually exist anymore so we just have to change that to meters as well let's go ahead and try that again perfect and we get 2763 meters we can go ahead and just look up what that would be converted to miles so uh 2,763 meters to miles. That's about 1.7 miles. Oh, that's a bit odd.
That conversion doesn't make sense. Okay, so I've gone ahead and had a look at what the response we should be getting is in the browser And it does say 1.7 miles or two seven six three meters there What I believe is happening if we go back here You can see it says 2.4 miles, so that strikes us as off, although the travel time is similar. I believe what's happening is if you go to walking, there's actually kind of this other route here. And I'm going to guess that somehow it ended up taking a route.
That was more similar to the walking route, which is about, you can see about 1.8 miles. So I'm going to guess that that's what's going on and that everything's working normally with VBA and the API, but that it's just giving us a little bit different results from what we're getting here in the browser on Google Maps itself. So that is unfortunate, but I don't think it's anything to be too concerned about. Whoa.
Okay. Apparently I added. Added this okay, but that is a relatively accurate distance So and of course you could you could do a conversion. I'm not actually sure how many meters are in a mile I will just do a simple round this divided by a thousand to two and Concatenate KM on there just to give us about two point seven six kilometers So that is how you can do travel time and distance with the Google Maps Directions API in Excel.
There's so many possible use cases for this, especially if you have lots of addresses or something. Check out my other video on how to import JSON data from the web. Maybe you could import a list of addresses from the web and then find the travel times between them.
that would be quite an interesting project i can picture lots of different use cases for that also if you guys had any difficulties following along with this tutorial or would like to just copy and paste the code i do have a written tutorial with the full source code linked down in the description on my website so definitely check that out anyway guys i will see you in the next video have a good one