Hourly AfterShip delivery log in Google Sheets

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.