UPS Tracking in Google Sheets: The Complete Guide
UPS is still the default carrier for an enormous amount of US e‑commerce — and anyone shipping at volume eventually hits the same wall: you have 80 UPS tracking numbers in a spreadsheet and no sane way to check their status without opening 80 browser tabs.
This guide shows how to pull live UPS tracking data — status, last location, delivery events — directly into any Google Sheet using a single formula. No UPS developer account, no API key, no Apps Script.
The one‑cell answer
If you already have the Your Package Tracker add‑on installed, type this into any empty cell:
=PKG_STATUS_LIVE("1Z999AA10123456784", "UPS", TODAY())
You'll get back something like Delivered, In Transit, Out for Delivery, or Exception — and crucially, the cell keeps updating itself as UPS posts new scans. No re-running, no re-clicking. That's the whole setup.
What's
PKG_STATUS_LIVE? It's our auto-updating formula — unique to Your Package Tracker. While other add-ons make you re-run a script every time you want fresh data, ours uses live carrier push notifications so your sheet stays current on its own. TheTODAY()argument tells Google Sheets to re-evaluate the formula daily; we also expose a one-click "Refresh Live Tracking" menu for instant updates.
The rest of this guide covers the details: UPS tracking number formats, which UPS services work, how to bulk‑track a large range, and the quirks worth knowing.
What a UPS tracking number actually looks like
UPS uses several tracking number formats depending on the service. The add‑on auto‑detects all of them, but it helps to know what you're looking at:
| Format | Looks like | Service |
|---|---|---|
| 1Z (18 chars) | 1Z999AA10123456784 | UPS Ground, Express, 2nd Day, Worldwide |
| 12 digits | 123456789012 | UPS InfoNotice reference |
| 9 digits | H1234567 | UPS Freight (legacy) |
| T‑prefix | T1234567890 | UPS Worldwide Express |
| 9400 1 (USPS‑handoff, 22 dig.) | 9400111899223100012345 | UPS Mail Innovations / SurePost |
That last row is the one that trips people up. UPS SurePost and UPS Mail Innovations hand the final‑mile delivery to USPS, so the tracking number often looks like a USPS tracking number. You can track them by passing UPS or USPS as the carrier — the add‑on resolves either.
Step‑by‑step: set up a UPS tracking sheet
1. Install the add‑on
Open a Google Sheet, go to Extensions → Add‑ons → Get add‑ons, search for Your Package Tracker, and install. You get 3 free lookups to test.
2. Lay out your columns
Here's the layout every UPS ops team converges on eventually:
| Col | Header | Example |
|---|---|---|
| A | Order ID | #1042 |
| B | UPS Tracking # | 1Z999AA10123456784 |
| C | Carrier | UPS |
| D | Status | (formula) |
| E | Summary | (formula) |
| F | Last Event | (formula, optional) |
3. Drop in the formulas
In D2:
=PKG_STATUS_LIVE(B2, C2, TODAY())
In E2:
=PKG_SUMMARY_LIVE(B2, C2, TODAY())
(Optional) in F2:
=PKG_LAST_EVENT_LIVE(B2, C2, TODAY())
You'll see In Transit, Denver, CO — Departed UPS Facility (2d ago), and so on. Ready to be sorted, filtered, charted, or color‑coded — and updating itself as UPS scans the package.
4. Apply to many rows — just drag-fill
This is where we differ from every other tracking add-on out there. With the _LIVE formulas, you can drag-fill across 500 rows without breaking anything. Our backend batches registrations behind the scenes and respects UPS's rate limits for you. No "too many requests" errors.
Just select D2:F2, grab the fill handle, and drag down to row 500. The first time each tracking number is seen, you'll see Registering... for a few seconds. After that, the values populate themselves — and stay current.
Need a one-time snapshot instead? The classic Bulk Track Packages menu (Your Package Tracker → Bulk Track Packages) is still there for users who want a synchronous fetch with no auto-updates. Pick whichever fits.
UPS services the add‑on handles
Because the underlying tracking engine speaks UPS's public tracking protocol, every UPS service is supported:
- UPS Ground — the everyday 1Z‑prefix domestic service
- UPS Next Day Air / 2nd Day Air / 3 Day Select — same format, different SLA
- UPS SurePost — last‑mile via USPS, traceable from either side
- UPS Mail Innovations — bulk‑mail hybrid, USPS last mile
- UPS Worldwide Express / Expedited / Saver — international, still 1Z or T‑prefix
- UPS Standard to Canada / Mexico
- UPS Freight (TForce) — LTL freight with pro numbers
- UPS Access Point returns
There's no configuration switch between them. Pass the tracking number and UPS as the carrier; the right backend is chosen automatically.
Understanding the statuses UPS returns
UPS normalizes events into a fairly predictable vocabulary. The statuses you'll most often see:
- Label Created — shipper printed the label; UPS hasn't picked it up yet
- Origin Scan — UPS has possession
- Departed Facility — moved between hubs
- In Transit — on the road or on a plane
- Arrived at Facility — regional sorting
- Out for Delivery — on the truck, delivery expected today
- Delivered — done, usually with a signature or photo indicator
- Exception — address issue, weather delay, customs hold, damage, etc.
- Return to Sender — coming back
A practical tip: set conditional formatting so Exception and Return cells turn red. That column becomes your proactive customer‑service queue.
Three UPS quirks worth knowing
1. "Label Created" can sit for hours
UPS only updates tracking once the package is physically scanned. If a warehouse prints 200 labels at 4 PM, you'll see 200 rows stuck at Label Created until UPS picks up. That is not a bug in the add‑on — it's UPS's reality. The first real scan usually appears within 24 hours.
2. International 1Z numbers are the same format
A 1Z number going from Atlanta to Frankfurt looks identical to a domestic one. The add‑on figures out the routing from UPS's side, including customs events (Export Scan, Import Scan, Released by Customs).
3. Mail Innovations / SurePost show different details over time
Early in the lifecycle, tracking lives in UPS's system. Once USPS takes custody, updates shift to USPS. Because the add‑on queries both networks transparently, you'll see the whole timeline from either side — but the Last Event location may suddenly jump from a UPS hub to a USPS facility. That's expected.
A worked example: a Shopify store with 300 UPS shipments/day
Say you run a DTC brand, ship via UPS Ground, and export a daily "open orders" CSV from Shopify. The workflow:
- Import the CSV into a Google Sheet. You should have columns for order ID, tracking number, and carrier (UPS).
- Add a Status column with
=PKG_STATUS_LIVE(B2, C2, TODAY())and drag-fill to the bottom. - That's it. The cells populate themselves and update as packages move. No daily run, no script.
- Pivot: count rows by status. Your dashboard now shows how many are delivered, in transit, or in exception — no separate tool, always current.
- Wire a filter view for
Exception. Hand that view to your customer service lead as their daily callback list — and it auto-refreshes too.
Setup time: under 10 minutes. Ongoing work per day: zero.
Frequently asked questions
Do I need a UPS API account? No. The add‑on handles the carrier integration. You never see a UPS developer portal.
Does it work with UPS CampusShip / WorldShip numbers? Yes. Any tracking number UPS itself can trace can be traced through the formula.
What about signature details and proof of delivery?
PKG_SUMMARY_LIVE includes the delivery timestamp. Full PoD images are not exposed via public tracking and therefore are not available through the formula — you'll still need ups.com for that.
How often does the status refresh?
With PKG_STATUS_LIVE, the cell updates daily via the TODAY() argument plus whenever UPS pushes a new scan event to our backend. For an instant manual refresh, use the menu: Your Package Tracker → Refresh Live Tracking. With the older PKG_STATUS (no auto-update), values are cached for 6 hours — use =PKG_REFRESH(B2, C2) to bypass that cache.
What does it cost? 3 free lookups to try it. Paid plans start at $19/month. See full pricing.
Can I mix UPS and FedEx in the same sheet? Yes. Put the carrier name in its own column and the same formulas work across every carrier row‑by‑row. That's the whole point — one sheet, every carrier.
The takeaway
Pulling UPS tracking data into Google Sheets used to mean either (a) paying for a logistics SaaS seat per user, or (b) maintaining an Apps Script that breaks every time UPS tweaks their endpoints. Neither is necessary anymore.
Install the add‑on, type =PKG_STATUS_LIVE("1Z...", "UPS", TODAY()), drag down the column, and you're done. Cells stay current automatically. Layer conditional formatting for exceptions and you have a self-updating UPS ops dashboard in the tool your team already knows.
Install Your Package Tracker free from the Google Workspace Marketplace →
Want the same for FedEx, USPS, or DHL? Browse the rest of our articles — every major carrier has its own guide.