ImportXML google sheet for shipment package tracking

0
votes

I want to track each FEDEX, DHL and UPS shipment status directly from google sheets. I am using importxml function: =IMPORTXML("https://www.fedex.com/apps/fedextrack/?action=track&tracknumbers="&C2&"&locale=en_US&cntry_code=us","//h1/div[@class="redesignSnapshotTVC snapshotController_addr_label dest"]/title")

However it shows error. Attaching my sheet link for this: https://docs.google.com/spreadsheets/d/1E1L0rn9-H4MCutI1On2uHDkkPo1pdjRpv014YREGIdU/edit?usp=sharing

Please tell what is best way to do it. I am from non tech background. Thanks so much for help!

1
apigoogle-sheetsgoogle-sheets-importxml

1 Answers

1
votes

Common issue. You're trying to import html that is generated after loading. That means we have to look for how the data you want is generated.

Data source

Upon inspection of the site, I found that it was making an XHR to the URL https://www.fedex.com/trackingCal/track, and that it was doing so via POST with a long payload. The response was in JSON format. Of note, there is the scanEventList entry.

"scanEventList": [{
    "date": "2020-07-15",
    "time": "13:15:00",
    "gmtOffset": "-07:00",
    "status": "Delivery exception",
    "statusCD": "DE",
    "scanLocation": "SAN BERNARDINO, CA",
    "scanDetails": "Future delivery requested",
    "scanDetailsHtml": "",
    "rtrnShprTrkNbr": "",
    "statusExceptionCode": "17",
    "isClearanceDelay": false,
    "isDelivered": false,
    "isDelException": true,
    "isException": true
},
...
]

Solution

First, get the ImportJSON script from GitHub, and add it into your sheet's scripts (Tools > Script Editor). It's not the most amazing thing, but it will at least give us ImportJSONViaPost() to get the data we want:

=INDEX(ImportJSONViaPost("https://www.fedex.com/trackingCal/track","data=%7B%22TrackPackagesRequest%22%3A%7B%22appType%22%3A%22WTRK%22%2C%22appDeviceType%22%3A%22DESKTOP%22%2C%22supportHTML%22%3Atrue%2C%22supportCurrentLocation%22%3Atrue%2C%22uniqueKey%22%3A%22%22%2C%22processingParameters%22%3A%7B%7D%2C%22trackingInfoList%22%3A%5B%7B%22trackNumberInfo%22%3A%7B%22trackingNumber%22%3A%22"&A2&"%22%2C%22trackingQualifier%22%3A%22%22%2C%22trackingCarrier%22%3A%22%22%7D%7D%5D%7D%7D&action=trackpackages&locale=en_US&version=1&format=json",,"/TrackPackagesResponse/packageList/scanEventList,/TrackPackagesResponse/packageList/trackingCarrierDesc","noHeaders"),1)

Arguments:

  1. URL for the tracker
  2. The POST payload. Cell A2 Holds your tracking number.
  3. Leave empty
  4. Query - Lets us select the data that we want. In this example, I chose to select the carrier description and the entire scanEventList entry, but you can specify particular elements of that as well.
  5. "noHeaders" means just the data.

Just using the JSON import gives us an entry for each element of scanEventList, but the first is the most recent, so, we use INDEX to retrieve the first entry, which should contain what you need.

For additional help on the ImportJSON package, see here.