Integration and API Spec

Integration and API Spec

Returns and Reverse Logistics

Shopify is the entry point for the entire workflow and also the execution surface for refunds and inventory adjustments. The automation subscribes to the returns/create webhook for intake and calls the Admin REST API (or GraphQL Admin API) for order lookups, refund creation, and inventory level updates.

// Inbound webhook payload (returns/create)
{ "return": { "id": <return_id>, "order_id": <order_id>, "status": "open",
   "return_line_items": [ { "id": <line_id>, "quantity": 1,
     "fulfillment_line_item": { "line_item": { "sku": "SKU-001",
       "product_id": <product_id> } } } ] } }
// Key fields extracted
return_id, order_id, customer_id, line_item_sku, quantity, reason_code
// Outbound: refund creation
POST /admin/api/2024-04/orders/{order_id}/refunds.json
{ "refund": { "currency": "USD", "notify": false,
   "refund_line_items": [ { "line_item_id": <id>, "quantity": 1,
     "restock_type": "return" } ],
   "transactions": [ { "parent_id": <transaction_id>,
     "amount": "<refund_amount>", "kind": "refund", "gateway": "<gateway>" } ] } }

Google Sheets holds the central returns tracking log. The automation appends a new row when a return is first received and writes back updated values (label URL, send timestamp, inspection outcome, refund amount, credit note ID) as the return progresses through each stage.

// Append new return row
POST /v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/Returns Log!A:O:append
{ "range": "Returns Log!A:O", "majorDimension": "ROWS",
  "values": [ [ <return_id>, <order_id>, <customer_email>, <customer_name>,
    <sku>, <reason_code>, <eligibility_status>, <timestamp_received>,
    "", "", "", "", "", "", "" ] ] }
// Write-back: label URL and send timestamp (columns I, J)
PUT /v4/spreadsheets/{SHEETS_SPREADSHEET_ID}/values/Returns Log!I{row}:J{row}
{ "values": [ [ <label_url>, <label_sent_timestamp> ] ] }

ShipStation handles return label creation and acts as the tracking event source. The automation calls the ShipStation REST API to create return shipments and retrieve label documents, then receives a webhook event when the shipment status changes to 'delivered'.

// Create return shipment
POST /orders/createshipment
{ "orderId": <shipstation_order_id>, "carrierCode": <SHIPSTATION_CARRIER_CODE>,
  "serviceCode": <SHIPSTATION_SERVICE_CODE>, "confirmation": "delivery",
  "shipDate": "<YYYY-MM-DD>", "returnLabel": true }
// Response
{ "shipmentId": <id>, "trackingNumber": "<tracking_no>",
  "labelData": null, "labelUrl": "<label_pdf_url>" }
// Delivery webhook payload (SHIP_NOTIFY)
{ "resource_url": "https://ssapi.shipstation.com/shipments?shipmentId=<id>",
  "resource_type": "SHIP_NOTIFY" }

Gmail sends two customer-facing emails: the return label dispatch email (Agent 2) and the refund confirmation email (Agent 3). Both use a stored HTML template with named placeholders substituted at runtime. Sending is done via the Gmail API using the authenticated user's address or a shared mailbox alias.

