How to Upload Addresses in Excel to Google for the Shortest Route
The Google Directions application programming interface does not include a method of uploading a database of addresses to retrieve directions. However, it is possible to create a series of programmatic URLs within Excel that retrieves directions information in formats that can then be used in Excel calculations.
Other People Are Reading
Instructions
-
-
1
Create an Excel spreadsheet with data in two columns containing the origin and destination addresses you wish to look up in the Google Directions API.
-
2
Enter a calculation in the next column that converts an address into a URL-encoded format that can be used in a Google Directions API lookup. This requires replacing spaces with plus signs. If the origin address is in cell A1, the calculation is as follows:
=SUBSTITUTE(A1," ","+")
-
-
3
Repeat step two in the next column to create a URL-encoded format for the destination address. If the destination address is in cell B1, the calculation is as follows:
=SUBSTITUTE(B1," ","+")
-
4
Create a calculation in a fifth column that creates the URL to be sent to the Google Directions API. If your converted origin and destination addresses are in cells C1 and D1, the calculation is as follows:
=CONCATENATE("http://maps.googleapis.com/maps/api/directions/xml?origin=",C1,"&destination=",D1,"&sensor=false")
-
5
Copy the calculations from steps two, three and four down the column to create a URL for each of the origin and destination addresses.
-
6
Copy each URL and paste it into the address bar of a Web browser, then hit return. Google will reply with the directions from the origin to the destination address in XML format.
-
7
Copy the XML results and paste them into a text editor such as Notepad. Most of this data will be unnecessary if you only need the text directions for the shortest route. You can delete any XML data that is not surrounded by "<html_instructions>" tags. If you need more information from the returned data set, use the XML tag structure to determine what other data to retain.
-
8
Paste the resulting XML data into a new column in the Excel spreadsheet.
-
9
Repeat steps six through eight to retrieve directions for each origin and destination.
-
1
Tips & Warnings
This process can be automated using Excel macro programming, but Excel is not the most efficient software to use for this purpose. Database software will be more efficient for creating the URLs to post to Google, automating the URL request and receive process, and parsing the resulting XML files into additional database fields.
