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("1Z999AA10123456784", "UPS")

You'll get back something like Delivered, In Transit, Out for Delivery, or Exception — refreshed live from UPS's tracking network. That's the whole setup.

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(B2, C2)

In E2:

=PKG_SUMMARY(B2, C2)

(Optional) in F2:

=PKG_LAST_EVENT(B2, C2)

You'll see In Transit, Denver, CO — Departed UPS Facility (2d ago), and so on. Ready to be sorted, filtered, charted, or color‑coded.

4. Apply to many rows the right way

Do not select D2:F2 and drag the fill handle to row 500 all at once. Every single formula will fire a UPS lookup in the same second, UPS will rate‑limit, and your sheet will fill with errors.

Instead, use Bulk Track Packages:

1. Menu bar → Your Package Tracker → Bulk Track Packages.
2. Tracking range: B2:B500.
3. Carrier range: C2:C500.
4. Output: Summary (most useful) or Status.
5. Click Start Bulk Tracking.

The add‑on paces the requests, caches duplicates, skips already‑delivered rows by default, and writes the results into the column immediately after your carrier column. You can close the dialog and come back — it keeps running in the background.

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:

1. Import the CSV into a Google Sheet. You should have columns for order ID, tracking number, and carrier (UPS).
2. Add a Status column with =PKG_STATUS(B2, C2).
3. Run Bulk Track Packages over the full range once every morning and once at end of day.
4. Pivot: count rows by status. Your dashboard now shows how many are delivered, in transit, or in exception — no separate tool.
5. Wire a filter view for Exception. Hand that view to your customer service lead as their daily callback list.

Setup time: under 30 minutes. Ongoing work per day: maybe 2 minutes to trigger bulk tracking.

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 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? Results are cached per tracking number to save your quota. Cached reads are free. If you need a hard refresh (e.g. you just updated a label), use =PKG_REFRESH(B2, C2) for that cell — it bypasses the 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("1Z...", "UPS"), and you're done. Scale it with Bulk Track Packages when the list gets long, layer conditional formatting for exceptions, and you have a live 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.