unifiprice.py

import re
import json
import requests
import pandas as pd
from datetime import datetime

# CONFIG: category slugs -> sheet names
slug_map = {
    "all-cloud-gateways":       "Cloud Gateways",
    "all-switching":            "Switching",
    "all-wifi":                 "Access Points",
    "accessories-cables-dacs":  "Cables & DACs",
    "accessories-modules-fiber":"Modules & Fiber",
    "accessories-poe-power":    "PoE & Power",
    "accessories-access-point": "AP Accessories"
}

# Base page for extracting the buildId
base_page = "https://store.ui.com/us/en/category/all-cloud-gateways"

# 1. Fetch base page and parse __NEXT_DATA__ for buildId
resp = requests.get(base_page)
resp.raise_for_status()

match = re.search(
    r'<script id="__NEXT_DATA__" type="application/json">(.*?)</script>',
    resp.text,
    re.DOTALL
)
if not match:
    raise RuntimeError("Could not locate __NEXT_DATA__ in HTML")

next_data = json.loads(match.group(1))
build_id = next_data.get("buildId")
if not build_id:
    raise RuntimeError("Could not extract buildId from __NEXT_DATA__")

# 2. Fetch each category’s JSON and collect records per slug
records_by_slug = {slug: [] for slug in slug_map}

for slug in slug_map:
    json_url = (
        f"https://store.ui.com/_next/data/{build_id}/us/en/category/"
        f"{slug}.json"
    )
    r = requests.get(json_url, params={
        "store": "us",
        "language": "en",
        "category": slug
    })
    r.raise_for_status()

    subcats = r.json() \
             .get("pageProps", {}) \
             .get("subCategories", [])

    for sub in subcats:
        for prod in sub.get("products", []):
            model = prod.get("title") or prod.get("name", "")
            variants = prod.get("variants", [])
            if not variants:
                continue
            sku = variants[0].get("sku", "").strip()
            price_cents = variants[0] \
                .get("displayPrice", {}) \
                .get("amount", 0)
            price_usd = price_cents / 100.0

            records_by_slug[slug].append({
                "Model": model.strip(),
                "SKU": sku,
                "PRICE (USD)": price_usd
            })

# 3. Build timestamped filename
now = datetime.now()
timestamp = now.strftime("%m-%d-%Y_%H-%M")
filename = f"unifi_pricing_{timestamp}.xlsx"

# 4. Write to Excel, one sheet per category, with currency formatting
with pd.ExcelWriter(filename, engine="xlsxwriter") as writer:
    workbook  = writer.book
    currency_fmt = workbook.add_format({'num_format': '$#,##0.00'})

    for slug, rows in records_by_slug.items():
        sheet = slug_map[slug]
        df = pd.DataFrame(rows)
        df.to_excel(writer, sheet_name=sheet, index=False)

        worksheet = writer.sheets[sheet]
        # Static column widths, change values as needed:
        worksheet.set_column('A:A', 45)              # Column A (model)
        worksheet.set_column('B:B', 40)              # Column B (SKU)
        worksheet.set_column('C:C', 15, currency_fmt)  # Column C (price) with currency

print(f"Fetched {sum(len(v) for v in records_by_slug.values())} items;")
print(f"Saved workbook as `{filename}`")