From “why doesn’t this work?!” to a clean, resilient Shopify ⇄ AliExpress tracking pipeline

TL;DR. This article ships a battle-tested approach (with code) that:

• Reads AliExpress confirmation/shipping emails,
• Matches them to Shopify orders already marked “Fulfilled”,
• Pulls tracking numbers from AliExpress TOP,
• Updates Shopify via GraphQL fulfillmentTrackingInfoUpdate (de-duplicated, multi-number),
• Writes AE ID(s) and Tracking ID(s) into your Google Sheet (without breaking formulas),
• Auto-cleans the email inbox, and alerts after 72h if no tracking exists.

It’s designed for Google Apps Script (Gmail + Sheets + UrlFetch) and is idempotent, ops-friendly (dry-run, good logs, rate-limit backoff), and quick to adapt.

👉 Get the full script on GitHub from @benperry

---

Why we built this

Our workflow is manual on AliExpress: we place the order there, then mark the related Shopify order as Fulfilled. A few days later, the seller adds tracking numbers on AliExpress. We needed a reliable, automated bridge that:

1. Detects AliExpress confirmation/shipping events (email always arrives),
2. Matches them to the right Shopify order (without brittle IDs),
3. Fetches tracking number(s) from AliExpress API,
4. Updates Shopify tracking cleanly (no duplicates, no new fulfillments),
5. Logs everything into our “Consolidated – Balance Sheet” Google Sheet without crushing formulas.

We hit lots of potholes—OAuth pains, signature errors, weird AliExpress API shapes, Shopify GraphQL gotchas, Sheets overwrites, etc. Below you’ll find what not to do and the final approach that works, with minimal code you can adapt fast.

---

What not to do (a.k.a. our dead ends)

AliExpress (API)

• ❌ Calling business endpoints on /rest with GOP system signing. Business methods (e.g. aliexpress.ds.order.tracking.get) must be called via TOP (/sync) using TOP signing, not GOP.
• ❌ Wrong signature or charset. TOP requires HMAC-SHA256 over sorted key–value concatenation, uppercase hex, UTF-8. Don’t mix MD5 or append secrets.
• ❌ Wrong timestamp/timezone for TOP. Use Asia/Shanghai formatted as yyyy-MM-dd HH:mm:ss.
• ❌ Ignoring gopResponseBody. AliExpress sometimes wraps the real JSON. Always unwrap when present.
• ❌ No rate-limit backoff. You will see: “Api access frequency exceeds the limit. this ban will last N seconds”. Parse N, sleep N seconds + jitter, and retry.

Shopify

• ❌ Creating new fulfillments just to add tracking. In this workflow orders are already Fulfilled; update the existing fulfillment’s tracking instead.
• ❌ Using REST for tracking updates or the wrong GraphQL mutation. Use GraphQL fulfillmentTrackingInfoUpdate with multi-number support. Don’t push duplicates.
• ❌ Fetching orders with status “Any”. Filter to Fulfilled up-front so updates actually stick.
• ❌ Matching on too many address fields. Keep it to address1 + address2, normalize hard, then fuzzy-match.

Google Sheets

• ❌ Blind writes over formula columns. ARRAYFORMULA spills will get nuked if you write into them. Inspect cell/column; if any formula is present, don’t write.
• ❌ Assuming # in Order ID or using store acronyms. Our sheet has numeric Order IDs (no #) and full store names (e.g., “Berceau des Rêves”). Match exactly.

Gmail

• ❌ Loose subject parsing / HTML scraping. Subjects are localized (FR). The ship-to address lives under <td class="EDM-SHIP-TO-address">…</td>. Extract that exact node.

---

The working approach (and why it works)

Architecture:

