Scheduled Order Sync Automation

Overview

A zero-touch automation that reads Matrexify Shopify order exports from a local folder, deduplicates them, normalizes phone numbers and names, and writes them into the team's tracking Google Sheet on a daily schedule. Includes dropdown columns for status and customer sentiment so the operations team can act on the data immediately.

The pipeline started life on GitHub Actions reading from the Google Drive API, then was deliberately simplified to a local Windows Task Scheduler job reading a local exports folder, trading CI portability for operational simplicity on the primary machine.

Problem

Operations teams were manually copying daily order exports into a tracking sheet at the start and middle of every day. Errors and lag were constant. When the responsible person was off, the sync simply didn't happen and the team flew blind.

Approach

Run a Python script on Windows Task Scheduler once a day (3 PM Beirut time). The script reads the latest export from a local folder, deduplicates by order ID against the existing sheet, formats phone numbers and names, appends new rows, and reapplies dropdown formatting so status columns stay editable.

Architecture

• Runtime: Python on a local Windows machine.
• Schedule: Windows Task Scheduler, 3 PM Beirut, daily.
• Auth: Google OAuth2 user credentials with token caching (Sheets API only).
• Input: local exports folder, read directly (no Drive API).
• Dedup: order ID-based, against existing sheet content, plus a processed-files tab to skip re-reading.
• Data transforms: phone numbers normalized to 961XXXXXXXX, first and last names title-cased.
• Status formatting: dropdown columns reapplied via Sheets API on every run, batched up to 500 rows.

Tech stack

• Runtime: Python (pip requirements.txt), local Windows environment
• Automation: Windows Task Scheduler, daily at 15:00
• APIs: Google Sheets API
• Auth: Google OAuth2 user credentials (cached token)

Engineering highlights

• Idempotent: re-running the script never creates duplicates, at both the file and order-ID level.
• Data enrichment: normalizes messy phone formats (+961, leading zeros, spaces, hyphens) to a single 961XXXXXXXX form and title-cases names.
• Column auto-detect: finds the order-ID column from a candidate list, falling back to the first column.
• Documented: README + runbook so the next person can re-auth without reverse-engineering anything.

Outcome

Live, running daily at 3 PM. Eliminates manual sheet updates, enriches order data with normalized phone numbers and formatted names, and gives the team confidence that the tracking sheet is within hours of reality.

Lessons

• Boring automation is often the highest-ROI work. This script saved the team a recurring tax forever.
• Simpler beats portable for a single-machine internal job: dropping the CI runner and Drive API removed two moving parts and a credential to rotate.
• The highest-value lines were the data-cleaning ones: normalized phones and names made the sheet usable downstream, not just populated.