Export Delivered Order and Fulfillment Details to Google Sheets

When Tracktor detects that a shipment's tracking status has changed to "delivered" (based on carrier tracking data updates), this trigger activates the workflow and captures fulfillment data including order ID, tracking number, carrier information, shipment status, delivery timestamp, and shop URL.

Setup requirement: You must have Tracktor installed and configured to monitor your shipments for this trigger to receive delivery webhooks.

Tracktor context: Tracktor is a Shopify app that monitors carrier tracking for your shipments and provides branded tracking pages. When carriers update tracking status to delivered, Tracktor sends webhooks to MESA triggering this workflow.

This step retrieves the full Shopify order record using the order ID from Tracktor's delivery webhook. It returns complete order data including customer details (name, email, phone), line items with fulfillment status, shipping address, billing information, order total, created date, and fulfillment information. This comprehensive data enables detailed tracking spreadsheets with both fulfillment-level and order-level information.

This step adds a row to a Google Sheets spreadsheet with fulfillment-specific data. The spreadsheet includes 12 columns:

1. Order ID: Shopify internal order ID
2. Order Name (number): Customer-facing order number (like #1001)
3. Order Email: Customer email address
4. Order Phone: Customer phone number
5. Tracking Number: Carrier tracking number
6. Tracking Company: Carrier name (USPS, UPS, FedEx, etc.)
7. Shipping Country: Destination country
8. Shipment Status: Current tracking status (delivered)
9. Created At (date and time): Order creation timestamp
10. Delivery Date (and time): Actual delivery timestamp from carrier
11. Tracking URL: Branded Tracktor tracking page URL
12. Days since Ordered: Column for calculating delivery time (formula needed)

The on_error setting is "replay," meaning if the spreadsheet write fails temporarily, the workflow will automatically retry to ensure no deliveries are lost.

Tracking URL format: The URL uses Tracktor's branded tracking page format with order number and email parameters, providing customers with a branded tracking experience rather than direct carrier links.

This custom JavaScript step validates that all line items in the order are properly fulfilled and require shipping before proceeding.

// Loops through each line item
shopifyOrder.line_items.forEach(item => {
  // Checks if item has no fulfillment status AND requires shipping
  if (item.fulfillment_status == null && item.requires_shipping == true) {
    // Marks order as not ready
    isReadyToSendOrder = false;
  }
});
// Only proceeds to next step if all items are fulfilled
if (isReadyToSendOrder) {
  Mesa.output.next(shopifyOrder);
}

Why this matters for multi-item orders: If an order contains multiple products shipped separately, this code ensures the Orders spreadsheet only gets a row when the LAST package is delivered, preventing duplicate or premature order logging.

Digital products: Items with requires_shipping = false (like digital downloads) don't need fulfillment, so they're excluded from the check.

This step adds a row to a second Google Sheets spreadsheet with complete order information. The spreadsheet includes 16 columns:

1. Order ID: Shopify internal order ID
2. Order Name (number): Customer-facing order number
3. Order Email: Order email (may differ from customer email)
4. Customer First Name: Customer's first name
5. Customer Last Name: Customer's last name
6. Customer Full Name: Complete customer name from default address
7. Customer Email: Customer account email
8. Total Price: Order total including tax and shipping
9. Shipping Address 1: Primary shipping address line
10. Shipping Address 2: Secondary address line (apt, suite, etc.)
11. Shipping City: Shipping city
12. Shipping Zip: Postal code
13. Shipping Province: State/province
14. Shipping Country: Destination country
15. Created At (date and time): Order creation timestamp
16. Tracking URL: Branded Tracktor tracking page URL

This comprehensive customer and shipping data enables customer service teams to quickly look up delivery details, verify addresses, or reference order information.

Two-spreadsheet approach: Separating Fulfillments and Orders spreadsheets provides different analytical views—fulfillment-level for carrier metrics and order-level for customer service and revenue analysis.