AfterShip to Google Sheets delivery log — hourly sync

PromptCreate

Every hour on a cron schedule, sync newly delivered AfterShip shipments into a designated Google Sheets tab so ops has a permanent, filterable delivery log outside AfterShip.

Trigger: cron, hourly. AfterShip is not a supported poll provider on this platform, so the workflow runs on a schedule and calls the AfterShip API itself.

Step 1 — Pull delivered trackings. Call AfterShip List Trackings filtered to tag=Delivered with an updated_at window covering the last hour (updated_at_min = now minus 1 hour, updated_at_max = now). Follow cursor pagination until every page is drained.

Step 2 — Read existing tracking numbers for dedup. Call Google Sheets Get Values on the tracking-number column of the target tab (for example the range Deliveries!A2:A) and load the returned values into an in-memory set. Treat this set as the source of truth for what is already logged.

Step 3 — Append one row per new tracking. For each tracking returned by AfterShip, skip it if its tracking_number is already in the dedup set; otherwise call Google Sheets Append Values on the same tab with a single row containing these columns in this order: tracking number, courier slug, courier display name (from the tracking's courier metadata), order id, customer name, destination country, ship date, delivered date, total transit days (delivered date minus ship date, in whole days), and the AfterShip tracking URL (https://track.aftership.com/{slug}/{tracking_number}). Add the tracking number to the dedup set immediately after a successful append so multiple new rows in the same run cannot duplicate each other.

Configuration inputs the workflow needs: the target Google Sheets spreadsheet id, the tab name (default Deliveries), and the column letter that holds the tracking number (default A). Use valueInputOption=USER_ENTERED on Append Values so dates render as real dates in the sheet.

Reliability notes: this is a deterministic mapping from structured AfterShip fields to fixed sheet columns, no reasoning or drafting. If List Trackings returns zero results in a given hour, exit cleanly without touching the sheet. If a tracking is missing an optional field (order id, customer name, destination country), write an empty cell in that column and continue.

What does this prompt do?

Runs every hour in the background — no one has to remember to pull the report.
Grabs every AfterShip shipment that was marked delivered in the past hour and adds one row per shipment to your delivery log.
Fills in tracking number, courier, order id, customer, destination, ship date, delivered date, transit days, and the AfterShip tracking link.
Skips shipments that already have a row so re-runs never create duplicates.
Gives ops a permanent, filterable record of every delivery outside AfterShip's own dashboard.

What do I need to use this?

An AfterShip account with an API key from Settings > API keys.
A Google account with access to the Google Sheet you want to log deliveries into.
A Google Sheet with a tab (for example Deliveries) and a header row that includes a tracking number column.

How can I customize it?

Change the schedule — run every 15 minutes for higher-volume stores, or daily for a lighter footprint.
Point it at a different tab or a different sheet per brand, region, or courier.
Add or reorder columns (return status, weight, service level) as long as you keep the tracking number column for dedup.
Filter to a specific courier or destination country if you only want part of your shipment volume logged.

FAQs

Will this create duplicate rows if the workflow runs twice?

No. Before appending, it reads the tracking number column in your sheet and skips any shipment that is already there, so re-runs and overlapping time windows are safe.

Does this pull shipments that were delivered before the workflow was set up?

By default it only looks at shipments AfterShip updated in the last hour. If you want to backfill older deliveries, run the workflow once with a wider time window and then let the hourly schedule take over.

What happens on hours when nothing was delivered?

AfterShip returns an empty list and the workflow exits cleanly without touching the sheet. No blank rows, no noise.

Can I log to Excel or Airtable instead of Google Sheets?

Yes. The same pattern works with any spreadsheet or database integration — swap the Google Sheets step for the destination you prefer and keep the deduplication check on the tracking number column.

Which courier does this work with?

AfterShip tracks shipments across more than 1,000 couriers, and this workflow logs whichever ones you have connected — the courier name is written into its own column so you can filter by carrier later.

Related templates

Call overdue Xero customers with an AI collections agent

Every weekday at 10am, an AI voice agent phones your most overdue Xero accounts, logs what each customer promised, and reports back to finance in Slack.