Results 1 to 8 of 8

Thread: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Location
    Amsterdam
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    The easiest way would be to use Google's distance matrix API. https://developers.google.com/maps/d...e-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?

  3. #3
    New Member
    Join Date
    Sep 2019
    Location
    Amsterdam
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    Quote Originally Posted by Tom Schreiner View Post
    The easiest way would be to use Google's distance matrix API. https://developers.google.com/maps/d...e-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.

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

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

  5. #5
    New Member
    Join Date
    Sep 2019
    Location
    Amsterdam
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    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).

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    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!

  7. #7
    New Member
    Join Date
    Sep 2019
    Location
    Amsterdam
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    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.

  8. #8
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,867
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: using Hyperlink function to extract Google kilometers into Excel Spreadsheet automatically

    Glad to help a little.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •