Build a shipment and package tracker

Northwind ships physical deliverables — printed reports, sample kits, signed contracts — and the team likes to know where each parcel is without logging into a carrier site for every tracking number. Checking by hand is dull and easy to skip, so a customer asking “has it arrived?” often gets a vague answer.

This script keeps a Shipments Sheet up to date on its own. It walks every row, skips anything already delivered, calls the carrier’s tracking API for the rest, and writes back the latest status and the time it was checked. Run it on a schedule and the Sheet becomes a live board of every parcel in transit.

What you’ll need

• A Shipments spreadsheet with a header row containing at least: carrier, trackingId, status, and lastChecked.
• A carrier tracking API endpoint that accepts a carrier and tracking ID and returns JSON with a status field. Swap the example URL for your provider’s.
• If the API needs a key, store it in Script Properties rather than the code — see Store API keys and secrets securely.

The script

// The spreadsheet holding outbound shipments.
const SHIPMENTS_SHEET_ID = '1abcShipmentsId';

// The carrier tracking API base URL. Returns JSON with a "status" field.
const TRACKING_API = 'https://api.tracking.example/v1/track';

// Rows already at this status are skipped — no need to re-check.
const FINAL_STATUS = 'delivered';

/**
 * Walks every shipment row, queries the carrier API for anything not
 * yet delivered, and writes back the latest status and check time.
 */
function updateTracking() {
  const sheet = SpreadsheetApp.openById(SHIPMENTS_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;

  // Bail out if there is nothing but a header row.
  if (!rows.length) {
    Logger.log('No shipments to track — nothing to do.');
    return;
  }

  // Map header names to column indexes so the code reads clearly.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  rows.forEach((r, i) => {
    // Skip parcels that have already arrived.
    if (r[col.status] === FINAL_STATUS) return;

    // Ask the carrier API for this tracking number.
    const url = TRACKING_API +
      '?carrier=' + encodeURIComponent(r[col.carrier]) +
      '&id=' + encodeURIComponent(r[col.trackingId]);
    const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });

    // A non-200 response means leave the row untouched this round.
    if (res.getResponseCode() !== 200) return;

    const data = JSON.parse(res.getContentText());

    // Write the fresh status and timestamp back into the value grid.
    values[i + 1][col.status] = data.status;
    values[i + 1][col.lastChecked] = new Date();
  });

  // One write for the whole grid — far faster than cell by cell.
  sheet.getDataRange().setValues(values);
  Logger.log('Tracking update complete.');
}

How it works

1. updateTracking opens the shipments sheet and reads the entire grid in one getValues call, splitting off the header row.
2. If there are no data rows it logs a message and stops.
3. It builds a col lookup so the rest of the code refers to columns by name (col.status) rather than fragile numeric indexes.
4. For each row it skips anything already marked delivered — those parcels never need another API call.
5. For every remaining row it builds the tracking URL, URL-encoding the carrier and ID, and fetches it with muteHttpExceptions so a bad response does not halt the loop.
6. On a successful response it parses the JSON and writes the new status and the current time into the in-memory values grid (offset by one for the header).
7. After the loop it pushes the whole grid back with a single setValues call — one write instead of one per row.

Example run

Before a run the Shipments sheet might look like this:

carrier	trackingId	status	lastChecked
Royal Mail	RM12345	in transit	2026-05-24 06:00
DPD	DP98765	delivered	2026-05-23 18:00
FedEx	FX55512	label created	2026-05-24 06:00

After a run the DPD row is left alone, and the other two are refreshed:

carrier	trackingId	status	lastChecked
Royal Mail	RM12345	out for delivery	2026-05-25 10:00
DPD	DP98765	delivered	2026-05-23 18:00
FedEx	FX55512	in transit	2026-05-25 10:00

Trigger it

Run this on a time-based trigger so the board stays current:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for updateTracking.
3. Choose a time-driven trigger, hour timer, every 4 hours — frequent enough to be useful without burning through API quota.

Watch out for

• API quotas are real. Every non-delivered row is one request per run; a long in-transit list checked every four hours adds up. Widen the interval or batch the calls if you hit a limit.
• The script trusts the API’s status string. If the carrier returns a different shape or field name, parsing will fail — log res.getContentText() once to confirm the structure before relying on it.
• A failed request leaves the row’s old status in place. That is deliberate, but it means a persistently broken endpoint will show stale data with no warning.
• setValues rewrites the whole grid, so do not add formulas to the tracked range — they will be overwritten with their last computed values.
• UrlFetchApp allows 20,000 calls a day on consumer accounts. A large shipments sheet checked frequently can approach that ceiling.