← back to Rentv Sheet Enrich
Live console: email cells are now clickable mailto links; color-coded Status field (colors pulled from the sheet) + click-to-set status picker that writes the value to the sheet
dfed233d681f82e5beb5be8e5c5bdea5ba0918d5 · 2026-08-13 16:41:20 -0700 · Steve Abrams
- snapshot.py: extract Status(color) value->color map + canonical value list from cell fills
- live_server.py meta(): expose status_field/status_colors/status_values
- UI: colored status chips + picker popover, email mailcell
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M live_server.pyM public_live/index.htmlM snapshot.py
Diff
commit dfed233d681f82e5beb5be8e5c5bdea5ba0918d5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Thu Aug 13 16:41:20 2026 -0700
Live console: email cells are now clickable mailto links; color-coded Status field (colors pulled from the sheet) + click-to-set status picker that writes the value to the sheet
- snapshot.py: extract Status(color) value->color map + canonical value list from cell fills
- live_server.py meta(): expose status_field/status_colors/status_values
- UI: colored status chips + picker popover, email mailcell
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
live_server.py | 3 +++
public_live/index.html | 64 ++++++++++++++++++++++++++++++++++++++++++++++----
snapshot.py | 48 +++++++++++++++++++++++++++++++++++--
3 files changed, 108 insertions(+), 7 deletions(-)
diff --git a/live_server.py b/live_server.py
index 5d19869..7872d0e 100644
--- a/live_server.py
+++ b/live_server.py
@@ -75,6 +75,9 @@ def refresh_snap():
def meta():
return {"generated": SNAP["generated"],
+ "status_field": SNAP.get("status_field"),
+ "status_colors": SNAP.get("status_colors", {}),
+ "status_values": SNAP.get("status_values", []),
"tabs": [{"gid": t["gid"], "title": t["title"], "rows": len(t["rows"])} for t in SNAP["tabs"]]}
def tab(gid):
diff --git a/public_live/index.html b/public_live/index.html
index 32c8e78..de0b794 100644
--- a/public_live/index.html
+++ b/public_live/index.html
@@ -96,13 +96,23 @@
a.callbtn{color:#16a34a} a.iconbtn.web{color:var(--accent)} a.iconbtn.li{color:#0a66c2}
.linkval{color:inherit}
/* whole-cell clickable links: icon on the LEFT (never clipped), entire cell opens popup / dials */
- td.linkcell,td.callcell{cursor:pointer}
- td.linkcell .cicon,td.callcell .cicon{display:inline-flex;vertical-align:middle;margin-right:5px}
- td.linkcell .cicon svg,td.callcell .cicon svg{width:1.05em;height:1.05em;display:block}
+ td.linkcell,td.callcell,td.mailcell{cursor:pointer}
+ td.linkcell .cicon,td.callcell .cicon,td.mailcell .cicon{display:inline-flex;vertical-align:middle;margin-right:5px}
+ td.linkcell .cicon svg,td.callcell .cicon svg,td.mailcell .cicon svg{width:1.05em;height:1.05em;display:block}
td.linkcell.web .cicon,td.linkcell.web .linkval{color:var(--accent)}
td.linkcell.li .cicon,td.linkcell.li .linkval{color:var(--linkval-li)}
td.callcell .cicon{color:#16a34a}
- td.linkcell:hover,td.callcell:hover{background:var(--linkhover)}
+ td.mailcell .cicon,td.mailcell .linkval{color:var(--accent)}
+ td.linkcell:hover,td.callcell:hover,td.mailcell:hover{background:var(--linkhover)}
+ /* color-coded Status field + click-to-set picker */
+ td.statuscell{cursor:pointer} td.statuscell:hover{background:var(--rowhover)}
+ .statuschip{display:inline-block;max-width:100%;padding:2px 10px;border-radius:12px;font-size:.92em;font-weight:600;line-height:1.55;overflow:hidden;text-overflow:ellipsis;white-space:nowrap}
+ td.statuscell.empty{color:var(--dim);font-size:.9em}
+ .statusmenu{position:fixed;z-index:70;background:var(--panel);border:1px solid var(--line);border-radius:10px;box-shadow:0 12px 34px #000a;padding:5px;min-width:230px;max-height:70vh;overflow:auto}
+ .statusmenu .smhead{color:var(--dim);font-size:10px;text-transform:uppercase;letter-spacing:.5px;padding:5px 8px 6px}
+ .smi{display:flex;align-items:center;gap:9px;padding:7px 8px;border-radius:7px;cursor:pointer;font-size:13px}
+ .smi:hover{background:var(--rowhover)} .smi.cur{font-weight:700}
+ .smi .sw{width:16px;height:16px;border-radius:4px;flex:0 0 16px;border:1px solid rgba(0,0,0,.15)} .smi .sl{flex:1} .smi .smck{color:var(--accent)}
td.linkcell:hover .linkval,td.callcell:hover .linkval{text-decoration:underline}
/* FIELD LOOKUP: click a field in the left rail -> its full value list opens here in the main area */
.lkhead{display:flex;align-items:center;gap:10px;padding:8px 10px;position:sticky;top:0;background:var(--panel2);border-bottom:1px solid var(--line);z-index:6}
@@ -194,6 +204,9 @@
const $=s=>document.querySelector(s), $$=s=>document.querySelectorAll(s);
const LS={get:(k,d)=>{try{return JSON.parse(localStorage.getItem('rc2.'+k))??d}catch(e){return d}},set:(k,v)=>localStorage.setItem('rc2.'+k,JSON.stringify(v))};
let DATA={gid:0,title:'',headers:[],rows:[],green:[]}, GREENSET=new Set(), ROLE={}, META=null;
+// color-coded Status field pulled from the spreadsheet (value->color) + the canonical value list
+let STATUS_FIELD=null, STATUS_COLORS={}, STATUS_VALUES=[];
+const STATUS_DEFAULTS={'Active / Contacted':'#cfe2f3','Needs Email / Outreach Pending':'#fff2a8','Prospect / Follow-up':'#d9ead3','In Progress / Left Message':'#fce5cd','On Hold / Inactive':'#e6e6e6','Flag / Priority':'#f4cccc','Section / Category':'#c9daf8'};
let VIEW=LS.get('view','table'), GID=LS.get('gid',null), editing=false, curRow=null, curChan='email';
let SELECTED=new Set(LS.get('sheets',[])); // selected sheet gids (1..all) shown combined
function curKey(){ return SELECTED.size===1 ? String([...SELECTED][0]) : ('combo.'+[...SELECTED].sort().join('-')); }
@@ -220,6 +233,7 @@ function hrefOf(v){ v=String(v||'').trim();
// ---- load (from OUR local copy) ---------------------------------------------
async function loadMeta(){
META=await (await fetch('/api/meta')).json();
+ STATUS_FIELD=META.status_field||null; STATUS_COLORS=META.status_colors||{}; STATUS_VALUES=META.status_values||[];
const sel=$('#tab');sel.innerHTML='';
META.tabs.forEach(t=>{const o=document.createElement('option');o.value=t.gid;o.textContent=`${t.title} (${t.rows})`;sel.appendChild(o)});
if(GID==null||!META.tabs.find(t=>t.gid==GID)){
@@ -496,15 +510,51 @@ function iconLink(url,kind){
return `<a class="iconbtn ${kind}" href="${esc(url)}" target="_blank" rel="noopener" contenteditable="false" title="${label}">${svg}</a>`;
}
// digits of a cell whose WHOLE value is one phone number (anchored, so a phone inside Notes prose is ignored)
+// a cell whose WHOLE value is one email address -> mailto:
+function emailOf(v){ v=String(v==null?'':v).trim(); return /^[^\s@]+@[^\s@]+\.[^\s@]{2,}$/.test(v)?v:null; }
function telDigits(v){ v=String(v==null?'':v).trim();
if(!/^(\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}(\s*(x|ext\.?)\s*\d+)?$/i.test(v))return null;
const core=v.match(/(\+?1[-.\s]?)?\(?\d{3}\)?[-.\s]?\d{3}[-.\s]?\d{4}/)[0];
let d=core.replace(/\D/g,''); if(d.length===10)d='1'+d; return d; }
// build one <td>. Website/LinkedIn/phone => whole cell is CLICKABLE (icon on the LEFT so it's never clipped,
// entire cell opens a popup / dials). Everything else stays contenteditable + expand-on-click.
+// ---- color-coded Status field (colors pulled from the sheet) ----
+function hashColor(s){ let h=0; for(let i=0;i<s.length;i++)h=(h*31+s.charCodeAt(i))>>>0; return `hsl(${h%360} 68% 84%)`; }
+function statusColor(v){ return (STATUS_COLORS&&STATUS_COLORS[v]) || STATUS_DEFAULTS[v] || hashColor(v); }
+function txtOn(color){ const m=color.match(/^#([0-9a-f]{6})$/i); if(!m)return '#111'; // hsl fallbacks are light -> dark text
+ const n=parseInt(m[1],16), r=n>>16, g=(n>>8)&255, b=n&255; return (0.299*r+0.587*g+0.114*b)>150?'#111':'#fff'; }
+function isStatusCol(ci){ return STATUS_FIELD && DATA.headers[ci]===STATUS_FIELD; }
+function openStatusPicker(td){
+ closeStatusPicker();
+ const r=+td.dataset.r, c=+td.dataset.c, cur=(DATA.rows[r]&&DATA.rows[r][c])||'';
+ const vals=(STATUS_VALUES&&STATUS_VALUES.length)?STATUS_VALUES:Object.keys(STATUS_DEFAULTS);
+ const m=document.createElement('div'); m.className='statusmenu'; m.id='statusmenu';
+ m.innerHTML=`<div class="smhead">Set status — writes to the sheet</div>`+vals.map(v=>{const col=statusColor(v);
+ return `<div class="smi${v===cur?' cur':''}" data-v="${esc(v)}"><span class="sw" style="background:${col}"></span><span class="sl">${esc(v)}</span>${v===cur?'<span class="smck">✓</span>':''}</div>`;}).join('')
+ +`<div class="smi smclear" data-v=""><span class="sw" style="background:transparent;border:1px dashed var(--line)"></span><span class="sl">Clear</span></div>`;
+ document.body.appendChild(m);
+ const b=td.getBoundingClientRect(), mw=250;
+ m.style.left=Math.max(6,Math.min(b.left,window.innerWidth-mw-6))+'px';
+ m.style.top=Math.min(b.bottom+4, window.innerHeight-m.offsetHeight-10)+'px';
+ m.querySelectorAll('.smi').forEach(el=>el.onclick=async()=>{ const v=el.dataset.v; closeStatusPicker();
+ try{ const res=await postWrite(r,c,v); if(!res.ok)throw new Error(res.error||'write failed');
+ DATA.rows[r][c]=v; if(v)GREENSET.add(r+','+c); render(); setSync('ok'); }
+ catch(err){ setSync('err'); alert('Status write failed: '+err.message); } });
+ setTimeout(()=>document.addEventListener('mousedown',closeStatusPickerOutside),0);
+}
+function closeStatusPicker(){const m=$('#statusmenu');if(m)m.remove();document.removeEventListener('mousedown',closeStatusPickerOutside);}
+function closeStatusPickerOutside(e){ if(!e.target.closest('#statusmenu')&&!e.target.closest('.statuscell'))closeStatusPicker(); }
function cellTD(ri,ci,val,isN,gr){
const cls=[isN?'namecell':'',gr?'enriched':''].filter(Boolean).join(' ');
- const s=String(val==null?'':val), u=hrefOf(s);
+ const s=String(val==null?'':val);
+ if(isStatusCol(ci)){ // color-coded status chip + click-to-set picker
+ if(!s) return `<td data-r="${ri}" data-c="${ci}" contenteditable="false" class="statuscell empty ${cls}" title="Click to set status">＋ set status</td>`;
+ const col=statusColor(s);
+ return `<td data-r="${ri}" data-c="${ci}" contenteditable="false" class="statuscell ${cls}" title="Click to change status"><span class="statuschip" style="background:${col};color:${txtOn(col)}">${esc(s)}</span></td>`;
+ }
+ const em=emailOf(s);
+ if(em){ return `<td data-r="${ri}" data-c="${ci}" contenteditable="false" data-mail="${esc(em)}" title="Email ${esc(em)}" class="mailcell ${cls}"><span class="cicon">${ICON.mail}</span><span class="linkval">${esc(em)}</span></td>`; }
+ const u=hrefOf(s);
if(u){ const li=/linkedin\.com/i.test(u), disp=s.replace(/^https?:\/\/(www\.)?/i,'').replace(/\/+$/,'');
return `<td data-r="${ri}" data-c="${ci}" contenteditable="false" data-href="${esc(u)}" title="Open ${esc(u)}" class="linkcell ${li?'li':'web'} ${cls}"><span class="cicon">${li?ICON.li:ICON.web}</span><span class="linkval">${esc(disp)}</span></td>`; }
const d=telDigits(s);
@@ -533,10 +583,14 @@ function wireGrid(){
const gridEl=$('#grid');
if(gridEl){
gridEl.addEventListener('click',e=>{
+ const st=e.target.closest('td.statuscell'); // Status -> color-coded picker (writes to sheet)
+ if(st){ e.preventDefault(); openStatusPicker(st); return; }
const lc=e.target.closest('td.linkcell'); // Website / LinkedIn -> popup window
if(lc){ e.preventDefault(); window.open(lc.dataset.href,'linkpop','popup=yes,noopener,width=1100,height=850,left=140,top=90'); return; }
const cc=e.target.closest('td.callcell'); // phone -> dial
if(cc){ location.href='tel:'+cc.dataset.tel; return; }
+ const mc=e.target.closest('td.mailcell'); // email -> mail client
+ if(mc){ location.href='mailto:'+mc.dataset.mail; return; }
});
}
$$('.card').forEach(c=>c.onclick=()=>openDraft(+c.dataset.r));
diff --git a/snapshot.py b/snapshot.py
index 09dcc86..d6675e1 100644
--- a/snapshot.py
+++ b/snapshot.py
@@ -36,6 +36,14 @@ def build(tok=None):
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
+ 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", [])
@@ -49,7 +57,22 @@ def build(tok=None):
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:
@@ -59,15 +82,36 @@ def build(tok=None):
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 = []
for ri, row in enumerate(green_rows.get(gid, [])):
if ri <= hr: # header + any title rows above it
continue
for ci, cell in enumerate(row.get("values", []) or []):
- if is_green((cell.get("effectiveFormat") or {}).get("backgroundColor")):
+ bg = (cell.get("effectiveFormat") or {}).get("backgroundColor")
+ if is_green(bg):
green.append(f"{ri-hr-1},{ci}")
+ if sidx is not None and ci == sidx: # learn the value->color coding from the sheet
+ hx = hexc(bg)
+ di = ri - hr - 1
+ 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})
- return {"generated": int(time.time()), "tabs": tabs}
+ 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()
← 00fc8a6 Live console: Width presets (Tight/Cozy/Wide/Fit + auto-refi
·
back to Rentv Sheet Enrich
·
rentv :9797 draft composer: richer Follow-up (thank-you + qu 1098075 →