Search for content

How to use PTV Developer in Microsoft Excel

PTV Developer in Microsoft Excel

In this tutorial you will learn how to use PTV Developer APIs in Microsoft Excel. Only Excel functions will be used. 

Download example

Preconditions

  • A PTV Developer account and API Key
  • Windows OS
  • Microsoft Excel 2013, 2016, 2019, 2021 or Excel for Microsoft 365

Limitations

  • Only works for GET-requests
  • Difficult access of data deeply nested in JSON response
  • Not supported by Microsoft Excel Web

Build the request

To build the url first add the url of the Developer API endpoint you wish to use and your API-key to a cell. In this tutorial, the Routing API is being used.

Build the request

Now add a cell for each parameter you wish to set for your request and concatenate the url, the parameters and your API-key to build the request url.

=A$2&"waypoints="&A5&"&waypoints="&B5&"&results="&C5&"&apiKey="&C$2

Send the request

To send the request and get the JSON response simply call the WEBSERVICE function on the request url.

=WEBSERVICE(D5)

Display the result

To extract and display data, search the JSON result for the desired key and read the value of this key. This can be done by utilizing the MID and SEARCH functions. The following function extracts the distance of the routing:

=VALUE(MID(E5,SEARCH("distance",E5)+LEN("distance")+2,SEARCH(",",E5,SEARCH("distance",E5))-SEARCH("distance",E5)-LEN("distance")-2))

This query can be adapted and used for all non-nested data:

TravelTime:
=VALUE(MID(E5,SEARCH("travelTime",E5)+LEN("travelTime")+2,SEARCH(",",E5,SEARCH("travelTime",E5))-SEARCH("travelTime",E5)-LEN("travelTime")-2))
TrafficDelay:
=VALUE(MID(E5,SEARCH("trafficDelay",E5)+LEN("trafficDelay")+2,SEARCH(",",E5,SEARCH("trafficDelay",E5))-SEARCH("trafficDelay",E5)-LEN("trafficDelay")-2))
Violated:
=MID(E5,SEARCH("violated",E5)+LEN("violated")+2,SEARCH(",",E5,SEARCH("violated",E5))-SEARCH("violated",E5)-LEN("violated")-2)

Depending on the data you want to extract the use of longer and more complex functions is necessary. As an alternative you can use VisualBasic for Applications (VBA) or other available methods to work with JSON in Microsoft Excel.