`analyze_colors.py`

71 lines
#!/usr/bin/env python3
"""
analyze_colors.py — READ-ONLY. For every tab, tally the EXACT background color of every
non-white, non-enrichment-green cell, BY COLUMN, so we can see which column carries each
row's status color-coding. Writes NOTHING.
"""
import sys, os, json, urllib.request, urllib.parse
sys.path.insert(0, os.path.dirname(os.path.abspath(__file__)))
import lib

def api(url, tok):
    r = urllib.request.Request(url, headers={"Authorization": f"Bearer {tok}"})
    return json.load(urllib.request.urlopen(r))

G = lib.GREEN
def is_green(bg):
    return bool(bg) and abs(bg.get("red",1)-G["red"])<0.06 and \
        abs(bg.get("green",1)-G["green"])<0.06 and abs(bg.get("blue",1)-G["blue"])<0.06
def hexc(bg):
    if not bg: return None
    r,g,b = bg.get("red",0.0),bg.get("green",0.0),bg.get("blue",0.0)
    if r>0.96 and g>0.96 and b>0.96: return None
    if (max(r,g,b)-min(r,g,b))<0.04 and min(r,g,b)>0.91: return None
    return "#%02x%02x%02x" % (round(r*255),round(g*255),round(b*255))

tok = lib.access_token()
sheets = [(s["properties"]["sheetId"], s["properties"]["title"])
          for s in lib.get_meta(tok)["sheets"]]
qs = "&".join("ranges="+urllib.parse.quote("'"+t.replace("'","''")+"'",safe="") for _,t in sheets)
# values (for headers) + backgrounds
vals = api(f"{lib.API}/{lib.SID}/values:batchGet?{qs}&majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE", tok)
vr = {t: v.get("values",[]) for (_,t),v in zip(sheets, vals.get("valueRanges",[]))}
fmt = api(f"{lib.API}/{lib.SID}?{qs}&includeGridData=true"
          "&fields=sheets(properties(sheetId,title),data.rowData.values.effectiveFormat.backgroundColor)", tok)

def header_row(rows):
    best,bi=-1,0
    for i in range(min(4,len(rows))):
        n=sum(1 for c in rows[i] if str(c).strip())
        if n>best: best,bi=n,i
    return bi

overall={}
print("=== PER-TAB, PER-COLUMN COLOR TALLY (data rows only) ===")
for sh in fmt.get("sheets",[]):
    gid=sh["properties"]["sheetId"]; title=sh["properties"]["title"]
    rows=vr.get(title,[])
    if not rows: continue
    hr=header_row(rows)
    hdr=[str(x).strip() for x in rows[hr]]
    grid=(sh.get("data") or [{}])[0].get("rowData",[])
    colcolors={}  # col -> {hex:count}
    for ri,row in enumerate(grid):
        if ri<=hr: continue
        for ci,cell in enumerate(row.get("values",[]) or []):
            bg=(cell.get("effectiveFormat") or {}).get("backgroundColor")
            if is_green(bg): continue
            hx=hexc(bg)
            if hx:
                colcolors.setdefault(ci,{}); colcolors[ci][hx]=colcolors[ci].get(hx,0)+1
                overall[hx]=overall.get(hx,0)+1
    if not colcolors: continue
    print(f"\n## {title}  (gid={gid}, header_row={hr}, cols={len(hdr)})")
    for ci in sorted(colcolors):
        h = hdr[ci] if ci<len(hdr) else ""
        dist=", ".join(f"{k}:{v}" for k,v in sorted(colcolors[ci].items(),key=lambda kv:-kv[1]))
        print(f"  col{ci:>2} [{h[:28]:28}] {dist}")
print("\n=== OVERALL COLOR TOTALS ===")
for k,v in sorted(overall.items(),key=lambda kv:-kv[1]):
    print(f"  {k}: {v}")