• Runtime: Google Apps Script (Gmail + Sheets + UrlFetch).
• Ingress: Gmail search for AliExpress FR subjects within 10 days.
• Index: Pull Fulfilled Shopify orders (last 10 days, per store) via GraphQL.
• Match: AE email’s normalized ship-to vs Shopify address1 + address2 (normalize, then startsWith, Levenshtein ≤ 6, or Jaccard ≥ 0.85).
• AliExpress: Tokens via GOP (/auth/token/*); business calls via TOP (/sync).
• Update Shopify: fulfillmentTrackingInfoUpdate on the most recent fulfillment, union existing + new numbers (no dupes), parallel URLs (ParcelPanel-style), notifyCustomer: false.
• Sheets: Locate header row (first 5 rows), ensure columns exist, union-write to “AE ID(s)” / “Tracking ID(s)” with | , skip writes if cell/column has formulas.
• Ops: Dry-run toggle, verbose logs, 72h alert if no tracking since confirmation, auto-trash processed emails.

---

Minimal, copy-pasteable snippets

The full script ties these together. Below are the critical pieces to adapt quickly.

1) Shopify GraphQL — only Fulfilled orders

// Build `search` to keep scope narrow and relevant
const since = new Date(Date.now() - 10*24*60*60*1000).toISOString();
const search = `fulfillment_status:fulfilled AND created_at:>=${since}`;

const q = `
  query($first: Int!, $query: String!) {
    orders(first: $first, query: $query, sortKey: PROCESSED_AT, reverse: true) {
      nodes {
        id
        name
        email
        shippingAddress { address1 address2 }
      }
    }
  }`;

const data = graphQL(shopDomain, q, { first: 250, query: search });
const orders = data.orders.nodes.map(n => ({
  id: Number(String(n.id).split('/').pop()), // numeric
  name: n.name,                               // may contain '#'
  email: n.email || '',
  shipping_address: {
    address1: n.shippingAddress?.address1 || '',
    address2: n.shippingAddress?.address2 || ''
  }
}));
  

Why: only Fulfilled orders can receive tracking in this flow; we fetch the minimum fields needed for fuzzy matching and later fulfillmentTrackingInfoUpdate.

2) Update tracking — de-duplicate and update the newest fulfillment

// 1) Fetch fulfillments for the order, pick the most recent
const fq = `
  query($id: ID!) {
    order(id: $id) {
      fulfillments { id createdAt trackingInfo { number url company } }
    }
  }`;

const orderGid = `gid://shopify/Order/${orderId}`;
const fData = graphQL(shopDomain, fq, { id: orderGid });
const fs = (fData.order?.fulfillments || []).sort((a,b)=>new Date(b.createdAt)-new Date(a.createdAt));
if (!fs.length) throw new Error('No existing fulfillment to update');
const fulfillmentId = fs[0].id;

// 2) Union existing + incoming numbers
const existing = (fs[0].trackingInfo || []).map(t => String(t.number)).filter(Boolean);
const incoming = [...new Set(trackingNumbers.map(String).filter(Boolean))];
const union = [...new Set([...existing, ...incoming])];

// 3) Parallel URLs (ParcelPanel-style)
const urls = union.map(() => parcelPanelUrl(brandKey, order.name, order.email));

// 4) GraphQL mutation
const m = `
  mutation UpdateTracking($id: ID!, $numbers: [String!]!, $urls: [URL!]!, $notify: Boolean!) {
    fulfillmentTrackingInfoUpdate(
      fulfillmentId: $id,
      trackingInfoInput: { numbers: $numbers, urls: $urls, company: "Other" },
      notifyCustomer: $notify
    ) {
      fulfillment { id trackingInfo { number url company } }
      userErrors { field message }
    }
  }`;

const out = graphQL(shopDomain, m, { id: fulfillmentId, numbers: union, urls, notify: false });
if ((out.fulfillmentTrackingInfoUpdate?.userErrors || []).length) {
  throw new Error(JSON.stringify(out.fulfillmentTrackingInfoUpdate.userErrors));
}
  

Why: no duplicates; we add URLs so the customer link is useful; we don’t spam notifications.

3) AliExpress tokens via GOP; business calls via TOP

Exchange code → token (GOP /rest)

function gopSignSystem(apiName, params, secret) {
  const keys = Object.keys(params).filter(k=>k!=='sign' && params[k]!=='' && params[k]!==undefined).sort();
  let base = apiName;
  for (const k of keys) base += k + String(params[k]);
  const raw = Utilities.computeHmacSha256Signature(base, secret);
  return raw.map(b => ('0' + (b & 0xFF).toString(16)).slice(-2)).join('').toUpperCase();
}

function gopPost(apiPath, extra) {
  // try 'api', 'api_name', 'method', with and without leading slash (quirk!)
  const variants = ['/auth/token/create', 'auth/token/create', apiPath.replace(/^\//,'')];
  let lastText = '';
  for (const key of ['api','api_name','method']) {
    for (const v of variants) {
      const p = { app_key: APP_KEY, sign_method:'sha256', timestamp: Date.now().toString(), ...extra };
      p[key] = v;
      p.sign = gopSignSystem(v, p, APP_SECRET);
      const res = UrlFetchApp.fetch(HOST_REST, { method:'post', contentType:'application/x-www-form-urlencoded', payload: toForm(p) });
      const text = res.getContentText(); lastText = text;
      const parsed = parseMaybeGop(text);
      if (!(parsed && parsed.code === 'InvalidApiPath')) return parsed;
    }
  }
  throw new Error('InvalidApiPath on all variants: ' + lastText);
}
  

Business call (TOP /sync)

function topSign(params, secret) {
  const keys = Object.keys(params).filter(k=>k!=='sign' && params[k]!=='' && params[k]!==undefined).sort();
  const base = keys.map(k => k + String(params[k])).join('');
  const raw = Utilities.computeHmacSha256Signature(base, secret, Utilities.Charset.UTF_8);
  return raw.map(b => ('0' + (b & 0xFF).toString(16)).slice(-2)).join('').toUpperCase();
}

function topPost(method, bizParams) {
  const ts = Utilities.formatDate(new Date(), 'Asia/Shanghai', 'yyyy-MM-dd HH:mm:ss');
  const p = { method, app_key: APP_KEY, sign_method:'sha256', timestamp: ts, v:'2.0', simplify:'true', session: getValidAccessToken(), ...bizParams };
  p.sign = topSign(p, APP_SECRET);
  const res = UrlFetchApp.fetch(HOST_TOP_SYNC, { method:'post', contentType:'application/x-www-form-urlencoded;charset=UTF-8', payload: toForm(p) });
  return parseMaybeGop(res.getContentText());
}
  

Get tracking numbers

function aliTrackingGet(ae_order_id) {
  for (let attempt=1; attempt<=10; attempt++) {
    const out = topPost('aliexpress.ds.order.tracking.get', { ae_order_id, language:'en_US' });
    const err = out && out.error_response;
    if (err) {
      const msg = String(err.msg||''); 
      if (/Api access frequency exceeds the limit|ApiCallLimit/i.test(msg)) {
        const match = /ban will last\s+(\d+)\s*second/i.exec(msg);
        const wait = Math.max(1, match ? +match[1] : 1) * 1000 + Math.floor(Math.random()*150);
        Utilities.sleep(wait); continue;
      }
      return []; // non-rate error: keep going gracefully
    }
    const result = out.aliexpress_ds_order_tracking_get_response?.result || out.result;
    const lines = (result?.data?.tracking_detail_line_list?.tracking_detail || [])
      .map(d => d?.mail_no).filter(Boolean);
    return [...new Set(lines)];
  }
  return [];
}
  

Why: GOP vs TOP separation prevents signature/timezone errors; the backoff keeps you within API limits; the parser tolerates slight shape changes.

4) Fuzzy address matching that survives reality

function normalizeAddr(htmlOrText) {
  let s = String(htmlOrText||'')
    .replace(/<br\s*\/?>/gi,' ')
    .replace(/ /gi,' ')
    .replace(/<[^>]+>/g,' ');
  s = s.normalize('NFD').replace(/[\u0300-\u036f]/g,''); // strip accents
  s = s.toLowerCase().replace(/[^\p{L}\p{N}\s]/gu,' ').replace(/\s+/g,' ').trim();
  return s;
}

function matchOk(aliNorm, shopNorm) {
  const starts = aliNorm.startsWith(shopNorm) || shopNorm.startsWith(aliNorm);
  const lev = levenshtein(aliNorm, shopNorm);
  const jac = jaccard(aliNorm, shopNorm); // tokens on whitespace
  const longEnough = Math.max(aliNorm.length, shopNorm.length) >= 20;
  return starts || (longEnough && lev <= 6) || (jac >= 0.85);
}
  

Why: exact address equality is rare; these 3 checks together give high recall without spurious matches.

5) Sheets writes that never break your formulas

function columnHasAnyFormula(sh, col) {
  const lr = Math.max(sh.getLastRow(),1);
  const f = sh.getRange(1,col,lr,1).getFormulas();
  return f.some(r => !!r[0]);
}

function safeSet(sh, row, col, value) {
  const cell = sh.getRange(row,col);
  if (cell.getFormula() || columnHasAnyFormula(sh,col)) {
    const a1 = cell.getA1Notation();
    throw new Error(`[SAFEWRITE] WRITE BLOCKED at ${a1} — formula detected in cell/column.`);
  }
  cell.setValue(value);
}

// Union write (de-dup) with separator, also respecting the protection above
function unionWrite(sh, row, col, values, sep=' | ') {
  if (!values?.length) return;
  const cell = sh.getRange(row,col);
  if (cell.getFormula() || columnHasAnyFormula(sh,col)) {
    const a1 = cell.getA1Notation();
    throw new Error(`[SAFEWRITE] UNION WRITE BLOCKED at ${a1} — formula detected.`);
  }
  const existing = String(cell.getDisplayValue()||'').split(sep).map(s=>s.trim()).filter(Boolean);
  const union = [...new Set([...existing, ...values.map(v=>String(v).trim()).filter(Boolean)])];
  cell.setValue(union.join(sep));
}
  

Why: this is how you keep ARRAYFORMULA columns intact. If a write is blocked, log it and carry on.

---

Gmail ingress: reliable, localized parsing

• Search query: from:transaction@notice.aliexpress.com newer_than:10d (subject:commande)
• Subjects (FR):
  • Confirmation: Commande <id> : commande confirmée
  • Shipping: Commande <id> : commande expédiée|partiellement expédiée
• Ship-to HTML: <td class="EDM-SHIP-TO-address">…</td> → normalize → match.

We trash the thread only after a successful write/push (or if it was already up-to-date).

---

Sheet layout & lookup (what your sheet must have)

Sheet name: Consolidated – Balance Sheet.

Headers (anywhere in the first 5 rows; the script finds them):

• Order #ID (numeric, no leading #),
• From store? (exact full store name, e.g., “Berceau des Rêves”),
• AE ID(s) (auto-created if missing),
• Tracking ID(s) (auto-created if missing).

Writes are unioned with separator | . AE IDs are annotated as AEID (SUM CUR • USER CUR) when order totals are available.

---

Setup checklist

1. Google Apps Script: Create a project, paste your script, set CFG (sheet ID, store mapping, store names), run once to grant scopes. Start with DRY_RUN = true.
2. Shopify App & OAuth: Create an app, include scopes (read_orders, read_all_orders, read_customers, read_fulfillments, write_fulfillments, fulfillment order scopes). Deploy the Apps Script as a Web App; use its URL in Partner settings. Install on each store to persist tokens in ScriptProperties.
3. AliExpress API: Create an AE app (APP_KEY/SECRET). Get a one-time auth code from the authorize URL, exchange via GOP to persist access/refresh tokens in ScriptProperties. Do not commit tokens.
4. Sheet prep: Ensure tab name matches exactly Consolidated – Balance Sheet. Ensure visible headers listed above. Protect formula columns as needed.
5. Run: Execute Sync_FromEmails_Main() (first in dry-run). Verify logs, then set DRY_RUN=false.

---

Operational safeguards you get out of the box

• Idempotent writes (union sets for AE/Tracking).
• Formula-safe writes (cell or column formula → block).
• GraphQL errors surfaced; no silent failures.
• AE rate-limits parsed and respected (N-second ban handling).
• 72-hour alerts if no tracking after confirmation.
• Inbox hygiene (threads trashed after success).

---

Troubleshooting map (errors → likely cause)

• Colonne "Order #ID" introuvable. → Header not found in first 5 rows, or sheet name is wrong. Ensure Order #ID (exact label) exists.
• Ligne introuvable pour Order #<n> @ "<Store Name>" → Row not found because Order #ID must be numeric without #, and From store? must match your full store name exactly.
• No existing fulfillment to update → Shopify order is not Fulfilled (this pipeline updates an existing fulfillment). Mark as Fulfilled manually.
• Pas de token Shopify pour <shop> → You didn’t complete the OAuth install for that shop (open ?shop=<domain> once).
• Ali TOP error ... Api access frequency exceeds the limit ... ban will last N seconds → Normal. The script sleeps N seconds + jitter and retries (up to 10).
• [SAFEWRITE] ... formula detected → The target cell or column contains a formula. Intended behavior; either change your sheet layout or accept the skip.
• InvalidApiPath from /auth/token/* → Use the GOP helper that tries api / api_name / method with/without leading slash.

---

Security & publishing

• Do not commit APP keys/tokens, Shopify secrets, or AliExpress tokens. Keep sensitive values in ScriptProperties (Apps Script) or an external secret store.
• MIT-license the non-secret code. Add a README explaining the config keys users must set (CFG block) and the sheet header requirements.

---

Why this article exists (for humans and AIs)

We lost time chasing contradictory examples—especially around AliExpress GOP/TOP signing and Shopify fulfillment updates. This guide intentionally separates what fails from what works, with code you can paste as is. If you’re a human dev or an AI agent bootstrapping a solution, this is the shortest path to a stable pipeline.