If you have an e-commerce and you send your products through DHL, keep reading to learn how to track all the parcels you send directly in Google Sheets.
Let’s say you are seeing all your shipments data in a page like this one:
In few steps, you will be able to have all this info in a spreadsheet that updates automatically like this one:
How does it work?
It uses the ImportJSON add-on for Google Sheets that we built.
ImportJSON provides a simple function to extract data from APIs. You just need to find the urls of the pages you want to load and tell ImportJSON to extract the elements you want. Thanks to the flexibility of ImportJSON you will be able track hundreds of shipments in a single table!
Install ImportJSON in Google Sheets
Install ImportJSON in Google Sheets” use_theme_fonts=”yes” el_class=”hightlight”]
[vc_column_text]First, install the tool into your Google Sheets from the Google Workspace Marketplace following the step-by-step instructions indicated by the add-on.
From there, you can access the tool by clicking “Add-ons” and activating ImportJSON within the spreadsheet, you are ready to go![/vc_column_text]
[vc_custom_heading text=”Copy the targeted URL and call the function ImportJSON” use_theme_fonts=”yes” el_class=”hightlight”][vc_column_text]In A1, we’ll paste our url, that should look similar to this one:
https://mydhl.express.dhl/au/en/tracking.html#/results?id=1322114461
in A10, let’s use IMPORTJSON function typing:
=IMPORTJSON(A1)
Here is what you’ll get:
This is the exact same information that is displayed in the DHL website, now we only need to filter and order it a bit to make it easier to manipulate.[/vc_column_text][vc_custom_heading text=”Filter with the data you need in your spreadsheet” use_theme_fonts=”yes” el_class=”hightlight”][vc_column_text]You maybe won’t need many of the fields extracted . 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)
| A2 | results/0/delivery/status |
| A3 | results/0/origin/value |
| A4 | results/0/destination/value |
| A5 | results/0/checkpoints/0/time |
| A6 | results/0/checkpoints/0/date |
| A7 | results/0/checkpoints/0/location |
Then, we will tell our IMPORTJSON function to filter the results, introducing in the function the targeted url + the filters we have indicated in cells A2 to A7:
=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")
[/vc_column_text][vc_custom_heading text=”Retrieve the table of checkpoints” use_theme_fonts=”yes” el_class=”hightlight”][vc_column_text]Now, 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
Did you find this tool useful?
We’d love to hear all of your feedback so that we can keep providing the best information on web scraping!
