Track DHL shipments in Google Sheets

With ImportJSON, it becomes so easy to track all the packages you send through DHL.

Here is how you can extract all the data from a page like this one

In a Google Sheets like that one:

In which the data updates automatically.

Thanks to the flexibility of ImportJSON you will be able track hundreds of shipments in a single table.

1. Install ImportJSON

Install and activate ImportJSON in your current spreadsheet

2. Call IMPORTJSON

In A1, we’ll paste our url:

https://mydhl.express.dhl/au/en/tracking.html#/results?id=1322114461

in A10, let’s call IMPORTJSON

=IMPORTJSON(A1)

Here is what you’ll get:

This is the exact same information that is displayed in the DHL website.

3. Filter with the data you need

Many of the fields here may be useless. Therefore it may be useful to clean up a bit the table.

For this purpose, we will simply copy the keys from the left column and add them in a list starting in cell A2
(remember we kept a few rows empty)

A2results/0/delivery/status
A3results/0/origin/value
A4results/0/destination/value
A5results/0/checkpoints/0/time
A6results/0/checkpoints/0/date
A7results/0/checkpoints/0/location

Then, we will tell our IMPORTJSON function to filter the results:

=IMPORTJSON(A1, A2:A7)

And here it is

Remember you can use multiple IMPORTJSON functions for each of the shipments you want to track. To have one shipment per column, just add more functions in B10, C10, … with different URLs.
But more importantly, add a third parameter to your function to hide the keys of the first column like so:

=IMPORTJSON(A1, A2:A8, "hideHeaders")

4. – Retrieve the table of checkpoints

You may want to dig into the route of your package.

ImportJSON makes it easy because it will pivot the table if it sees a pattern that allows it.
The trick is to filter the table correctly.

In the data provided by DHL that we can see by using IMPORTJSON with no filter, we can notice that a bunch of keys seem to indicate different checkpoints:

By using the filter results/0/checkpoints We will tell IMPORTJSON to show only the checkpoints and display them in column