Spreadsheet
ling 3.0 Flash · full

Browser check: Artifact runtime error. This is a technical check, not a quality review.
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 36,361 bytes · SHA-256 d697ecfbcece
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>Spreadsheet</title>
<style>
/* ===== Tunable Constants ===== */
:root {
--col-width: 80px;
--row-header-width: 40px;
--header-height: 36px;
--cell-height: 24px;
--font-size: 13px;
--font-mono: 'Consolas', 'Monaco', monospace;
--color-bg: #ffffff;
--color-grid: #d0d0d0;
--color-header-bg: #f0f0f0;
--color-header-border: #b0b0b0;
--color-selection-bg: #cce5ff;
--color-selection-border: #4a90d9;
--color-active-border: #2563eb;
--color-error: #d32f2f;
--color-formula-bar-bg: #fafafa;
--color-pinned: #e8e8e8;
}
* { margin: 0; padding: 0; box-sizing: border-box; }
body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; font-size: var(--font-size); background: #f5f5f5; overflow: hidden; height: 100vh; display: flex; flex-direction: column; }
#toolbar { display: flex; align-items: center; gap: 4px; padding: 4px 8px; background: #fff; border-bottom: 1px solid #ccc; min-height: 36px; flex-wrap: wrap; }
#toolbar button { padding: 4px 10px; border: 1px solid #ccc; background: #fff; border-radius: 3px; cursor: pointer; font-size: 12px; }
#toolbar button:hover { background: #e8e8e8; }
#toolbar .sep { width: 1px; height: 20px; background: #ccc; margin: 0 4px; }
#formula-bar-wrap { display: flex; align-items: center; padding: 4px 8px; background: var(--color-formula-bar-bg); border-bottom: 1px solid #ccc; min-height: 32px; }
#formula-bar-label { font-weight: 600; margin-right: 6px; color: #555; min-width: 30px; font-size: 13px; }
#formula-bar { flex: 1; padding: 4px 8px; border: 1px solid #ccc; border-radius: 3px; font-size: var(--font-size); font-family: var(--font-mono); background: #fff; outline: none; min-height: 24px; line-height: 24px; }
#formula-bar:focus { border-color: var(--color-active-border); box-shadow: 0 0 0 1px rgba(37,99,235,.2); }
#grid-wrap { flex: 1; overflow: auto; position: relative; background: var(--color-bg); }
#grid { border-collapse: collapse; border-spacing: 0; table-layout: fixed; }
#grid th, #grid td { border: 0.5px solid var(--color-grid); padding: 0; overflow: hidden; white-space: nowrap; text-overflow: ellipsis; font-size: var(--font-size); height: var(--cell-height); line-height: var(--cell-height); }
#grid th { background: var(--color-header-bg); border-bottom: 2px solid var(--color-header-border); font-weight: 600; text-align: center; color: #555; position: sticky; z-index: 2; cursor: default; user-select: none; }
#grid th.row-header { background: var(--color-pinned); border-right: 2px solid var(--color-header-border); z-index: 3; }
#grid th.col-header { background: var(--color-pinned); border-bottom: 2px solid var(--color-header-border); z-index: 3; }
#grid th.corner { background: #e0e0e0; z-index: 4; }
#grid td { text-align: right; padding: 0 4px; cursor: default; position: relative; }
#grid td.text-cell { text-align: left; }
#grid td.error-cell { color: var(--color-error); font-style: italic; }
#grid td .cell-input { width: 100%; border: none; outline: none; font-size: var(--font-size); font-family: var(--font-mono); background: transparent; text-align: inherit; padding: 0; height: 100%; box-sizing: border-box; }
#grid td .cell-input.text-input { text-align: left; }
#grid td.selected { background: var(--color-selection-bg); }
#grid td .selection-border { position: absolute; top: -1px; left: -1px; right: -1px; bottom: -1px; border: 2px solid var(--color-active-border); pointer-events: none; z-index: 5; display: none; }
#grid td.active .selection-border { display: block; }
#grid td.selected .selection-border { display: block; border-color: var(--color-selection-border); }
</style>
</head>
<body>
<div id="toolbar">
<button id="btn-ins-row">Insert Row</button>
<button id="btn-del-row">Delete Row</button>
<button id="btn-ins-col">Insert Column</button>
<button id="btn-del-col">Delete Column</button>
<div class="sep"></div>
<button id="btn-clear">Clear</button>
<div class="sep"></div>
<button id="btn-save">Save</button>
<button id="btn-load">Load</button>
</div>
<div id="formula-bar-wrap">
<span id="formula-bar-label">A1</span>
<input id="formula-bar" type="text" spellcheck="false" autocomplete="off">
</div>
<div id="grid-wrap">
<table id="grid"></table>
</div>
<script>
/* ===== Tunable Constants ===== */
const COLS = 26;
const ROWS = 100;
const COL_LABELS = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');
/* ===== State ===== */
let grid = []; // grid[r][c] = { formula: string, value: any, error: string|null }
let selRow = 0, selCol = 0;
let editing = false;
let depGraph = {}; // cell -> Set of cells it depends on
let revDep = {}; // cell -> Set of cells that depend on it
/* ===== Cell Address Helpers ===== */
function addr(r, c) { return COL_LABELS[c] + (r + 1); }
function parseAddr(s) {
const m = s.match(/^([A-Z]+)(\d+)$/i);
if (!m) return null;
let col = 0;
for (let i = 0; i < m[1].length; i++) col = col * 26 + m[1].charCodeAt(i) - 64;
return { row: parseInt(m[2]) - 1, col: col - 1 };
}
function inBounds(r, c) { return r >= 0 && r < ROWS && c >= 0 && c < COLS; }
/* ===== Initialize Grid ===== */
function initGrid() {
grid = [];
for (let r = 0; r < ROWS; r++) {
grid[r] = [];
for (let c = 0; c < COLS; c++) {
grid[r][c] = { formula: '', value: '', error: null };
}
}
depGraph = {}; revDep = {};
}
initGrid();
/* ===== DOM References ===== */
const gridEl = document.getElementById('grid');
const formulaBar = document.getElementById('formula-bar');
const formulaBarLabel = document.getElementById('formula-bar-label');
const gridWrap = document.getElementById('grid-wrap');
/* ===== Build Table DOM ===== */
function buildTable() {
gridEl.innerHTML = '';
const thead = document.createElement('thead');
const headerRow = document.createElement('tr');
const corner = document.createElement('th');
corner.className = 'corner';
corner.style.width = 'var(--row-header-width)';
headerRow.appendChild(corner);
for (let c = 0; c < COLS; c++) {
const th = document.createElement('th');
th.className = 'col-header';
th.textContent = COL_LABELS[c];
th.style.width = 'var(--col-width)';
th.dataset.col = c;
headerRow.appendChild(th);
}
thead.appendChild(headerRow);
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 = 'row-header';
rh.textContent = r + 1;
rh.dataset.row = r;
tr.appendChild(rh);
for (let c = 0; c < COLS; c++) {
const td = document.createElement('td');
td.dataset.row = r;
td.dataset.col = c;
const span = document.createElement('span');
span.className = 'cell-value';
td.appendChild(span);
const input = document.createElement('input');
input.className = 'cell-input';
input.spellcheck = false;
input.autocomplete = 'off';
td.appendChild(input);
const border = document.createElement('div');
border.className = 'selection-border';
td.appendChild(border);
tr.appendChild(td);
}
tbody.appendChild(tr);
}
gridEl.appendChild(tbody);
updateAllCells();
refreshSelection();
}
function getCellEl(r, c) { return gridEl.querySelector(`td[data-row="${r}"][data-col="${c}"]`); }
function getCellValueEl(r, c) { return getCellEl(r, c)?.querySelector('.cell-value'); }
function getCellInputEl(r, c) { return getCellEl(r, c)?.querySelector('.cell-input'); }
/* ===== Display Helpers ===== */
function formatValue(val) {
if (val === '' || val === null || val === undefined) return '';
if (typeof val === 'number') {
if (Number.isNaN(val)) return '#ERR!';
if (!Number.isFinite(val)) return '#DIV/0!';
// Show integers without decimals, else up to 10
if (Number.isInteger(val)) return String(val);
return parseFloat(val.toPrecision(10)).toString();
}
return String(val);
}
function updateCellDisplay(r, c) {
const cell = grid[r][c];
const el = getCellEl(r, c);
if (!el) return;
const valEl = el.querySelector('.cell-value');
const inputEl = el.querySelector('.cell-input');
const text = formatValue(cell.value);
valEl.textContent = text;
el.classList.toggle('text-cell', !cell.error && typeof cell.value === 'string' && cell.formula === '');
el.classList.toggle('error-cell', !!cell.error);
inputEl.value = cell.formula;
inputEl.className = 'cell-input' + (typeof cell.value === 'string' && cell.formula === '' ? ' text-input' : '');
}
function updateAllCells() {
for (let r = 0; r < ROWS; r++)
for (let c = 0; c < COLS; c++)
updateCellDisplay(r, c);
}
/* ===== Selection ===== */
function refreshSelection() {
gridEl.querySelectorAll('td').forEach(td => {
td.classList.remove('selected', 'active');
});
const el = getCellEl(selRow, selCol);
if (el) {
el.classList.add('selected', 'active');
el.scrollIntoView({ block: 'nearest', inline: 'nearest' });
}
formulaBarLabel.textContent = addr(selRow, selCol);
if (!editing) {
formulaBar.value = grid[selRow][selCol].formula;
}
}
function selectCell(r, c) {
if (!inBounds(r, c)) return;
selRow = r; selCol = c;
editing = false;
refreshSelection();
}
/* ===== Formula Engine: Tokenizer ===== */
function tokenize(expr) {
const tokens = [];
let i = 0;
while (i < expr.length) {
const ch = expr[i];
if (/\s/.test(ch)) { i++; continue; }
if ('+-*/()^,'.includes(ch)) { tokens.push({ type: ch, value: ch }); i++; continue; }
if (ch === '=') { i++; continue; }
if (/[A-Z]/i.test(ch)) {
let j = i;
while (j < expr.length && /[A-Z]/i.test(expr[j])) j++;
const colPart = expr.slice(i, j).toUpperCase();
if (j < expr.length && /\d/.test(expr[j])) {
let k = j;
while (k < expr.length && /\d/.test(expr[k])) k++;
const rowPart = expr.slice(j, k);
// Check if followed by : for a range
if (k < expr.length && expr[k] === ':') {
let m = k + 1;
if (m < expr.length && /[A-Z]/i.test(expr[m])) {
let n = m;
while (n < expr.length && /[A-Z]/i.test(expr[n])) n++;
if (n < expr.length && /\d/.test(expr[n])) {
let p = n;
while (p < expr.length && /\d/.test(expr[p])) p++;
const endRef = expr.slice(m, p);
tokens.push({ type: 'RANGE', value: colPart + rowPart + ':' + endRef });
i = p;
} else {
tokens.push({ type: 'ERR', value: 'Parse error' });
return tokens;
}
} else {
tokens.push({ type: 'ERR', value: 'Parse error' });
return tokens;
}
} else {
tokens.push({ type: 'REF', value: colPart + rowPart });
i = k;
}
} else if (j < expr.length && expr[j] === ':') {
// Range start like A1:
let k = j + 1;
if (k < expr.length && /[A-Z]/i.test(expr[k])) {
let m = k;
while (m < expr.length && /[A-Z]/i.test(expr[m])) m++;
if (m < expr.length && /\d/.test(expr[m])) {
let n = m;
while (n < expr.length && /\d/.test(expr[n])) n++;
const endRef = expr.slice(k, n);
tokens.push({ type: 'RANGE', value: colPart + rowPart + ':' + endRef });
i = n;
} else {
tokens.push({ type: 'ERR', value: 'Parse error' });
return tokens;
}
} else {
tokens.push({ type: 'ERR', value: 'Parse error' });
return tokens;
}
} else if (j < expr.length && expr[j] === '(') {
// Function name like SUM(
tokens.push({ type: 'FUNC', value: colPart });
i = j;
} else {
// Just column letters (like in a function arg that's not a ref) — treat as error or part of function name
tokens.push({ type: 'NAME', value: colPart });
i = j;
}
continue;
}
if (/\d/.test(ch) || (ch === '.' && i + 1 < expr.length && /\d/.test(expr[i + 1]))) {
let j = i;
while (j < expr.length && (/\d/.test(expr[j]) || expr[j] === '.')) j++;
tokens.push({ type: 'NUM', value: expr.slice(i, j) });
i = j;
continue;
}
// Quoted string
if (ch === '"') {
let j = i + 1;
let str = '';
while (j < expr.length && expr[j] !== '"') { str += expr[j]; j++; }
if (j < expr.length) { tokens.push({ type: 'STR', value: str }); i = j + 1; }
else { tokens.push({ type: 'ERR', value: 'Unterminated string' }); return tokens; }
continue;
}
tokens.push({ type: 'UNKNOWN', value: ch });
i++;
}
return tokens;
}
/* ===== Formula Engine: Parser (Recursive Descent) ===== */
// Grammar:
// expr = term (('+' | '-') term)*
// term = factor (('*' | '/') factor)*
// factor = ('-' factor) | primary
// primary = NUMBER | STRING | cellRef | range | functionCall | '(' expr ')' | NAME
// cellRef = COL LETTERS + DIGITS
// range = cellRef ':' cellRef
// functionCall = NAME '(' args ')'
// args = expr (',' expr)*
function parse(tokens) {
let pos = 0;
function peek() { return tokens[pos] || null; }
function consume(type) {
const t = tokens[pos];
if (t && (!type || t.type === type)) { pos++; return t; }
return null;
}
function expect(type) {
const t = consume(type);
if (!t) throw new Error('Parse error at token ' + pos + ': expected ' + type + ', got ' + (peek() ? peek().type : 'EOF'));
return t;
}
function parseExpr() {
let left = parseTerm();
while (peek() && (peek().type === '+' || peek().type === '-')) {
const op = consume();
const right = parseTerm();
left = { type: 'binop', op: op.value, left, right };
}
return left;
}
function parseTerm() {
let left = parseFactor();
while (peek() && (peek().type === '*' || peek().type === '/')) {
const op = consume();
const right = parseFactor();
left = { type: 'binop', op: op.value, left, right };
}
return left;
}
function parseFactor() {
if (peek() && peek().type === '-') {
consume();
const operand = parseFactor();
return { type: 'unary', op: '-', operand };
}
if (peek() && peek().type === '+') {
consume();
return parseFactor();
}
return parsePrimary();
}
function parsePrimary() {
const t = peek();
if (!t) throw new Error('Unexpected end of expression');
if (t.type === 'NUM') { consume(); return { type: 'num', value: parseFloat(t.value) }; }
if (t.type === 'STR') { consume(); return { type: 'str', value: t.value }; }
if (t.type === '(') {
consume();
const expr = parseExpr();
expect(')');
return expr;
}
if (t.type === 'REF' || t.type === 'RANGE') {
consume();
return { type: 'ref', value: t.value };
}
if (t.type === 'FUNC') {
consume();
expect('(');
const args = [];
if (peek() && peek().type !== ')') {
args.push(parseExpr());
while (peek() && peek().type === ',') {
consume();
args.push(parseExpr());
}
}
expect(')');
return { type: 'func', name: t.value, args };
}
if (t.type === 'NAME') {
consume();
return { type: 'name', value: t.value };
}
throw new Error('Unexpected token: ' + t.type + ' ' + t.value);
}
const ast = parseExpr();
if (pos < tokens.length) throw new Error('Unexpected token at position ' + pos);
return ast;
}
/* ===== Formula Engine: Evaluator ===== */
function evaluateAST(ast, currentCell) {
if (!ast) return { value: null, error: null };
switch (ast.type) {
case 'num': return { value: ast.value, error: null };
case 'str': return { value: ast.value, error: null };
case 'ref': {
const val = ast.value;
if (val.includes(':')) {
// Range reference like A1:A5
const parts = val.split(':');
const start = parseAddr(parts[0]);
const end = parseAddr(parts[1]);
if (!start || !end || !inBounds(start.row, start.col) || !inBounds(end.row, end.col)) {
return { value: null, error: '#REF!' };
}
const minR = Math.min(start.row, end.row), maxR = Math.max(start.row, end.row);
const minC = Math.min(start.col, end.col), maxC = Math.max(start.col, end.col);
const values = [];
for (let r = minR; r <= maxR; r++) {
for (let c = minC; c <= maxC; c++) {
const cell = grid[r][c];
if (cell.error) return { value: null, error: cell.error };
values.push(cell.value);
}
}
return { value: values, error: null };
}
const ref = parseAddr(val);
if (!ref || !inBounds(ref.row, ref.col)) return { value: null, error: '#REF!' };
const cell = grid[ref.row][ref.col];
if (cell.error) return { value: null, error: cell.error };
return { value: cell.value, error: null };
}
case 'binop': {
const left = evaluateAST(ast.left, currentCell);
if (left.error) return left;
const right = evaluateAST(ast.right, currentCell);
if (right.error) return right;
const l = left.value, r = right.value;
if (typeof l === 'string' || typeof r === 'string') {
// In spreadsheet, string + number = string concat, but for other ops error
if (ast.op === '+') return { value: String(l) + String(r), error: null };
return { value: null, error: '#ERR!' };
}
if (typeof l !== 'number' || typeof r !== 'number') return { value: null, error: '#ERR!' };
switch (ast.op) {
case '+': return { value: l + r, error: null };
case '-': return { value: l - r, error: null };
case '*': return { value: l * r, error: null };
case '/':
if (r === 0) return { value: null, error: '#DIV/0!' };
return { value: l / r, error: null };
default: return { value: null, error: '#ERR!' };
}
}
case 'unary': {
const operand = evaluateAST(ast.operand, currentCell);
if (operand.error) return operand;
if (typeof operand.value !== 'number') return { value: null, error: '#ERR!' };
return { value: ast.op === '-' ? -operand.value : operand.value, error: null };
}
case 'func': {
const evaluatedArgs = [];
for (const arg of ast.args) {
const ev = evaluateAST(arg, currentCell);
if (ev.error) return ev;
evaluatedArgs.push(ev.value);
}
return evalFunction(ast.name, evaluatedArgs, currentCell);
}
case 'name':
return { value: null, error: '#ERR!' };
default:
return { value: null, error: '#ERR!' };
}
}
function evalFunction(name, args, currentCell) {
// Flatten arrays from range references into individual values
const flatArgs = [];
for (const a of args) {
if (Array.isArray(a)) {
for (const v of a) flatArgs.push(v);
} else {
flatArgs.push(a);
}
}
switch (name) {
case 'SUM': {
let sum = 0;
for (const a of flatArgs) {
if (typeof a === 'number') sum += a;
else if (typeof a === 'string' && !isNaN(parseFloat(a))) sum += parseFloat(a);
else if (a === null || a === '') continue;
else return { value: null, error: '#ERR!' };
}
return { value: sum, error: null };
}
case 'AVG':
case 'AVERAGE': {
let sum = 0, count = 0;
for (const a of flatArgs) {
if (typeof a === 'number') { sum += a; count++; }
else if (typeof a === 'string' && !isNaN(parseFloat(a))) { sum += parseFloat(a); count++; }
else if (a === null || a === '') continue;
else return { value: null, error: '#ERR!' };
}
if (count === 0) return { value: null, error: '#DIV/0!' };
return { value: sum / count, error: null };
}
case 'MIN': {
const nums = flatArgs.filter(a => typeof a === 'number' || (typeof a === 'string' && !isNaN(parseFloat(a))));
if (nums.length === 0) return { value: null, error: '#ERR!' };
return { value: Math.min(...nums.map(Number)), error: null };
}
case 'MAX': {
const nums = flatArgs.filter(a => typeof a === 'number' || (typeof a === 'string' && !isNaN(parseFloat(a))));
if (nums.length === 0) return { value: null, error: '#ERR!' };
return { value: Math.max(...nums.map(Number)), error: null };
}
case 'COUNT': {
let count = 0;
for (const a of flatArgs) {
if (typeof a === 'number') count++;
}
return { value: count, error: null };
}
default:
return { value: null, error: '#ERR!' };
}
}
/* ===== Dependency Graph ===== */
function extractRefs(ast) {
const refs = new Set();
function walk(node) {
if (!node) return;
if (node.type === 'ref') {
const ref = parseAddr(node.value);
if (ref && inBounds(ref.row, ref.col)) refs.add(addr(ref.row, ref.col));
}
if (node.type === 'range') {
const parts = node.value.split(':');
const start = parseAddr(parts[0]);
const end = parseAddr(parts[1]);
if (start && end && inBounds(start.row, start.col) && inBounds(end.row, end.col)) {
const minR = Math.min(start.row, end.row), maxR = Math.max(start.row, end.row);
const minC = Math.min(start.col, end.col), maxC = Math.max(start.col, end.col);
for (let r = minR; r <= maxR; r++)
for (let c = minC; c <= maxC; c++)
refs.add(addr(r, c));
}
}
if (node.type === 'binop') { walk(node.left); walk(node.right); }
if (node.type === 'unary') { walk(node.operand); }
if (node.type === 'func') { for (const a of node.args) walk(a); }
}
walk(ast);
return refs;
}
function rebuildDeps() {
depGraph = {}; revDep = {};
for (let r = 0; r < ROWS; r++) {
for (let c = 0; c < COLS; c++) {
const cell = grid[r][c];
const myAddr = addr(r, c);
depGraph[myAddr] = new Set();
if (cell.formula.startsWith('=')) {
try {
const tokens = tokenize(cell.formula.slice(1));
const ast = parse(tokens);
const refs = extractRefs(ast);
depGraph[myAddr] = refs;
} catch (e) { /* parse error, no deps */ }
}
}
}
// Build reverse deps
for (const [cell, deps] of Object.entries(depGraph)) {
for (const dep of deps) {
if (!revDep[dep]) revDep[dep] = new Set();
revDep[dep].add(cell);
}
}
}
/* ===== Recalculation ===== */
function topologicalSort(startCells) {
const visited = new Set();
const order = [];
const temp = new Set(); // for cycle detection
function visit(cell) {
if (visited.has(cell)) return;
if (temp.has(cell)) return; // cycle detected, skip
temp.add(cell);
const deps = depGraph[cell] || new Set();
for (const dep of deps) {
visit(dep);
}
temp.delete(cell);
visited.add(cell);
order.push(cell);
}
for (const cell of startCells) visit(cell);
return order;
}
function recalculate(changedCell) {
rebuildDeps();
// Find all cells that transitively depend on changedCell
const toRecalc = new Set();
const queue = [changedCell];
while (queue.length > 0) {
const cell = queue.shift();
if (toRecalc.has(cell)) continue;
toRecalc.add(cell);
const dependents = revDep[cell] || new Set();
for (const dep of dependents) queue.push(dep);
}
// Sort in topological order, detecting cycles along the way
const sorted = [];
const visited = new Set();
const inStack = new Set();
const cycleNodes = new Set();
function visit(cell) {
if (visited.has(cell)) return;
if (cycleNodes.has(cell)) return;
if (inStack.has(cell)) {
// Found a back edge - mark all nodes in the current stack as cycle nodes
cycleNodes.add(cell);
return;
}
inStack.add(cell);
const deps = depGraph[cell] || new Set();
for (const dep of deps) visit(dep);
inStack.delete(cell);
if (cycleNodes.has(cell)) return; // Don't add cycle nodes to sorted
visited.add(cell);
sorted.push(cell);
}
for (const cell of [...toRecalc]) visit(cell);
// Propagate cycle status: any cell that depends on a cycle node is also in a cycle
let changed = true;
while (changed) {
changed = false;
for (const cell of toRecalc) {
if (cycleNodes.has(cell)) continue;
const deps = depGraph[cell] || new Set();
for (const dep of deps) {
if (cycleNodes.has(dep)) {
cycleNodes.add(cell);
changed = true;
break;
}
}
}
}
// Cells not in sorted are part of cycles
const cycleCells = new Set();
for (const cell of toRecalc) {
if (!sorted.includes(cell)) cycleCells.add(cell);
}
// Mark cycle cells
for (const cellAddr of cycleCells) {
const parts = parseAddr(cellAddr);
if (!parts) continue;
grid[parts.row][parts.col].error = '#CYCLE!';
grid[parts.row][parts.col].value = null;
}
// Recalculate non-cycle cells in topological order
for (const cellAddr of sorted) {
const parts = parseAddr(cellAddr);
if (!parts) continue;
const cell = grid[parts.row][parts.col];
if (!cell.formula.startsWith('=')) continue;
if (cycleCells.has(cellAddr)) continue;
try {
const tokens = tokenize(cell.formula.slice(1));
const ast = parse(tokens);
const result = evaluateAST(ast, cellAddr);
cell.value = result.value;
cell.error = result.error;
} catch (e) {
cell.value = null;
cell.error = '#ERR!';
}
}
// Clear stale cycle errors on non-cycle formula cells
for (let r = 0; r < ROWS; r++) {
for (let c = 0; c < COLS; c++) {
if (!grid[r][c].formula.startsWith('=')) continue;
const myAddr = addr(r, c);
if (cycleCells.has(myAddr)) continue;
if (grid[r][c].error === '#CYCLE!') {
try {
const tokens = tokenize(grid[r][c].formula.slice(1));
const ast = parse(tokens);
const result = evaluateAST(ast, myAddr);
grid[r][c].value = result.value;
grid[r][c].error = result.error;
} catch (e) {
grid[r][c].error = '#ERR!';
}
}
}
}
}
function setCellFormula(r, c, formula) {
grid[r][c].formula = formula;
recalculate(addr(r, c));
updateAllCells();
refreshSelection();
}
/* ===== Formula Bar & Cell Editing ===== */
function startEdit() {
editing = true;
formulaBar.focus();
formulaBar.select();
}
function commitEdit() {
if (!editing) return;
editing = false;
// Read from formula bar (primary editing surface)
grid[selRow][selCol].formula = formulaBar.value;
recalculate(addr(selRow, selCol));
updateAllCells();
refreshSelection();
}
function cancelEdit() {
editing = false;
formulaBar.value = grid[selRow][selCol].formula;
refreshSelection();
}
/* ===== Event Handlers ===== */
gridEl.addEventListener('mousedown', (e) => {
const td = e.target.closest('td');
if (!td) return;
const r = parseInt(td.dataset.row);
const c = parseInt(td.dataset.col);
if (isNaN(r) || isNaN(c)) return;
// If already editing and clicking same cell, don't interfere
if (editing && r === selRow && c === selCol) return;
if (editing) commitEdit();
selectCell(r, c);
// Double-click to edit
if (e.detail === 2) {
startEdit();
}
});
formulaBar.addEventListener('keydown', (e) => {
if (e.key === 'Enter') {
e.preventDefault();
commitEdit();
// Move down
if (selRow < ROWS - 1) selectCell(selRow + 1, selCol);
} else if (e.key === 'Tab') {
e.preventDefault();
commitEdit();
if (selCol < COLS - 1) selectCell(selRow, selCol + 1);
} else if (e.key === 'Escape') {
cancelEdit();
}
});
formulaBar.addEventListener('input', () => {
if (!editing) {
editing = true;
// Start edit mode — live preview as user types
}
grid[selRow][selCol].formula = formulaBar.value;
// Live preview
try {
const tokens = tokenize(formulaBar.value.slice(1));
const ast = parse(tokens);
const result = evaluateAST(ast, addr(selRow, selCol));
const valEl = getCellValueEl(selRow, selCol);
if (valEl) valEl.textContent = formatValue(result.value);
} catch (e) { /* ignore during typing */ }
});
document.addEventListener('keydown', (e) => {
if (editing) return; // let formula bar handle keys
const key = e.key;
if (key === 'ArrowUp' && selRow > 0) { e.preventDefault(); selectCell(selRow - 1, selCol); }
else if (key === 'ArrowDown' && selRow < ROWS - 1) { e.preventDefault(); selectCell(selRow + 1, selCol); }
else if (key === 'ArrowLeft' && selCol > 0) { e.preventDefault(); selectCell(selRow, selCol - 1); }
else if (key === 'ArrowRight' && selCol < COLS - 1) { e.preventDefault(); selectCell(selRow, selCol + 1); }
else if (key === 'Enter') { e.preventDefault(); startEdit(); }
else if (key === 'Delete' || key === 'Backspace') {
e.preventDefault();
grid[selRow][selCol].formula = '';
recalculate(addr(selRow, selCol));
updateAllCells();
refreshSelection();
}
});
// Prevent formula bar from handling arrow keys when grid is focused
formulaBar.addEventListener('keydown', (e) => {
// Already handled above for Enter/Tab/Escape
});
/* ===== Insert/Delete Rows & Columns ===== */
function insertRow(rowIdx) {
// Shift all formulas referencing rows >= rowIdx down by 1
for (let r = ROWS - 1; r > rowIdx; r--) {
for (let c = 0; c < COLS; c++) {
grid[r][c] = { ...grid[r - 1][c] };
}
}
// Clear the new row
for (let c = 0; c < COLS; c++) {
grid[rowIdx][c] = { formula: '', value: '', error: null };
}
// Rewrite all formulas to adjust row references
rewriteFormulas((ref) => {
const parsed = parseAddr(ref);
if (parsed && parsed.row >= rowIdx) {
return addr(parsed.row + 1, parsed.col);
}
return ref;
});
rebuildDeps();
recalculate(addr(0, 0));
updateAllCells();
refreshSelection();
}
function deleteRow(rowIdx) {
// Shift rows up
for (let r = rowIdx; r < ROWS - 1; r++) {
for (let c = 0; c < COLS; c++) {
grid[r][c] = { ...grid[r + 1][c] };
}
}
// Clear last row
for (let c = 0; c < COLS; c++) {
grid[ROWS - 1][c] = { formula: '', value: '', error: null };
}
// Rewrite formulas
rewriteFormulas((ref) => {
const parsed = parseAddr(ref);
if (parsed && parsed.row > rowIdx) {
return addr(parsed.row - 1, parsed.col);
} else if (parsed && parsed.row === rowIdx) {
return null; // reference deleted
}
return ref;
});
rebuildDeps();
recalculate(addr(0, 0));
updateAllCells();
refreshSelection();
}
function insertCol(colIdx) {
for (let c = COLS - 1; c > colIdx; c--) {
for (let r = 0; r < ROWS; r++) {
grid[r][c] = { ...grid[r][c - 1] };
}
}
for (let r = 0; r < ROWS; r++) {
grid[r][colIdx] = { formula: '', value: '', error: null };
}
rewriteFormulas((ref) => {
const parsed = parseAddr(ref);
if (parsed && parsed.col >= colIdx) {
return addr(parsed.row, parsed.col + 1);
}
return ref;
});
rebuildDeps();
recalculate(addr(0, 0));
updateAllCells();
refreshSelection();
}
function deleteCol(colIdx) {
for (let c = colIdx; c < COLS - 1; c++) {
for (let r = 0; r < ROWS; r++) {
grid[r][c] = { ...grid[r][c + 1] };
}
}
for (let r = 0; r < ROWS; r++) {
grid[r][COLS - 1] = { formula: '', value: '', error: null };
}
rewriteFormulas((ref) => {
const parsed = parseAddr(ref);
if (parsed && parsed.col > colIdx) {
return addr(parsed.row, parsed.col - 1);
} else if (parsed && parsed.col === colIdx) {
return null;
}
return ref;
});
rebuildDeps();
recalculate(addr(0, 0));
updateAllCells();
refreshSelection();
}
function rewriteFormulas(transformFn) {
for (let r = 0; r < ROWS; r++) {
for (let c = 0; c < COLS; c++) {
const cell = grid[r][c];
if (!cell.formula.startsWith('=')) continue;
let newFormula = cell.formula;
const tokens = tokenize(cell.formula.slice(1));
let offset = 0;
for (const tok of tokens) {
if (tok.type === 'REF' || tok.type === 'RANGE') {
const rangeParts = tok.value.split(':');
const transformed = rangeParts.map(transformFn);
if (transformed.includes(null)) {
// Reference points to deleted row/col - replace with #REF!
const idx = newFormula.indexOf(tok.value, offset);
if (idx !== -1) {
newFormula = newFormula.substring(0, idx) + '#REF!' + newFormula.substring(idx + tok.value.length);
offset = idx + 5; // length of '#REF!'
}
} else {
const newRef = transformed.join(':');
if (newRef !== tok.value) {
const idx = newFormula.indexOf(tok.value, offset);
if (idx !== -1) {
newFormula = newFormula.substring(0, idx) + newRef + newFormula.substring(idx + tok.value.length);
offset = idx + newRef.length;
}
}
}
}
}
cell.formula = newFormula;
}
}
}
/* ===== Toolbar Buttons ===== */
document.getElementById('btn-ins-row').addEventListener('click', () => insertRow(selRow));
document.getElementById('btn-del-row').addEventListener('click', () => deleteRow(selRow));
document.getElementById('btn-ins-col').addEventListener('click', () => insertCol(selCol));
document.getElementById('btn-del-col').addEventListener('click', () => deleteCol(selCol));
document.getElementById('btn-clear').addEventListener('click', () => {
grid[selRow][selCol].formula = '';
recalculate(addr(selRow, selCol));
updateAllCells();
refreshSelection();
});
/* ===== localStorage Serialization ===== */
function serialize() {
const data = [];
for (let r = 0; r < ROWS; r++) {
for (let c = 0; c < COLS; c++) {
if (grid[r][c].formula !== '') {
data.push({ r, c, formula: grid[r][c].formula });
}
}
}
return JSON.stringify(data);
}
function deserialize(json) {
initGrid();
try {
const data = JSON.parse(json);
for (const entry of data) {
if (inBounds(entry.r, entry.c)) {
grid[entry.r][entry.c].formula = entry.formula;
}
}
} catch (e) { /* ignore */ }
rebuildDeps();
recalculate(addr(0, 0));
updateAllCells();
refreshSelection();
}
document.getElementById('btn-save').addEventListener('click', () => {
localStorage.setItem('spreadsheet', serialize());
});
document.getElementById('btn-load').addEventListener('click', () => {
const data = localStorage.getItem('spreadsheet');
if (data) deserialize(data);
});
// Auto-save on changes
let saveTimeout;
function autoSave() {
clearTimeout(saveTimeout);
saveTimeout = setTimeout(() => {
localStorage.setItem('spreadsheet', serialize());
}, 1000);
}
// Hook into setCellFormula for auto-save
const origSetCellFormula = setCellFormula;
// We'll patch after definition
/* ===== Right-click context menu for headers ===== */
document.querySelectorAll('#grid th.col-header').forEach(th => {
th.addEventListener('contextmenu', (e) => {
e.preventDefault();
const c = parseInt(th.dataset.col);
if (confirm('Delete column ' + COL_LABELS[c] + '?')) deleteCol(c);
});
});
document.querySelectorAll('#grid th.row-header').forEach(th => {
th.addEventListener('contextmenu', (e) => {
e.preventDefault();
const r = parseInt(th.dataset.row);
if (confirm('Delete row ' + (r + 1) + '?')) deleteRow(r);
});
});
/* ===== Initialize ===== */
buildTable();
selectCell(0, 0);
// Auto-load saved state
const saved = localStorage.getItem('spreadsheet');
if (saved) deserialize(saved);
</script>
</body>
</html>
<!-- agent-meta {"model":"inclusionai/ling-3.0-flash","provider":"openrouter","persona":"full","sessionId":"5ff83fbf-5571-462e-9c4a-7b834903b1d8","tokensIn":11162284,"tokensOut":99774,"tokensTotal":11262058,"cost":0.0949556556,"turns":104,"toolCalls":103,"failedToolCalls":3,"timestamp":"2026-08-24T22:59:11.264Z"} -->