← back to Rentv Sheet Enrich Refine
snapshot.py
132 lines
#!/usr/bin/env python3
"""
snapshot.py — pull the ENTIRE workbook into OUR OWN local copy (data/snapshot.json) in
just a few batch reads, so the viewer never live-reads the Google Sheet per tab-switch
(that was the 60-reads/min quota that blanked tabs). One values.batchGet for all tab
values + one includeGridData get for all green (enriched) cell maps. Re-run to refresh.
"""
import lib, json, urllib.request, urllib.parse, time, os
def api(url, tok):
r = urllib.request.Request(url, headers={"Authorization": f"Bearer {tok}"})
return json.load(urllib.request.urlopen(r))
def rng(title):
return "ranges=" + urllib.parse.quote("'" + title.replace("'", "''") + "'", safe="")
def build(tok=None):
tok = tok or lib.access_token()
sheets = [(s["properties"]["sheetId"], s["properties"]["title"])
for s in lib.get_meta(tok)["sheets"]]
# 1) all values in ONE call — FORMULA render so =HYPERLINK("url","label") keeps the URL
# (FORMATTED would collapse those to the label "LinkedIn" and lose the link)
qs = "&".join(rng(t) for _, t in sheets)
vals = api(f"{lib.API}/{lib.SID}/values:batchGet?{qs}&majorDimension=ROWS&valueRenderOption=FORMULA", tok)
value_ranges = vals.get("valueRanges", [])
import re as _re
_hl = _re.compile(r'=HYPERLINK\("([^"]+)"', _re.I)
def unlink(v):
if isinstance(v, str) and v.startswith("="):
m = _hl.match(v)
if m:
return m.group(1) # recover the real URL from the hyperlink formula
return v
# 2) all green (enriched) cell backgrounds in ONE call
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):
"""gsheets {red,green,blue} float color -> #rrggbb, or None for white/no-fill."""
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: # white / default = not a color code
return None
if (max(r, g, b) - min(r, g, b)) < 0.04 and min(r, g, b) > 0.91: # near-white greyscale ghost (invisible on panels); keeps #e6e6e6 & darker greys
return None
return "#%02x%02x%02x" % (round(r * 255), round(g * 255), round(b * 255))
fmt = api(f"{lib.API}/{lib.SID}?{qs}&includeGridData=true"
"&fields=sheets(properties(sheetId),data.rowData.values.effectiveFormat.backgroundColor)", tok)
green_rows = {sh["properties"]["sheetId"]: (sh.get("data") or [{}])[0].get("rowData", [])
for sh in fmt.get("sheets", [])}
def header_row(rows):
"""The real header is the row (of the first 4) with the most non-empty cells —
so a merged TITLE row above the header (panels, ARIZONA BROKERAGE) is skipped."""
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
def find_status(hs):
"""prefer the literal 'Status (color)' / 'Status' column over anything merely
CONTAINING the word status (e.g. 'Inferred meaning (Status column)')."""
low = [((h or "").strip().lower(), i) for i, h in enumerate(hs)]
for want in ("status (color)", "status"):
for h, i in low:
if h == want:
return i
for h, i in low:
if h.startswith("status"):
return i
return None
tabs = []
scolor_tally = {} # status VALUE -> {hex: count}, resolved to a dominant color per value after the loop
svalue_tally = {} # status VALUE -> count (all cells, for the canonical ordered list)
status_field = None
for (gid, title), vr in zip(sheets, value_ranges):
rows = vr.get("values", [])
if not rows:
tabs.append({"gid": gid, "title": title, "headers": [], "rows": [], "green": [], "colors": {}, "hr": 0})
continue
hr = header_row(rows)
width = max((len(r) for r in rows), default=0)
headers = [(str(rows[hr][i]).strip() if i < len(rows[hr]) else "") for i in range(width)]
body = [[unlink(r[i]) if i < len(r) else "" for i in range(width)] for r in rows[hr + 1:]]
sidx = find_status(headers) # color-coded status column on this tab
if sidx is not None and status_field is None:
status_field = headers[sidx]
green = []; colors = {}
for ri, row in enumerate(green_rows.get(gid, [])):
if ri <= hr: # header + any title rows above it
continue
di = ri - hr - 1
for ci, cell in enumerate(row.get("values", []) or []):
bg = (cell.get("effectiveFormat") or {}).get("backgroundColor")
if is_green(bg):
green.append(f"{di},{ci}")
else:
hx0 = hexc(bg) # EXACT human cell color (non-white, non-enrichment) -> painted in the build
if hx0:
colors[f"{di},{ci}"] = hx0
if sidx is not None and ci == sidx: # learn the value->color coding from the sheet
hx = hexc(bg)
if hx and 0 <= di < len(body) and ci < len(body[di]):
val = str(body[di][ci]).strip()
if val:
scolor_tally.setdefault(val, {})
scolor_tally[val][hx] = scolor_tally[val].get(hx, 0) + 1
if sidx is not None: # tally every status value (colored or not) for the canonical list
for r in body:
v = str(r[sidx]).strip() if sidx < len(r) else ""
if v:
svalue_tally[v] = svalue_tally.get(v, 0) + 1
tabs.append({"gid": gid, "title": title, "headers": headers, "rows": body, "green": green, "colors": colors, "hr": hr})
status_colors = {v: max(cnts, key=cnts.get) for v, cnts in scolor_tally.items()}
status_values = [v for v, _ in sorted(svalue_tally.items(), key=lambda kv: -kv[1])]
return {"generated": int(time.time()), "tabs": tabs,
"status_field": status_field, "status_colors": status_colors,
"status_values": status_values}
def main():
snap = build()
os.makedirs("data", exist_ok=True)
json.dump(snap, open("data/snapshot.json", "w"))
print(f"snapshot: {len(snap['tabs'])} tabs · "
f"{sum(len(t['rows']) for t in snap['tabs'])} rows · "
f"{sum(len(t['green']) for t in snap['tabs'])} green cells")
if __name__ == "__main__":
main()