`build_sheet_grs.py`

57 lines
#!/usr/bin/env python3
"""Build data/sheet_grs.json — EVERY GRS item on the TWIL PRices sheet with full info,
   so the photo app can create+go-live a not-yet-in-Shopify GRS SKU on first photo.
   Columns: B mfr(1) C cost(2) D pattern(3) I alt(8) J desc(9) K color(10) P width(15) Q length(16) R series(17) S js(18)"""
import json,re,os,gspread
SA="/Users/macstudio3/Projects/secrets-manager/gmc-sa-146735262.json"
KEY="1trKNm-ymqlbs96XJfndDUz19mzH8A11ktaw2lfQ2VQo"
OUT=os.path.join(os.path.dirname(__file__),"data","sheet_grs.json")
# material from Jerry prices if available
MATS=[("HERRINGBONE","Herringbone"),("SMALL DIAMOND","Small Diamond"),("PANAMA","Panama Weave"),
("PAPERWEAVE","Paperweave"),("PAPERWEAV","Paperweave"),("BURLAP","Burlap"),("JUTE","Jute"),
("RAFFIA","Raffia"),("ABACA","Abaca"),("ARROWROOT","Arrowroot"),("MICA","Mica"),("HEMP","Hemp"),
("CORK","Cork"),("BOODLE","Boodle"),("SISAL","Sisal")]
def material(blob):
    b=(blob or '').upper()
    for kw,lab in MATS:
        if kw in b: return lab
    return ""
def tc(s): return " ".join(w[:1].upper()+w[1:].lower() if w else w for w in (s or "").split())
gc=gspread.service_account(filename=SA)
vals=gc.open_by_key(KEY).sheet1.get_all_values()
def g(r,i): return (r[i] or '').strip() if len(r)>i else ''
seen={}
for r in vals[1:]:
    mfr=g(r,1)
    grs=None
    for col in (17,18,22):                    # R series, S js, W shopify-sku — all hold GRS-xxxxx
        m=re.search(r'(GRS-\d+)',g(r,col).upper())
        if m: grs=m.group(1); break
    if grs and grs not in seen:
        cost=None
        try:
            c=float(g(r,2));  cost=c if c>0 else None
        except: pass
        pat=g(r,3); alt=g(r,8); col_=g(r,10); desc=g(r,9)
        # name: pattern col, else leading word(s) of alt before 'grasscloth/wallcovering'
        name=pat
        if not name and alt:
            mm=re.match(r"(.+?)\s+(grasscloth|wallcovering)",alt,re.I)
            if mm: name=mm.group(1).strip()
        if name.strip().lower() in ('grasscloth','wallcovering'): name=''
        mat=material(desc+' '+pat+' '+alt)
        seen[grs]={
            "grs":grs,"mfr":mfr,"name":tc(name),"color":tc(col_),"material":mat,
            "cost":cost,"price":round(cost*0.75,2) if cost else None,
            "width":tc(g(r,15)) or "36 Inches","length":tc(g(r,16)) or "8 Yards",
            "desc":desc,"img1":g(r,6),"img2":g(r,7)
        }
items=list(seen.values())
json.dump(items,open(OUT,"w"),indent=2)
priced=sum(1 for x in items if x["price"])
named=sum(1 for x in items if x["name"])
print(f"GRS items on sheet: {len(items)} | priced: {priced} | named: {named} -> {OUT}")
for ex in ("GRS-26810","GRS-30710","GRS-27080"):
    h=[x for x in items if x["grs"]==ex]
    print(f"  {ex}: {h[0] if h else 'NOT FOUND'}" if h else f"  {ex}: NOT FOUND")