using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

foxozaur

New Member
Joined
Sep 24, 2019
Messages
11
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
 

foxozaur

New Member
Joined
Sep 24, 2019
Messages
11
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.
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
Fox, what is an example of a "full Origin and Destination post code" in your country?
 

foxozaur

New Member
Joined
Sep 24, 2019
Messages
11
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).
 

Tom Schreiner

Well-known Member
Joined
Mar 18, 2002
Messages
6,867
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

New Member
Joined
Sep 24, 2019
Messages
11
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.
 

Forum statistics

Threads
1,085,504
Messages
5,384,068
Members
401,879
Latest member
wzakaria

Some videos you may like

This Week's Hot Topics

Top