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.
Install and activate ImportJSON in your current spreadsheet
In A1, we’ll paste our url:
in A10, let’s call IMPORTJSON
Here is what you’ll get:
This is the exact same information that is displayed in the DHL website.
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)
Then, we will tell our IMPORTJSON function to filter the results:
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")
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