Spreadsheet
muse spark 1.2 · basic

Reviews & generation details
Reviews
No human review yet. Unreviewed does not mean unsuccessful.
Generation context
Imported historical artifact. Its exact original instructions and lineage are not known.
Generation prompt · exact text unavailable
See the current prompt for reference. It may differ from the instructions used to generate this artifact.
Inspect original source 39,631 bytes · SHA-256 dbbe563c7ca1
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Sheet — Spreadsheet</title>
<style>
:root{
--bg:#f8f9fa;
--header-bg:#f8f9fa;
--header-text:#5f6368;
--grid-line:#dadce0;
--grid-line-strong:#c4c7cc;
--selection:#1a73e8;
--selection-bg:#e8f0fe;
--error:#d93025;
--cell-bg:#ffffff;
--toolbar-bg:#ffffff;
--formula-bg:#ffffff;
}
*{box-sizing:border-box}
html,body{height:100%;margin:0;font-family:Roboto,Arial,sans-serif;font-size:13px;color:#202124;background:var(--bg)}
button{font-family:inherit}
#app{display:flex;flex-direction:column;height:100vh;overflow:hidden}
/* Toolbar */
#toolbar{display:flex;align-items:center;gap:6px;padding:8px 12px;background:var(--toolbar-bg);border-bottom:1px solid var(--grid-line);flex-wrap:wrap}
#toolbar .title{font-size:18px;font-weight:400;color:#202124;margin-right:12px;display:flex;align-items:center;gap:8px}
#toolbar .title span{color:#34a853;font-weight:500}
#toolbar button{border:1px solid var(--grid-line);background:#fff;border-radius:4px;padding:6px 10px;cursor:pointer;font-size:12px;color:#3c4043}
#toolbar button:hover{background:#f1f3f4;border-color:#dadce0}
#toolbar button:active{background:#e8eaed}
#toolbar .sep{width:1px;height:22px;background:var(--grid-line);margin:0 4px}
#toolbar .hint{margin-left:auto;font-size:11px;color:#5f6368}
/* Formula bar */
#formulaBarWrap{display:flex;align-items:center;gap:8px;padding:6px 8px;background:var(--formula-bg);border-bottom:1px solid var(--grid-line)}
#cellRefBox{min-width:72px;height:28px;display:flex;align-items:center;justify-content:center;border:1px solid var(--grid-line);border-radius:4px;background:#fff;font-size:12px;color:#5f6368;padding:0 8px}
#formulaBarWrap .fx{color:#5f6368;font-style:italic;font-size:13px;padding:0 4px;border-right:1px solid var(--grid-line);margin-right:4px}
#formulaInput{flex:1;height:28px;border:1px solid var(--grid-line);border-radius:4px;padding:4px 8px;font-size:13px;outline:none}
#formulaInput:focus{border-color:var(--selection);box-shadow:0 0 0 1px var(--selection)}
/* Grid container */
#gridContainer{flex:1;overflow:auto;position:relative;background:var(--cell-bg)}
table#grid{border-collapse:separate;border-spacing:0;table-layout:fixed}
#grid th, #grid td{border-right:1px solid var(--grid-line);border-bottom:1px solid var(--grid-line);font-weight:400;white-space:nowrap;overflow:hidden}
#grid th{position:sticky;background:var(--header-bg);color:var(--header-text);font-size:11px;text-align:center;user-select:none;z-index:2}
#grid th.corner{left:0;top:0;z-index:5;width:48px;min-width:48px;background:#f1f3f4;border-right:1px solid var(--grid-line-strong);border-bottom:1px solid var(--grid-line-strong)}
#grid th.colHeader{top:0;z-index:3;height:22px;min-width:80px;width:80px;cursor:pointer}
#grid th.colHeader:hover{background:#e8eaed}
#grid th.rowHeader{left:0;z-index:2;width:48px;min-width:48px;text-align:center;background:var(--header-bg);cursor:pointer;position:sticky}
#grid th.rowHeader:hover{background:#e8eaed}
#grid td{height:22px;min-width:80px;width:80px;padding:0;position:relative;background:var(--cell-bg);vertical-align:middle}
#grid td .cellInner{width:100%;height:100%;padding:2px 4px;overflow:hidden;text-overflow:ellipsis;white-space:nowrap;display:flex;align-items:center}
#grid td.selected{outline:2px solid var(--selection);outline-offset:-1px;z-index:1}
#grid td.activeRange{background:var(--selection-bg)}
#grid td.editing{outline:2px solid var(--selection)}
#grid td.editing .cellInner{display:none}
.cellEditor{position:absolute;inset:0;width:100%;height:100%;border:2px solid var(--selection);padding:2px 4px;font-size:13px;outline:none;box-shadow:0 2px 6px rgba(0,0,0,.15);z-index:4;background:#fff}
.num{justify-content:flex-end;text-align:right}
.text{justify-content:flex-start;text-align:left}
.err{color:var(--error);justify-content:flex-start;font-style:italic}
#contextMenu{position:fixed;display:none;background:#fff;border:1px solid #dadce0;border-radius:4px;box-shadow:0 2px 10px rgba(0,0,0,.2);z-index:100;min-width:190px;padding:4px 0}
#contextMenu div{padding:8px 14px;font-size:13px;cursor:pointer;color:#202124}
#contextMenu div:hover{background:#f1f3f4}
#status{padding:4px 12px;font-size:11px;color:#5f6368;border-top:1px solid var(--grid-line);background:#fff;display:flex;gap:16px}
</style>
</head>
<body>
<div id="app">
<div id="toolbar">
<div class="title"><span>▦</span> Sheet</div>
<button id="btnInsertRow">Insert row</button>
<button id="btnDeleteRow">Delete row</button>
<div class="sep"></div>
<button id="btnInsertCol">Insert column</button>
<button id="btnDeleteCol">Delete column</button>
<div class="sep"></div>
<button id="btnClear">Clear cell</button>
<button id="btnClearAll">Clear all</button>
<div class="hint">Double-click to edit • Delete to clear • Right-click headers for insert/delete</div>
</div>
<div id="formulaBarWrap">
<div id="cellRefBox">A1</div>
<span class="fx">fx</span>
<input id="formulaInput" type="text" placeholder="Enter value or formula (=A1*2, =SUM(A1:A5))" spellcheck="false">
</div>
<div id="gridContainer">
<table id="grid"></table>
</div>
<div id="status"><span id="statusCell">A1</span><span id="statusInfo">Ready</span><span style="margin-left:auto">26×100 • vanilla JS</span></div>
</div>
<div id="contextMenu"></div>
<script>
/* ============================================================
TUNABLE CONSTANTS
============================================================ */
const CONFIG = {
ROWS: 100,
COLS: 26,
CELL_WIDTH: 80,
CELL_HEIGHT: 22,
STORAGE_KEY: 'spreadsheet_v1_raw',
COLORS: { selection:'#1a73e8', error:'#d93025', grid:'#dadce0' }
};
/* ============================================================
STATE
============================================================ */
let ROWS = CONFIG.ROWS;
let COLS = CONFIG.COLS;
let cells = []; // 2D [row][col] = {raw:''}
let computed = []; // 2D cache {value, error, display}
let selRow = 0, selCol = 0;
let isEditing = false;
let depMap = new Map(); // cellId -> Set(deps)
let revMap = new Map(); // cellId -> Set(dependents)
const gridEl = document.getElementById('grid');
const containerEl = document.getElementById('gridContainer');
const formulaInput = document.getElementById('formulaInput');
const cellRefBox = document.getElementById('cellRefBox');
const statusCell = document.getElementById('statusCell');
const statusInfo = document.getElementById('statusInfo');
const contextMenu = document.getElementById('contextMenu');
/* ============================================================
UTIL: Column / Row conversions
============================================================ */
function colToIndex(colStr){
let n=0;
for(let i=0;i<colStr.length;i++) n = n*26 + (colStr.charCodeAt(i)-64);
return n-1;
}
function indexToCol(idx){
let s=''; idx++;
while(idx>0){ let r=(idx-1)%26; s=String.fromCharCode(65+r)+s; idx=Math.floor((idx-1)/26); }
return s;
}
function cellIdFromRC(r,c){ return indexToCol(c)+(r+1); }
function parseCellId(id){
const m=id.match(/^([A-Z]+)(\d+)$/);
if(!m) return null;
return {col:colToIndex(m[1]), row:parseInt(m[2],10)-1, colStr:m[1]};
}
function isValidRC(r,c){ return r>=0 && c>=0 && r<ROWS && c<COLS; }
// for rewriting we allow any positive row/col before bounds check
function getRawById(id){
const p=parseCellId(id); if(!p) return '';
if(p.row<0||p.col<0||p.row>=ROWS||p.col>=COLS) return '';
return cells[p.row][p.col].raw;
}
/* ============================================================
TOKENIZER & PARSER
============================================================ */
function tokenize(expr){
const tokens=[]; let i=0;
while(i<expr.length){
const ch=expr[i];
if(/\s/.test(ch)){ i++; continue; }
if(ch==='('||ch===')'||ch===','||ch===':' ){ tokens.push({t:ch}); i++; continue; }
if(ch==='+'||ch==='*'||ch==='/'){ tokens.push({t:ch}); i++; continue; }
if(ch==='-'){ tokens.push({t:'-'}); i++; continue; }
if(/[0-9.]/.test(ch)){
let num=''; let dot=0;
while(i<expr.length && /[0-9.]/.test(expr[i])){ if(expr[i]==='.') dot++; num+=expr[i++]; }
if(dot>1 || num==='.' ) throw new Error('parse');
tokens.push({t:'NUM', v:parseFloat(num)}); continue;
}
if(/[A-Za-z#]/.test(ch)){
// handle #REF! as token
if(expr.slice(i,i+5)==='#REF!'){ tokens.push({t:'REFERR'}); i+=5; continue; }
if(/[A-Za-z]/.test(ch)){
let letters=''; while(i<expr.length && /[A-Za-z]/.test(expr[i])) letters+=expr[i++].toUpperCase();
// check if followed by digits -> cell ref
let digits='';
let j=i;
while(j<expr.length && /[0-9]/.test(expr[j])) digits+=expr[j++];
if(digits.length>0){
tokens.push({t:'CELL', v:letters+digits});
i=j; continue;
} else {
tokens.push({t:'IDENT', v:letters});
continue;
}
}
}
throw new Error('parse');
}
tokens.push({t:'EOF'});
return tokens;
}
class Parser{
constructor(tokens){ this.t=tokens; this.p=0; }
peek(){ return this.t[this.p]; }
consume(e){ const cur=this.peek(); if(e && cur.t!==e) throw new Error('parse'); this.p++; return cur; }
parse(){ const n=this.parseExpr(); if(this.peek().t!=='EOF') throw new Error('parse'); return n; }
parseExpr(){
let n=this.parseTerm();
while(this.peek().t==='+' || this.peek().t==='-'){
const op=this.consume().t; const r=this.parseTerm(); n={type:'binary', op, left:n, right:r};
}
return n;
}
parseTerm(){
let n=this.parseFactor();
while(this.peek().t==='*' || this.peek().t==='/'){
const op=this.consume().t; const r=this.parseFactor(); n={type:'binary', op, left:n, right:r};
}
return n;
}
parseFactor(){
if(this.peek().t==='-'){
this.consume('-'); const e=this.parseFactor(); return {type:'unary', op:'-', expr:e};
}
return this.parsePrimary();
}
parsePrimary(){
const cur=this.peek();
if(cur.t==='NUM'){ this.consume(); return {type:'number', value:cur.v}; }
if(cur.t==='REFERR'){ this.consume(); return {type:'referr'}; }
if(cur.t==='CELL'){
const cellTok=this.consume();
if(this.peek().t===':'){
this.consume(':');
const nxt=this.peek();
if(nxt.t!=='CELL') throw new Error('parse');
const end=this.consume();
return {type:'range', start:cellTok.v, end:end.v};
}
return {type:'cell', ref:cellTok.v};
}
if(cur.t==='IDENT'){
const name=cur.v; this.consume();
if(this.peek().t==='('){
this.consume('(');
const args=[];
if(this.peek().t!==')'){
while(true){
// allow range or expr
// need to look ahead for range: CELL ':' CELL
if(this.peek().t==='CELL' && this.t[this.p+1] && this.t[this.p+1].t===':'){
const start=this.consume().v; this.consume(':'); const end=this.consume().v;
args.push({type:'range', start, end});
} else {
args.push(this.parseExpr());
}
if(this.peek().t===','){ this.consume(','); } else break;
}
}
if(this.peek().t!==')') throw new Error('parse');
this.consume(')');
return {type:'func', name, args};
} else {
throw new Error('parse');
}
}
if(cur.t==='('){
this.consume('('); const e=this.parseExpr(); if(this.peek().t!==')') throw new Error('parse'); this.consume(')'); return e;
}
throw new Error('parse');
}
}
function parseFormula(str){
// str includes = at start; we parse after =
const expr=str.slice(1);
if(expr.trim()==='') throw new Error('parse');
const toks=tokenize(expr);
const p=new Parser(toks);
return p.parse();
}
/* ============================================================
EVALUATION HELPERS
============================================================ */
function collectDeps(ast, outSet){
if(!ast) return;
if(ast.type==='cell') outSet.add(ast.ref);
else if(ast.type==='range'){
const s=parseCellId(ast.start), e=parseCellId(ast.end);
if(!s||!e){ // will be REF
outSet.add(ast.start); outSet.add(ast.end);
return;
}
const r1=Math.min(s.row,e.row), r2=Math.max(s.row,e.row);
const c1=Math.min(s.col,e.col), c2=Math.max(s.col,e.col);
for(let r=r1;r<=r2;r++) for(let c=c1;c<=c2;c++) outSet.add(cellIdFromRC(r,c));
} else if(ast.type==='binary'){ collectDeps(ast.left,outSet); collectDeps(ast.right,outSet); }
else if(ast.type==='unary'){ collectDeps(ast.expr,outSet); }
else if(ast.type==='func'){ ast.args.forEach(a=>collectDeps(a,outSet)); }
}
function getDepsForRaw(raw){
if(!raw || raw[0]!=='=') return new Set();
if(raw.includes('#REF!')) return new Set(['#REF!']); // special dependency for error
try{
const ast=parseFormula(raw);
const s=new Set(); collectDeps(ast,s);
// filter invalid ids? keep but will be REF
return s;
}catch(e){ return new Set(); }
}
// evaluation returns {value, error}
function evalAST(node){
if(node.type==='number') return {value:node.value, error:null};
if(node.type==='referr') return {value:null, error:'#REF!'};
if(node.type==='cell'){
const p=parseCellId(node.ref);
if(!p) return {value:null, error:'#REF!'};
if(p.row<0||p.col<0||p.row>=ROWS||p.col>=COLS) return {value:null, error:'#REF!'};
const cid=cellIdFromRC(p.row,p.col);
const cellCache = cacheMap.get(cid);
if(!cellCache) return {value:0, error:null}; // empty
if(cellCache.error) return {value:null, error:cellCache.error};
const v=cellCache.value;
if(v===null || v==='') return {value:0, error:null}; // empty treated as 0 for arithmetic? But for display empty is blank.
// if value is number -> return number, if string try numeric
if(typeof v==='number') return {value:v, error:null};
if(typeof v==='string'){
if(v==='') return {value:0, error:null};
const n=Number(v);
if(!isNaN(n) && v.trim()!=='') return {value:n, error:null};
// text in arithmetic => error? We'll return ERR to distinguish from functions
return {value:null, error:'#ERR!'};
}
return {value:v, error:null};
}
if(node.type==='range'){
// ranges should only appear inside funcs; if standalone, return REF? treat as error
return {value:null, error:'#REF!'};
}
if(node.type==='unary'){
const ev=evalAST(node.expr);
if(ev.error) return ev;
return {value: -ev.value, error:null};
}
if(node.type==='binary'){
const l=evalAST(node.left);
if(l.error) return l;
const r=evalAST(node.right);
if(r.error) return r;
const lv=l.value, rv=r.value;
if(typeof lv!=='number' || typeof rv!=='number'){
// try coercion if string numeric already handled, but if still not number => error
return {value:null, error:'#ERR!'};
}
if(node.op==='+') return {value:lv+rv, error:null};
if(node.op==='-') return {value:lv-rv, error:null};
if(node.op==='*') return {value:lv*rv, error:null};
if(node.op==='/'){
if(rv===0) return {value:null, error:'#DIV/0!'};
return {value:lv/rv, error:null};
}
}
if(node.type==='func'){
const name=node.name.toUpperCase();
// expand args
let allValues=[]; // numbers
let hasNum=false;
for(const arg of node.args){
if(arg.type==='range'){
const s=parseCellId(arg.start), e=parseCellId(arg.end);
if(!s||!e) return {value:null, error:'#REF!'};
if(s.row>=ROWS||e.row>=ROWS||s.col>=COLS||e.col>=COLS) return {value:null, error:'#REF!'};
const r1=Math.min(s.row,e.row), r2=Math.max(s.row,e.row);
const c1=Math.min(s.col,e.col), c2=Math.max(s.col,e.col);
for(let r=r1;r<=r2;r++) for(let c=c1;c<=c2;c++){
const cid=cellIdFromRC(r,c);
const cc=cacheMap.get(cid);
if(cc && cc.error) return {value:null, error:cc.error};
const v=cc? cc.value : null;
if(typeof v==='number') { allValues.push(v); hasNum=true; }
else if(typeof v==='string' && v!=='' && !isNaN(Number(v))) { allValues.push(Number(v)); hasNum=true; }
else {
// for SUM/AVG ignore text, for COUNT ignore as well
// push nothing
}
}
} else {
const ev=evalAST(arg);
if(ev.error){
// For functions, propagate error except maybe ignore text? But if arg evaluation gave #ERR! for text, treat as ignore for SUM?
// We already propagate DIV0, REF, CYCLE. For ERR from text, should we ignore? Let's treat: if error is #ERR! from text, ignore instead of propagate for SUM etc? To keep SUM(A1) where A1=text should not error.
// So we need to distinguish: eval of cell with text returns #ERR! ; for functions we should ignore text not error.
// We'll handle: if error is #ERR! and arg is cell ref directly, ignore.
// But if arg is complex expression that resulted in ERR, propagate.
// Simple: if ev.error==='#ERR!' and (arg.type==='cell' || arg.type==='range') then ignore (skip)
if(ev.error==='#ERR!'){
// check if arg is cell: ignore
if(arg.type==='cell') continue;
// expression text error propagate? ignore for sum? We'll ignore
continue;
}
return ev;
}
// ev is number or 0 etc.
if(typeof ev.value==='number') { allValues.push(ev.value); hasNum=true; }
else if(ev.value===null || ev.value===0){ // empty -> 0 for SUM? but COUNT should not count empty? We'll handle per func
// For SUM, empty as 0 should not affect sum (0). We'll push 0? But sum of empty should be 0.
// For COUNT, empty not counted.
// We'll push for SUM but not COUNT. Need to know func.
if(name==='SUM' || name==='AVG' || name==='AVERAGE') { /* ignore empty for avg? */ if(ev.value===0 && arg.type==='cell') { /* for AVG empty ignored */ } else allValues.push(ev.value); }
else allValues.push(ev.value);
}
}
}
if(name==='SUM'){
let sum=0; allValues.forEach(v=>sum+=v); return {value:sum, error:null};
}
if(name==='AVG' || name==='AVERAGE'){
if(allValues.length===0) return {value:null, error:'#DIV/0!'};
let sum=0; allValues.forEach(v=>sum+=v); return {value:sum/allValues.length, error:null};
}
if(name==='MIN'){
if(allValues.length===0) return {value:0, error:null};
return {value:Math.min(...allValues), error:null};
}
if(name==='MAX'){
if(allValues.length===0) return {value:0, error:null};
return {value:Math.max(...allValues), error:null};
}
if(name==='COUNT'){
// count numeric values (already filtered)
return {value:allValues.length, error:null};
}
return {value:null, error:'#ERR!'};
}
return {value:null, error:'#ERR!'};
}
/* ============================================================
DEPENDENCY GRAPH & RECALC
============================================================ */
let cacheMap = new Map(); // cellId -> {value, error}
function rebuildGraph(){
depMap.clear(); revMap.clear();
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++){
const id=cellIdFromRC(r,c);
const raw=cells[r][c].raw;
const deps=getDepsForRaw(raw);
depMap.set(id, deps);
// ensure rev entry
if(!revMap.has(id)) revMap.set(id, new Set());
for(const d of deps){
if(d==='#REF!') continue;
if(!revMap.has(d)) revMap.set(d, new Set());
revMap.get(d).add(id);
}
}
}
function updateGraphForCell(id, raw){
const oldDeps=depMap.get(id)||new Set();
for(const d of oldDeps){
if(revMap.has(d)) revMap.get(d).delete(id);
}
const newDeps=getDepsForRaw(raw);
depMap.set(id, newDeps);
for(const d of newDeps){
if(d==='#REF!') continue;
if(!revMap.has(d)) revMap.set(d,new Set());
revMap.get(d).add(id);
}
}
function evaluateCellRaw(raw){
if(raw===''||raw===null) return {value:'', error:null, display:''};
if(raw[0]!=='='){
// plain value: try number
const trimmed=raw.trim();
if(trimmed!=='' && !isNaN(Number(trimmed)) && /^[-+]?(\d+(\.\d*)?|\.\d+)$/.test(trimmed)){
const n=Number(trimmed);
return {value:n, error:null, display:String(n)};
}
return {value:raw, error:null, display:raw};
}
// formula
if(raw.includes('#REF!')) return {value:null, error:'#REF!', display:'#REF!'};
try{
const ast=parseFormula(raw);
const res=evalAST(ast);
if(res.error) return {value:null, error:res.error, display:res.error};
// format number display
let v=res.value;
if(typeof v==='number'){
// avoid -0
if(Object.is(v,-0)) v=0;
// pretty: if integer show integer, else limit 10 decimals strip trailing zeros
let disp = Number.isInteger(v) ? String(v) : String(parseFloat(v.toFixed(10)));
return {value:v, error:null, display:disp};
}
return {value:v, error:null, display:String(v)};
}catch(e){
return {value:null, error:'#ERR!', display:'#ERR!'};
}
}
function recomputeAll(){
// topo sort all
cacheMap.clear();
// Build graph already done
// Collect all ids
const allIds=[];
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++) allIds.push(cellIdFromRC(r,c));
// Kahn
const indeg=new Map();
allIds.forEach(id=>{
const deps=depMap.get(id)||new Set();
let cnt=0;
for(const d of deps){
if(d==='#REF!') continue;
// only count deps that are actual cells in sheet (ignore out of bounds refs already filtered)
// if dep not in sheet (e.g., Z1000 beyond rows) then it's REF already handled
if(allIds.includes(d)) cnt++;
else {
// if dep not in sheet but is valid cell outside range -> treat as REF? Already would have been considered but we still count? Instead will be REF error at eval.
}
}
indeg.set(id,cnt);
});
const q=[];
allIds.forEach(id=>{ if(indeg.get(id)===0) q.push(id); });
const sorted=[];
while(q.length){
const cur=q.shift(); sorted.push(cur);
const dependents=revMap.get(cur)||new Set();
for(const dep of dependents){
if(!indeg.has(dep)) continue;
indeg.set(dep, indeg.get(dep)-1);
if(indeg.get(dep)===0) q.push(dep);
}
}
const sortedSet=new Set(sorted);
const cycleIds = allIds.filter(id=>!sortedSet.has(id));
// evaluate sorted
for(const id of sorted){
const p=parseCellId(id);
const raw=cells[p.row][p.col].raw;
const res=evaluateCellRaw(raw);
// if this cell's deps include a cycle cell, should propagate cycle? But topo ensures cycle not in sorted, but dependents of cycle may be in sorted? Actually dependents of cycle will have indeg>0 so not sorted -> also cycle indirectly. Our cycleIds already includes those.
cacheMap.set(id, {value:res.value, error:res.error});
// store display in computed 2d later
}
for(const id of cycleIds){
cacheMap.set(id, {value:null, error:'#CYCLE!'});
}
// build computed 2D for display quick
computed = Array.from({length:ROWS}, ()=>Array(COLS));
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++){
const id=cellIdFromRC(r,c);
const entry=cacheMap.get(id);
let disp='';
if(entry){
if(entry.error) disp=entry.error;
else {
if(entry.value===''||entry.value===null) disp='';
else if(typeof entry.value==='number') disp= Number.isInteger(entry.value)? String(entry.value): String(parseFloat(entry.value.toFixed(10)));
else disp=String(entry.value);
}
}
computed[r][c]={value:entry?entry.value:'', error:entry?entry.error:null, display:disp};
}
// update UI for all cells
refreshAllDisplays();
}
function recomputeFrom(startId){
// BFS affected
const affected=new Set(); const q=[startId];
affected.add(startId);
let idx=0;
while(idx<q.length){
const cur=q[idx++];
const deps=revMap.get(cur)||new Set();
for(const d of deps){
if(!affected.has(d)){ affected.add(d); q.push(d); }
}
}
// topo for affected
const affectedArr=[...affected];
const indeg=new Map();
affectedArr.forEach(id=>{
const deps=depMap.get(id)||new Set();
let cnt=0;
for(const d of deps){
if(d==='#REF!') continue;
if(affected.has(d)) cnt++;
}
indeg.set(id,cnt);
});
const queue=affectedArr.filter(id=>indeg.get(id)===0);
const sorted=[];
let qi=0;
while(qi<queue.length){
const cur=queue[qi++];
sorted.push(cur);
const dependents=revMap.get(cur)||new Set();
for(const nxt of dependents){
if(!affected.has(nxt)) continue;
indeg.set(nxt, indeg.get(nxt)-1);
if(indeg.get(nxt)===0) queue.push(nxt);
}
}
const sortedSet=new Set(sorted);
const cycle=[...affected].filter(id=>!sortedSet.has(id));
// evaluate sorted in order
for(const id of sorted){
const p=parseCellId(id);
if(!p || p.row<0||p.row>=ROWS||p.col<0||p.col>=COLS) continue;
const raw=cells[p.row][p.col].raw;
const res=evaluateCellRaw(raw);
cacheMap.set(id,{value:res.value, error:res.error});
// update computed 2d
computed[p.row][p.col]={value:res.value, error:res.error, display: res.error?res.error:(res.value===''?'': (typeof res.value==='number'? (Number.isInteger(res.value)?String(res.value):String(parseFloat(res.value.toFixed(10)))): String(res.value))) };
updateCellDisplay(p.row,p.col);
}
for(const id of cycle){
const p=parseCellId(id);
cacheMap.set(id,{value:null, error:'#CYCLE!'});
if(p && p.row>=0&&p.row<ROWS&&p.col>=0&&p.col<COLS){
computed[p.row][p.col]={value:null, error:'#CYCLE!', display:'#CYCLE!'};
updateCellDisplay(p.row,p.col);
}
}
}
/* ============================================================
GRID RENDERING
============================================================ */
function createGridStructure(){
gridEl.innerHTML='';
// header row
const thead=document.createElement('thead');
const hr=document.createElement('tr');
const corner=document.createElement('th');
corner.className='corner'; corner.textContent='';
hr.appendChild(corner);
for(let c=0;c<COLS;c++){
const th=document.createElement('th');
th.className='colHeader'; th.textContent=indexToCol(c);
th.dataset.col=c;
th.addEventListener('contextmenu', e=>{ e.preventDefault(); showColMenu(e,c); });
th.addEventListener('click', ()=>selectCell(selRow,c));
hr.appendChild(th);
}
thead.appendChild(hr);
gridEl.appendChild(thead);
const tbody=document.createElement('tbody');
for(let r=0;r<ROWS;r++){
const tr=document.createElement('tr');
const rh=document.createElement('th');
rh.className='rowHeader'; rh.textContent=r+1; rh.dataset.row=r;
rh.addEventListener('contextmenu', e=>{ e.preventDefault(); showRowMenu(e,r); });
rh.addEventListener('click', ()=>selectCell(r,selCol));
tr.appendChild(rh);
for(let c=0;c<COLS;c++){
const td=document.createElement('td');
td.dataset.row=r; td.dataset.col=c;
const inner=document.createElement('div');
inner.className='cellInner';
td.appendChild(inner);
td.addEventListener('click', e=>{ selectCell(r,c); });
td.addEventListener('dblclick', e=>{ selectCell(r,c); enterEditMode(); });
tr.appendChild(td);
}
tbody.appendChild(tr);
}
gridEl.appendChild(tbody);
refreshAllDisplays();
highlightSelection();
}
function refreshAllDisplays(){
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++) updateCellDisplay(r,c);
}
function updateCellDisplay(r,c){
const td = gridEl.querySelector(`td[data-row="${r}"][data-col="${c}"]`);
if(!td) return;
const inner=td.querySelector('.cellInner');
const comp=computed[r] && computed[r][c] ? computed[r][c] : {display:'', error:null, value:''};
inner.textContent=comp.display;
inner.className='cellInner';
if(comp.error) inner.classList.add('err');
else if(typeof comp.value==='number') inner.classList.add('num');
else inner.classList.add('text');
}
function highlightSelection(){
gridEl.querySelectorAll('td').forEach(td=>{
td.classList.remove('selected','activeRange');
});
const td=gridEl.querySelector(`td[data-row="${selRow}"][data-col="${selCol}"]`);
if(td) td.classList.add('selected');
cellRefBox.textContent=cellIdFromRC(selRow,selCol);
statusCell.textContent=cellIdFromRC(selRow,selCol);
const raw=cells[selRow][selCol].raw;
if(document.activeElement!==formulaInput) formulaInput.value=raw;
}
/* ============================================================
SELECTION & EDITING
============================================================ */
function selectCell(r,c){
if(r<0) r=0; if(c<0) c=0; if(r>=ROWS) r=ROWS-1; if(c>=COLS) c=COLS-1;
// commit previous edit if any
if(isEditing) commitEdit();
selRow=r; selCol=c;
highlightSelection();
// scroll into view
const td=gridEl.querySelector(`td[data-row="${r}"][data-col="${c}"]`);
if(td) td.scrollIntoView({block:'nearest', inline:'nearest'});
}
let activeEditor=null;
function enterEditMode(){
if(isEditing) return;
isEditing=true;
const td=gridEl.querySelector(`td[data-row="${selRow}"][data-col="${selCol}"]`);
td.classList.add('editing');
const raw=cells[selRow][selCol].raw;
const input=document.createElement('input');
input.className='cellEditor';
input.value=raw;
input.spellcheck=false;
td.appendChild(input);
activeEditor=input;
input.focus(); input.select();
input.addEventListener('keydown', e=>{
if(e.key==='Enter'){ e.preventDefault(); commitEdit(); selectCell(selRow+1, selCol); }
else if(e.key==='Tab'){ e.preventDefault(); commitEdit(); selectCell(selRow, selCol+1); }
else if(e.key==='Escape'){ cancelEdit(); }
else if(e.key==='ArrowUp' && !input.value){ e.preventDefault(); commitEdit(); selectCell(selRow-1,selCol); }
else if(e.key==='ArrowDown' && !input.value){ e.preventDefault(); commitEdit(); selectCell(selRow+1,selCol); }
});
input.addEventListener('blur', ()=>{ if(isEditing) commitEdit(); });
// sync with formula bar
input.addEventListener('input', ()=>{ formulaInput.value=input.value; });
}
function commitEdit(){
if(!isEditing) return;
let newRaw='';
if(activeEditor) newRaw=activeEditor.value;
isEditing=false;
const td=gridEl.querySelector(`td[data-row="${selRow}"][data-col="${selCol}"]`);
if(td){ td.classList.remove('editing'); if(activeEditor) activeEditor.remove(); }
activeEditor=null;
setCellRaw(selRow, selCol, newRaw);
}
function cancelEdit(){
isEditing=false;
const td=gridEl.querySelector(`td[data-row="${selRow}"][data-col="${selCol}"]`);
if(td){ td.classList.remove('editing'); if(activeEditor) activeEditor.remove(); }
activeEditor=null;
highlightSelection();
}
function setCellRaw(r,c,raw){
const oldRaw=cells[r][c].raw;
if(oldRaw===raw) return;
cells[r][c].raw=raw;
const id=cellIdFromRC(r,c);
updateGraphForCell(id, raw);
// recompute from this cell
// need cacheMap already has entries for all cells from initial recomputeAll
recomputeFrom(id);
saveToStorage();
// sync formula bar if selected
if(r===selRow && c===selCol) formulaInput.value=raw;
}
formulaInput.addEventListener('keydown', e=>{
if(e.key==='Enter'){
e.preventDefault();
const raw=formulaInput.value;
setCellRaw(selRow, selCol, raw);
selectCell(selRow+1, selCol);
formulaInput.focus();
} else if(e.key==='Escape'){
formulaInput.value=cells[selRow][selCol].raw;
formulaInput.blur();
}
});
formulaInput.addEventListener('input', ()=>{
// live sync: update raw but debounce recompute? we recompute instantly
const r=selRow, c=selCol;
const raw=formulaInput.value;
// if editing in cell, sync editor
if(isEditing && activeEditor) activeEditor.value=raw;
// update graph live but avoid excessive recompute? do it
cells[r][c].raw=raw;
const id=cellIdFromRC(r,c);
updateGraphForCell(id, raw);
recomputeFrom(id);
saveToStorage();
});
formulaInput.addEventListener('focus', ()=>{ formulaInput.select(); });
/* Keyboard navigation when not editing */
document.addEventListener('keydown', e=>{
if(isEditing) return;
if(e.target===formulaInput) return;
if(e.key==='ArrowUp'){ e.preventDefault(); selectCell(selRow-1, selCol); }
else if(e.key==='ArrowDown'){ e.preventDefault(); selectCell(selRow+1, selCol); }
else if(e.key==='ArrowLeft'){ e.preventDefault(); selectCell(selRow, selCol-1); }
else if(e.key==='ArrowRight'){ e.preventDefault(); selectCell(selRow, selCol+1); }
else if(e.key==='Enter'){ e.preventDefault(); enterEditMode(); }
else if(e.key==='Tab'){ e.preventDefault(); if(e.shiftKey) selectCell(selRow, selCol-1); else selectCell(selRow, selCol+1); }
else if(e.key==='Delete' || e.key==='Backspace'){
// if not editing, clear cell
if(e.target.tagName!=='INPUT'){
e.preventDefault();
setCellRaw(selRow, selCol, '');
highlightSelection();
}
} else if(e.key.length===1 && !e.ctrlKey && !e.metaKey && !e.altKey){
// start typing in cell
enterEditMode();
setTimeout(()=>{
if(activeEditor){ activeEditor.value=e.key; activeEditor.selectionStart=1; formulaInput.value=e.key; }
},0);
}
});
/* ============================================================
ROW/COL OPERATIONS with formula rewriting
============================================================ */
function rewriteAllFormulas(opts){
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++){
let raw=cells[r][c].raw;
if(raw && raw[0]==='='){
let newRaw=raw.replace(/([A-Z]+)(\d+)/g, (match, colLetters, rowNumStr)=>{
let colIdx=colToIndex(colLetters);
let rowIdx=parseInt(rowNumStr,10)-1;
if(opts.insertRow!==undefined && opts.insertRow!==null){
if(rowIdx>=opts.insertRow) rowIdx++;
} else if(opts.deleteRow!==undefined && opts.deleteRow!==null){
if(rowIdx===opts.deleteRow) return '#REF!';
if(rowIdx>opts.deleteRow) rowIdx--;
}
if(opts.insertCol!==undefined && opts.insertCol!==null){
if(colIdx>=opts.insertCol) colIdx++;
} else if(opts.deleteCol!==undefined && opts.deleteCol!==null){
if(colIdx===opts.deleteCol) return '#REF!';
if(colIdx>opts.deleteCol) colIdx--;
}
return indexToCol(colIdx)+(rowIdx+1);
});
cells[r][c].raw=newRaw;
}
}
}
function insertRow(at){
rewriteAllFormulas({insertRow:at});
cells.splice(at,0, Array.from({length:COLS}, ()=>({raw:''})));
ROWS++;
rebuildGraph(); recomputeAll(); createGridStructure(); saveToStorage();
statusInfo.textContent=`Inserted row ${at+1}`;
}
function deleteRow(at){
if(ROWS<=1) return;
rewriteAllFormulas({deleteRow:at});
cells.splice(at,1);
ROWS--;
if(selRow>=ROWS) selRow=ROWS-1;
rebuildGraph(); recomputeAll(); createGridStructure(); saveToStorage();
statusInfo.textContent=`Deleted row ${at+1}`;
}
function insertCol(at){
rewriteAllFormulas({insertCol:at});
for(let r=0;r<ROWS;r++) cells[r].splice(at,0,{raw:''});
COLS++;
rebuildGraph(); recomputeAll(); createGridStructure(); saveToStorage();
statusInfo.textContent=`Inserted column ${indexToCol(at)}`;
}
function deleteCol(at){
if(COLS<=1) return;
rewriteAllFormulas({deleteCol:at});
for(let r=0;r<ROWS;r++) cells[r].splice(at,1);
COLS--;
if(selCol>=COLS) selCol=COLS-1;
rebuildGraph(); recomputeAll(); createGridStructure(); saveToStorage();
statusInfo.textContent=`Deleted column ${indexToCol(at)}`;
}
/* Toolbar buttons */
document.getElementById('btnInsertRow').addEventListener('click', ()=>insertRow(selRow));
document.getElementById('btnDeleteRow').addEventListener('click', ()=>deleteRow(selRow));
document.getElementById('btnInsertCol').addEventListener('click', ()=>insertCol(selCol));
document.getElementById('btnDeleteCol').addEventListener('click', ()=>deleteCol(selCol));
document.getElementById('btnClear').addEventListener('click', ()=>{ setCellRaw(selRow,selCol,''); highlightSelection(); });
document.getElementById('btnClearAll').addEventListener('click', ()=>{
if(confirm('Clear all cells?')){
for(let r=0;r<ROWS;r++) for(let c=0;c<COLS;c++) cells[r][c].raw='';
rebuildGraph(); recomputeAll(); saveToStorage();
}
});
/* Context menus */
function showRowMenu(e,row){
contextMenu.innerHTML='';
const opts=[
{label:`Insert row above`, fn:()=>insertRow(row)},
{label:`Insert row below`, fn:()=>insertRow(row+1)},
{label:`Delete row ${row+1}`, fn:()=>deleteRow(row)},
];
opts.forEach(o=>{ const d=document.createElement('div'); d.textContent=o.label; d.onclick=()=>{ hideMenu(); o.fn(); }; contextMenu.appendChild(d); });
contextMenu.style.left=e.clientX+'px'; contextMenu.style.top=e.clientY+'px'; contextMenu.style.display='block';
}
function showColMenu(e,col){
contextMenu.innerHTML='';
const label=indexToCol(col);
const opts=[
{label:`Insert column left of ${label}`, fn:()=>insertCol(col)},
{label:`Insert column right of ${label}`, fn:()=>insertCol(col+1)},
{label:`Delete column ${label}`, fn:()=>deleteCol(col)},
];
opts.forEach(o=>{ const d=document.createElement('div'); d.textContent=o.label; d.onclick=()=>{ hideMenu(); o.fn(); }; contextMenu.appendChild(d); });
contextMenu.style.left=e.clientX+'px'; contextMenu.style.top=e.clientY+'px'; contextMenu.style.display='block';
}
function hideMenu(){ contextMenu.style.display='none'; }
document.addEventListener('click', hideMenu);
containerEl.addEventListener('scroll', hideMenu);
/* ============================================================
PERSISTENCE
============================================================ */
function saveToStorage(){
try{
const data={rows:ROWS, cols:COLS, cells:cells.map(r=>r.map(c=>c.raw))};
localStorage.setItem(CONFIG.STORAGE_KEY, JSON.stringify(data));
}catch(e){}
}
function loadFromStorage(){
try{
const s=localStorage.getItem(CONFIG.STORAGE_KEY);
if(!s) return false;
const data=JSON.parse(s);
if(!data.cells) return false;
ROWS=data.rows; COLS=data.cols;
cells=data.cells.map(row=>row.map(raw=>({raw:raw||''})));
// ensure correct dimensions if corrupted
// pad/truncate
while(cells.length<ROWS) cells.push(Array.from({length:COLS},()=>({raw:''})));
while(cells.length>ROWS) cells.pop();
for(let r=0;r<ROWS;r++){
while(cells[r].length<COLS) cells[r].push({raw:''});
while(cells[r].length>COLS) cells[r].pop();
}
return true;
}catch(e){ return false; }
}
function initEmpty(){
cells=Array.from({length:ROWS}, ()=>Array.from({length:COLS}, ()=>({raw:''})));
}
/* ============================================================
INIT
============================================================ */
(function init(){
const loaded=loadFromStorage();
if(!loaded) initEmpty();
rebuildGraph();
createGridStructure();
recomputeAll();
highlightSelection();
// demo hint status
if(!loaded){
statusInfo.textContent='Tip: try =SUM(A1:A5) • =A1*2+B1/4 • =AVG(A1:A3)';
} else statusInfo.textContent='Restored from localStorage';
})();
</script>
</body>
</html>
<!-- agent-meta {"model":"meta/muse-spark-1.2","provider":"openrouter","persona":"basic","sessionId":"8da5e594-4149-4f35-a737-c51742db0a95","tokensIn":1124,"tokensOut":16519,"tokensTotal":17643,"cost":0.07141605,"turns":1,"toolCalls":0,"failedToolCalls":0,"timestamp":"2026-08-06T15:11:11.433Z"} -->