Nightly ShipStation shipping log to Google Sheets

Every night at 1am UTC, run a deterministic code workflow that logs the previous calendar day's shipped orders from ShipStation into a Google Sheet so finance and ops have a permanent, queryable record outside the ShipStation UI.

Trigger: cron, nightly at 01:00 UTC. The workflow operates on the calendar day that just ended (UTC midnight to UTC midnight).

Step 1. Compute the previous day's date window as ISO 8601 UTC timestamps: created_at_start = yesterday 00:00:00Z, created_at_end = yesterday 23:59:59Z.

Step 2. Call ShipStation List Labels with status=completed and the date window above. Use page_size=100 and paginate by incrementing page until page >= pages (or until links.next is absent). Collect all label objects across pages into one array. ShipStation V2 base URL is https://api.shipstation.com/v2 and authenticates with the API-Key header.

Step 3. For each label, build a row with this fixed column order: ship_date (label.ship_date or created_at, formatted YYYY-MM-DD), label_id, tracking_number, carrier_id, service_code, ship_to.name, ship_to.country_code, packages[0].weight.value with packages[0].weight.unit appended (e.g. "2.5 ounce"), shipment_cost.amount, insurance_cost.amount, external_order_id. Money fields (shipment_cost, insurance_cost) are objects in ShipStation V2, so extract the .amount number. If insurance_cost is missing or zero, write 0.

Step 4. Append the rows to Google Sheets using Append Values on a configurable spreadsheet_id and sheet/tab name (default tab name: "Shipments"). Use valueInputOption=USER_ENTERED so dates and numbers parse correctly. Send all rows in a single Append Values call (one values array of arrays), not one call per row.

Step 5. If List Labels returns zero results for the day, skip the Append Values call entirely and log a no-op. Do not write an empty or placeholder row.

Assume the destination sheet already has a header row matching the column order above; the workflow does not create or update the header.

Connections needed: ShipStation (API key, V2) and Google Sheets (OAuth, write scope). Make the spreadsheet ID, tab name, and timezone offset configurable inputs.

Additional information

What does this prompt do?

Pulls every order shipped the previous day from ShipStation and writes one row per shipment into a Google Sheet tab you choose.
Captures the fields finance and ops actually use: ship date, tracking number, carrier, service, recipient name and country, weight, shipping cost, insurance cost, and your original order ID.
Runs on a nightly schedule so the spreadsheet always has yesterday's shipments by the time the day starts.
Skips quietly on zero-shipment days instead of writing blank rows.

What do I need to use this?

A ShipStation account with V2 API access (you can grab a key from Settings, API Settings).
A Google account that can edit the destination spreadsheet.
A Google Sheet with a dedicated tab and a header row that matches the columns you want to log.

How can I customize it?

Change the schedule (for example, run at 1am UTC, or at midnight in your warehouse timezone).
Swap the destination spreadsheet or tab name, or split the log into one tab per month.
Add or remove columns (store, warehouse, package dimensions, customs value) by adjusting the field list and the sheet header to match.

Frequently asked questions

Will this work if I ship through multiple carriers?

Yes. ShipStation returns every completed label regardless of carrier, and the carrier ID and service code are written into the row so you can filter or pivot by carrier inside the sheet.

What happens on a day with no shipments?

The workflow finds zero completed labels for that day and exits without writing anything. No blank rows, no empty entries.

Can I backfill historical shipments into the same sheet?

The nightly run only handles yesterday. For a backfill, you can clone the workflow, change the date window to the range you care about, run it once, and then go back to the nightly schedule.

How are shipping cost and insurance amounts written?

They are written as plain numbers in the currency ShipStation returns, so you can sum them, average them, or feed them into a pivot table directly.

Does this duplicate rows if it runs twice?

Each run appends rows for the calendar day it was given. If you run it twice for the same day you will get two sets of rows, so the recommended setup is one nightly run per day.