using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

[foxozaur]

Dear members of the forum,

I'm struggling with a mind-numbing task that needs immediate automation. While we're looking for cheap alternatives to employ for automating kilometer calculation from point A to point B, I thought it's best to inquire the forum what are the possible options to 1) create a hyperlink for each lane (Point A to Point B) in order to populate and 2) extract all the values from the hyperlink straight into the spreadsheet without clicking every single hyperlink to extract the distance in kilometers.

Every day, we receive more than 500+ lanes to evaluate and calculate their distance, and it got to the point where it's incredibly mind numbing for the whole team to spend 1/2 of the work day trying to add the kilometers from google maps onto the excel spreadsheet.

Any tips will be greatly appreciated!

Best

Foxozaur

 

[Legacy 98055]

The easiest way would be to use Google's distance matrix API. https://developers.google.com/maps/documentation/distance-matrix/intro
The cost is about $5.00 US per 1000 hits. Another way, I suppose, would be to scrape the results of each individual search. Define point A, B. Are these addresses? Geo?

 

[foxozaur]

The easiest way would be to use Google's distance matrix API. https://developers.google.com/maps/documentation/distance-matrix/intro
The cost is about $5.00 US per 1000 hits. Another way, I suppose, would be to scrape the results of each individual search. Define point A, B. Are these addresses? Geo?

Thank you a lot, Tom!

The reason for inquiry is for undue kilometers based on Geocoded Origin & Destination city, post codes, country codes. Although each corporation has a different definition for Geocoding, I'm referring to a 3-5km radius based on full Origin & Destination post codes.

 

[Legacy 98055]

Fox, what is an example of a "full Origin and Destination post code" in your country?

 

[foxozaur]

Tom, in the context of the Netherlands, a hypothetical situation - Origin post code is 2515AL (shortened would be the first two digits, 25) and destination code is 2621GB (shortened - 26).

 

[Legacy 98055]

Fox, I tried to run a query in a plain old Google search and it could not find the 2621GB. It did find the first one. I'm not sure if I am going to be able to help you out or not. The Google API may be your best bet. There are other companies out there that offer the same service. If that's not an option, you should be able to use Google. I'll need you to do a Google search that returns the result you are looking for and then post the query string in here. Have a good day!

 

[foxozaur]

Tom, apologies - I listed random post codes over there, expected the destination code to be operational. Anyways, I contacted Google Maps customer service, among many other distance calculation and route optimization companies, and we're onto something.

Thank you for taking the time and providing an amazing lead.

 

[Legacy 98055]

Glad to help a little.