// Send label email (Agent 2)
POST /gmail/v1/users/me/messages/send
{ "raw": "<base64url-encoded RFC 2822 message>",
  // Message headers: To: <customer_email>, From: <GMAIL_FROM_ADDRESS>,
  // Subject: Your return label for order #{{order_id}}
  // Body: HTML template with {{customer_name}}, {{label_url}} substituted }
// Send confirmation email (Agent 3)
// Subject: Your refund for order #{{order_id}} has been processed
// Body: HTML template with {{customer_name}}, {{order_id}},
//   {{refund_amount}}, {{return_reason}} substituted

Slack receives a single outbound alert from Agent 2 when a return shipment is marked delivered. The message is posted to the warehouse inspection channel and includes the order number, return reason, and a direct link to the inspection outcome form. The warehouse operative's form submission is the trigger for Agent 3.

// chat.postMessage payload
POST https://slack.com/api/chat.postMessage
{ "channel": <SLACK_WAREHOUSE_CHANNEL_ID>,
  "blocks": [
    { "type": "section", "text": { "type": "mrkdwn",
      "text": "*Return arrived* — Order #{{order_id}}\nReason: {{return_reason}}" } },
    { "type": "actions", "elements": [
      { "type": "button", "text": { "type": "plain_text",
        "text": "Submit inspection outcome" },
        "url": "{{SLACK_INSPECTION_FORM_URL}}?order_id={{order_id}}" } ] }
  ] }

Xero receives a single API call from Agent 3 to create a credit note matching the Shopify refund. The credit note uses the customer's Xero contact record (matched by email), the refund line amount, and the configured revenue account code. No manual finance entry is required after this step.

// Contact lookup by email
GET /api.xro/2.0/Contacts?where=EmailAddress=="{{customer_email}}"
// Response: extract Contacts[0].ContactID

// Create credit note
POST /api.xro/2.0/CreditNotes
{ "Type": "ACCRECCREDIT", "Contact": { "ContactID": "<contact_id>" },
  "Date": "<YYYY-MM-DD>", "LineAmountTypes": "Exclusive",
  "LineItems": [ { "Description": "Return credit — Order #{{order_id}}",
    "Quantity": 1, "UnitAmount": <refund_amount>,
    "AccountCode": <XERO_ACCOUNT_CODE>, "TaxType": <XERO_TAX_TYPE> } ],
  "Reference": "Shopify Order #{{order_id}}",
  "Status": "AUTHORISED" }

// Credential store contents (all values encrypted at rest)
// Shopify
SHOPIFY_API_TOKEN          = <permanent Admin API access token>
SHOPIFY_STORE_DOMAIN       = yourcompany.myshopify.com
SHOPIFY_API_VERSION        = 2024-04
SHOPIFY_WEBHOOK_SECRET     = <HMAC secret from Shopify webhook settings>
SHOPIFY_LOCATION_ID        = <warehouse location ID from Shopify admin>
SHOPIFY_RETURN_WINDOW_DAYS = 30

// Google Sheets
GOOGLE_SERVICE_ACCOUNT_JSON = <service account key JSON, base64-encoded>
SHEETS_SPREADSHEET_ID       = <spreadsheet ID from URL>
SHEETS_TAB_NAME             = Returns Log

// ShipStation
SHIPSTATION_API_KEY         = <API key from ShipStation account settings>
SHIPSTATION_API_SECRET      = <API secret from ShipStation account settings>
SHIPSTATION_CARRIER_CODE    = <e.g. stamps_com>
SHIPSTATION_SERVICE_CODE    = <e.g. usps_first_class_mail>
SHIPSTATION_WAREHOUSE_ID    = <warehouse ID from ShipStation>

// Gmail
GMAIL_CLIENT_ID             = <OAuth 2.0 client ID from Google Cloud Console>
GMAIL_CLIENT_SECRET         = <OAuth 2.0 client secret>
GMAIL_REFRESH_TOKEN         = <long-lived refresh token from OAuth consent>
GMAIL_FROM_ADDRESS          = returns@yourcompany.com
GMAIL_LABEL_TEMPLATE_ID     = <Google Drive file ID of label email HTML template>
GMAIL_CONFIRMATION_TEMPLATE_ID = <Google Drive file ID of confirmation template>

// Slack
SLACK_BOT_TOKEN             = xoxb-<workspace bot token>
SLACK_WAREHOUSE_CHANNEL_ID  = <channel ID, not channel name>
SLACK_INSPECTION_FORM_URL   = https://forms.yourcompany.com/returns-inspection

// Xero
XERO_CLIENT_ID              = <Xero app client ID>
XERO_CLIENT_SECRET          = <Xero app client secret>
XERO_REFRESH_TOKEN          = <OAuth refresh token, updated on each token refresh>
XERO_TENANT_ID              = <organisation tenant ID from Xero>
XERO_ACCOUNT_CODE           = 200
XERO_TAX_TYPE               = NONE
XERO_TRACKING_CATEGORY_ID   = <if applicable, else leave blank>
XERO_TRACKING_OPTION_ID     = <if applicable, else leave blank>

// Inspection form
INSPECTION_FORM_WEBHOOK_SECRET = <shared secret set in form tool webhook config>