`pending-approval/twil_sheet_price.py`

33 lines
#!/usr/bin/env python3
"""Write TWIL email pricing into the TWIL PRices sheet for all 57 Jerry SKUs.
   C (cost)=Jerry list_per_sr ; L (cost/yd)=cost/4 ; O (DW Price)=cost*0.75.
   Only touches rows whose B(mfr) matches a Jerry SKU. DRY default; CONFIRM=1 writes."""
import os, json, gspread
SA="/Users/macstudio3/Projects/secrets-manager/gmc-sa-146735262.json"
KEY="1trKNm-ymqlbs96XJfndDUz19mzH8A11ktaw2lfQ2VQo"
DRY=os.environ.get("CONFIRM","0")!="1"
def norm(s): return (s or "").strip().upper().rstrip("T")
jerry={norm(x['code']):x['list_per_sr'] for x in json.load(open('/tmp/twil_prices.json')) if x['list_per_sr'] is not None}
gc=gspread.service_account(filename=SA); ws=gc.open_by_key(KEY).sheet1
vals=ws.get_all_values()
updates=[]; rows_changed=0
for i,r in enumerate(vals[1:],start=2):
    b=(r[1] or '').strip()
    if not b: continue
    c=jerry.get(norm(b))
    if c is None: continue
    cost=round(c,2); cyd=round(c/4,2); dw=round(c*0.75,2)
    old=( (r[2] or '').strip(), (r[11] or '').strip() if len(r)>11 else '', (r[14] or '').strip() if len(r)>14 else '' )
    if (old[0],old[2])==(f"{cost}",f"{dw}"): 
        pass
    updates.append({'range':f'C{i}','values':[[cost]]})
    updates.append({'range':f'L{i}','values':[[cyd]]})
    updates.append({'range':f'O{i}','values':[[dw]]})
    rows_changed+=1
    print(f"  row {i:>3} {b:>10}  C {old[0]or'∅':>7}->{cost:<7} L->{cyd:<7} O {old[2]or'∅':>7}->{dw}")
print(f"\n{rows_changed} sheet rows to write ({len(updates)} cells)")
if DRY:
    print("[DRY] no write. CONFIRM=1 to apply."); raise SystemExit
ws.batch_update(updates, value_input_option='USER_ENTERED')
print("WROTE.")