CDXRouteGoogle is a right-click function available in Google compattible versions of CDXZipStream™. Using the Google API web service, the function automatically calculates the driving distance, driving time, and directions between two addresses.
CDXRouteGoogle can be employed as a right-click function as well as input directly into your worksheet as a custom function formula. (Please also refer to the help section on custom functions.) However, we highly recommend that to speed up data retrieval and minimize the number of Google data requests, you enable the AutoCopy option and set the returned data to text when obtaining data for lists of locations. Autcopy is discussed in more detail below.
Before using CDXRouteGoogle, you must enter a Google API key in CDXZipStream. A free developer key can be obtained at console.cloud.google.com.
To use as a right-click function, right-click on any cell in a worksheet, select the Insert CDXRouteGoogle Function, and you will see the input box shown below:
The following inputs are required:
Address 1: The beginning point of the trip or the range containing all the stops in your trip. You may use the actual ZIP Code (such as "06830") or the worksheet cell location of the ZIP Code ("B4"), or the range of locations ("B4:B5") in Excel. An Excel named range can also be used here.
Address 2: The end point of the trip. This is only required when you only specify one address in the first address field. You may use the actual ZIP Code (such as "08033") or the worksheet cell location of the ZIP Code ("B5").
Route Calculation: Choose Distance (in miles or kilometers), or Driving Time (in minutes, hours, or days) or any of the other available options. This is the resulting output that will display in the worksheet.
Travel Type: Select the travel type of interest: Driving, Bicycle, Walking, Two_Wheeler or Transit
AutoCopy: By enabling AutoCopy, data will automatically be returned for an entire list of locations, starting at the specified worksheet cell and ending at the first occurence of an empty row. By using the "Set To Text" option, the data is returned as text instead of custom formulas. This allows for sorting, moving or other manipulation of the returned data while avoiding inadvertent data requests to Google.
Google API Settings: Click on this button to edit settings for Google calculations and input a Google API key.
Acceptable address formats are:
Zip Code Example: 06830
Street Zip Code Example: 101 Field Point Road 06830
City Zip Code Example: Greenwich 06830
City State Example: Greenwich CT
Street, City Zip Code Example: 101 Field Point Road, Greenwich 06830
Street, City, State Example: 101 Field Point Road, Greenwich, CT
Street, City, State Zip Code Example: 101 Field Point Road, Greenwich, CT 06830
Latitude, Longitude example: 40.84|-74.56
Either the full state name or two-letter abbreviation can be used. If there is a major discrepency in the address information (the ZIP Code is incorrect for the city or state provided) an error will be returned for that address. If you are doing a multi-country analysis you should also add the country name as part of the address.
US addresses are shown here, but address formats vary by country. Use the standard address format for the country of interest.
Here is an example where the CDXRouteGoogle function is applied to lists of addresses:
We are requesting that driving distance in miles be returned between two lists of addresses (ZIP Codes in this case) in columns B and C, starting in row 4. Alternatively, the range "B4:C4" could have been used as the Address 1 input, with Address 2 left emplty. AutoCopy is enabled to return data for the entire list, and the returned data is "Set to Text", which is recommended for long lists. The result is:
Note that the driving distance data in column D is returned as text and not formulas.
If there are more than two columns of addresses, i.e. each route has intermediate stops, the input for Address 1 can be a range, such as "B4:E4". Intermediate stops can only be applied to driving and walking routes.
To use CDXRouteGoogle function in a custom function formula, use the following format:
=CDXRouteGoogle(Route Calculation, Travel type, Start Address, End Address)
Use the values below for the first three parameters:
