Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.
TypeScript agentaiai-agentsall-in-onecrmcrm-systememaillinearmcpmessagingnotesnotionofficerustslackslack-alternativestartupstartup-toolstypescriptworkspace
import { cellDateMention, cellPlainText, cellTextParts } from './cell-mentions';
import { checkboxChecked } from './checkboxes';
import {
DROPDOWN_CHOICE_COLORS,
dropdownRule,
literalListItems,
MAX_LIST_FORMULA_LENGTH,
replaceValidations,
} from './data-validation';
import {
boundsSqref,
type CellBounds,
type DataValidation,
sqrefBounds,
sqrefContains,
} from './sheet-rules';
import {
createTable,
findTable,
replaceTable,
type SheetTable,
type TableChange,
type TableColumnType,
tableBounds,
tableColumnNames,
tableDataRows,
withEdits,
} from './sheet-tables';
import {
formatCellAddress,
parseCellAddress,
type SpreadsheetCell,
type SpreadsheetCellEdits,
type SpreadsheetCellStyle,
type SpreadsheetCells,
} from './spreadsheet-document';
import { hasThisRowReference } from './structured-references';
import type { WorkbookSheetMetadata } from './workbook-metadata';
export const TABLE_COLUMN_TYPE_LABELS: Record<TableColumnType, string> = {
text: 'Text',
number: 'Number',
percent: 'Percent',
currency: 'Currency',
date: 'Date',
datetime: 'Date time',
time: 'Time',
checkbox: 'Checkbox',
dropdown: 'Dropdown',
person: 'People',
};
const PLAIN: SpreadsheetCellStyle = {
format: 'general',
numberFormat: '',
checkbox: false,
};
/** The number format, or checkbox, a column type gives its cells. */
export function columnTypeStyle(type: TableColumnType): SpreadsheetCellStyle {
switch (type) {
case 'text':
return { ...PLAIN, format: 'text' };
case 'number':
case 'percent':
case 'currency':
case 'date':
case 'time':
return { ...PLAIN, format: type };
case 'datetime':
return { ...PLAIN, format: 'date', numberFormat: 'm/d/yyyy h:mm' };
case 'checkbox':
return { ...PLAIN, checkbox: true };
case 'dropdown':
case 'person':
return PLAIN;
}
}
/** A cell's value as a type check reads it: its shown text, and its number
* when it calculates to one. */
export type TableCellValue = {
display: string;
number?: number;
error?: string;
};
/**
* Why a cell's value does not fit its column's type, or undefined when it
* fits. Blank cells and errors fit every type. `choices` are a dropdown
* column's options.
*/
export function columnTypeMismatch(
type: TableColumnType | undefined,
cell: SpreadsheetCell | undefined,
value: TableCellValue | undefined,
choices?: string[]
): string | undefined {
const source = cell?.value ?? '';
if (!type || !source.trim() || value?.error) return;
const display = (value?.display ?? source).trim();
const number =
value?.number ??
(!source.startsWith('=') && Number.isFinite(Number(source))
? Number(source)
: undefined);
switch (type) {
case 'number':
case 'percent':
case 'currency':
return number === undefined ? 'Expected a number' : undefined;
case 'date':
case 'datetime':
return number === undefined && !cellDateMention(source)
? 'Expected a date'
: undefined;
case 'time':
return number === undefined ? 'Expected a time' : undefined;
case 'checkbox':
return checkboxChecked(display) === undefined
? 'Expected a checkbox: TRUE or FALSE'
: undefined;
case 'dropdown':
return choices &&
!choices.some(
(choice) => choice.toLowerCase() === display.toLowerCase()
)
? 'Expected one of the dropdown’s options'
: undefined;
case 'person':
return cellTextParts(source).every((part) =>
part.mention ? part.mention.type === 'user' : /^[\s,]*$/.test(part.text)
) && cellTextParts(source).some((part) => part.mention)
? undefined
: 'Expected a person: type @ to mention someone';
case 'text':
return;
}
}
/** Choices for a column becoming a dropdown: its distinct values, as many
* as a typed list holds, or two placeholders. */
function dropdownChoices(values: string[]): string[] {
const choices: string[] = [];
const seen = new Set<string>();
let length = 2;
for (const value of values) {
const choice = value.trim();
const key = choice.toLowerCase();
if (!choice || choice.includes(',') || seen.has(key)) continue;
const next =
length + choice.replace(/"/g, '""').length + (choices.length ? 1 : 0);
if (next > MAX_LIST_FORMULA_LENGTH) break;
choices.push(choice);
seen.add(key);
length = next;
}
return choices.length ? choices : ['Option 1', 'Option 2'];
}
/** A list rule covering every cell of `area`. */
function listRuleCovering(
validations: DataValidation[] | undefined,
area: CellBounds
) {
return validations?.find(
(rule) =>
rule.type === 'list' &&
Array.from({ length: area.bottom - area.top + 1 }).every((_, offset) =>
sqrefContains(rule.range, area.top + offset, area.left)
)
);
}
/** The options of a dropdown column, when its list is typed. */
export function columnChoices(
validations: DataValidation[] | undefined,
table: SheetTable,
column: number
): string[] | undefined {
const { first, last } = tableDataRows(table);
const rule = listRuleCovering(validations, {
top: first,
bottom: last,
left: column,
right: column,
});
const source = rule?.formulas?.[0];
return source === undefined ? undefined : literalListItems(source);
}
/**
* Give a table column a type, or none. The column's data cells take the
* type's number format; a checkbox column shows its blank cells unchecked,
* as FALSE, and a dropdown column offers its values, colored, as choices.
* Leaving the checkbox or dropdown type removes its checkboxes or list.
*/
export function setTableColumnType(options: {
metadata: WorkbookSheetMetadata | undefined;
cells: SpreadsheetCells;
name: string;
column: number;
type: TableColumnType | undefined;
/** What a cell shows, for a new dropdown's choices. */
display: (row: number, column: number) => string;
}): TableChange {
const { cells, column, type } = options;
const table = findTable(options.metadata, options.name);
const bounds = tableBounds(table);
if (column < bounds.left || column > bounds.right)
throw new Error('Choose a column of the table.');
const offset = String(column - bounds.left);
const previous = table.columnTypes?.[offset];
const { first, last } = tableDataRows(table);
const area = { top: first, bottom: last, left: column, right: column };
const edits: SpreadsheetCellEdits = {};
for (let row = first; row <= last; row++) {
const address = formatCellAddress(row, column);
const cell = cells[address];
if (type) edits[address] = columnTypeStyle(type);
else if (previous === 'checkbox' && cell?.checkbox)
edits[address] = { checkbox: false };
if (type === 'checkbox' && !cell?.value)
edits[address] = { ...edits[address], value: 'FALSE' };
}
let validations = options.metadata?.validations;
const range = boundsSqref([area]);
if (type === 'dropdown' && !listRuleCovering(validations, area)) {
const values: string[] = [];
for (let row = first; row <= last; row++)
values.push(options.display(row, column));
const choices = dropdownChoices(values);
validations = replaceValidations(
validations,
range,
dropdownRule({
items: choices,
rejectInvalid: false,
colors: choices.map((item, index) => ({
item,
color:
DROPDOWN_CHOICE_COLORS[index % DROPDOWN_CHOICE_COLORS.length].value,
})),
})
);
} else if (previous === 'dropdown' && type !== 'dropdown')
validations = replaceValidations(validations, range);
const metadata = replaceTable(
options.metadata ?? {},
table.name,
(current) => {
const columnTypes = { ...current.columnTypes };
if (type) columnTypes[offset] = type;
else delete columnTypes[offset];
const { columnTypes: _types, ...rest } = current;
return Object.keys(columnTypes).length ? { ...rest, columnTypes } : rest;
}
);
const { validations: _validations, ...rest } = metadata;
return {
metadata: validations?.length ? { ...rest, validations } : rest,
edits,
};
}
/**
* Column types a new table's data suggests, by column position: a type
* every value of a column fits, from its formats, checkboxes, list rules and
* values; text takes any other literal values. Columns with formulas or
* TRUE/FALSE among other values, or with no values, stay untyped.
*/
export function detectColumnTypes(
table: SheetTable,
cells: SpreadsheetCells,
validations?: DataValidation[]
): Record<string, TableColumnType> {
const bounds = tableBounds(table);
const { first, last } = tableDataRows(table);
const types: Record<string, TableColumnType> = {};
for (let column = bounds.left; column <= bounds.right; column++) {
const filled: SpreadsheetCell[] = [];
for (let row = first; row <= last; row++) {
const cell = cells[formatCellAddress(row, column)];
if (cell?.value.trim()) filled.push(cell);
}
const listed = !!listRuleCovering(validations, {
top: first,
bottom: last,
left: column,
right: column,
});
const type = filled.length ? suggestedType(filled, listed) : undefined;
if (type) types[String(column - bounds.left)] = type;
}
return types;
}
/** The type every one of a column's filled cells fits, if there is one. */
function suggestedType(
filled: SpreadsheetCell[],
listed: boolean
): TableColumnType | undefined {
const every = (test: (cell: SpreadsheetCell) => boolean) =>
filled.every(test);
const numeric = (cell: SpreadsheetCell) =>
!cell.value.startsWith('=') && Number.isFinite(Number(cell.value));
const formatted = (...formats: SpreadsheetCell['format'][]) =>
every((cell) => formats.includes(cell.format) && numeric(cell));
if (every((cell) => !!cell.checkbox)) return 'checkbox';
if (listed) return 'dropdown';
if (every((cell) => !columnTypeMismatch('person', cell, undefined)))
return 'person';
if (formatted('percent')) return 'percent';
if (formatted('currency', 'accounting')) return 'currency';
if (
every(
(cell) =>
(cell.format === 'date' && numeric(cell)) ||
!!cellDateMention(cell.value)
)
)
return filled.some((cell) => /h/i.test(cell.numberFormat ?? ''))
? 'datetime'
: 'date';
if (formatted('time')) return 'time';
if (every(numeric)) return 'number';
if (
every(
(cell) =>
!cell.value.startsWith('=') && checkboxChecked(cell.value) === undefined
)
)
return 'text';
}
/** The style of a cell, without its value. */
function cellStyle(cell: SpreadsheetCell | undefined): SpreadsheetCellStyle {
if (!cell) return {};
const { value: _value, ...style } = cell;
return style;
}
/** The formula every data cell of a column holds, if they share one. */
function calculatedFormula(
table: SheetTable,
cells: SpreadsheetCells,
column: number
): string | undefined {
const { first, last } = tableDataRows(table);
let formula: string | undefined;
for (let row = first; row <= last; row++) {
const cell = cells[formatCellAddress(row, column)];
if (!cell?.value.startsWith('=') || cell.format === 'text') return;
if (formula !== undefined && cell.value !== formula) return;
formula = cell.value;
}
return formula;
}
/** `rule` with the cell at `row` and `column` added to its range. */
function extendedRule(
rule: DataValidation,
row: number,
column: number
): DataValidation {
const areas = [
...sqrefBounds(rule.range),
{ top: row, bottom: row, left: column, right: column },
].sort((a, b) => a.left - b.left || a.right - b.right || a.top - b.top);
const merged: CellBounds[] = [];
for (const area of areas) {
const previous = merged.at(-1);
if (
previous &&
previous.left === area.left &&
previous.right === area.right &&
area.top <= previous.bottom + 1
)
previous.bottom = Math.max(previous.bottom, area.bottom);
else merged.push({ ...area });
}
return { ...rule, range: boundsSqref(merged) };
}
/**
* Cells and rules a row added to the bottom of a table takes from the row
* above, as Excel and Google Sheets fill a table's new row: each cell's
* formatting and list, and the formula of a calculated column, whose data
* cells all hold one formula. `edits` are the cells being written to the
* new row, which keep their own values.
*/
export function newTableRow(options: {
metadata: WorkbookSheetMetadata;
table: SheetTable;
cells: SpreadsheetCells;
row: number;
edits: SpreadsheetCellEdits;
}): { edits: SpreadsheetCellEdits; validations?: DataValidation[] } {
const { table, cells, row } = options;
const bounds = tableBounds(table);
const edits: SpreadsheetCellEdits = {};
let validations = options.metadata.validations;
let rulesChanged = false;
for (let column = bounds.left; column <= bounds.right; column++) {
const address = formatCellAddress(row, column);
const above = cells[formatCellAddress(row - 1, column)];
const edit = options.edits[address];
const style = cells[address] ? {} : cellStyle(above);
const formula =
edit?.value === undefined &&
!cells[address]?.value &&
calculatedFormula(
{
...table,
range: `${formatCellAddress(bounds.top, bounds.left)}:${formatCellAddress(row - 1, bounds.right)}`,
},
cells,
column
);
const next = { ...style, ...(formula && { value: formula }), ...edit };
if (Object.keys(next).length) edits[address] = next;
const rule = validations?.find((item) =>
sqrefContains(item.range, row - 1, column)
);
if (
rule &&
!validations?.some((item) => sqrefContains(item.range, row, column))
) {
validations = validations?.map((item) =>
item === rule ? extendedRule(item, row, column) : item
);
rulesChanged = true;
}
}
return { edits, ...(rulesChanged && { validations }) };
}
/**
* Typing a formula that refers to its own row, such as `=[@Qty]*[@Price]`,
* into an empty column of a table fills the column with it, as Excel's
* calculated columns do. Returns the edits for the column's other cells.
*/
export function calculatedColumnEdits(
tables: SheetTable[] | undefined,
cells: SpreadsheetCells,
edits: SpreadsheetCellEdits
): SpreadsheetCellEdits | undefined {
const entries = Object.entries(edits);
if (!tables?.length || entries.length !== 1) return;
const [address, edit] = entries[0];
const value = edit?.value;
if (!value?.startsWith('=') || !hasThisRowReference(value)) return;
const position = parseCellAddress(address);
if (!position) return;
const table = tables.find((item) => {
const bounds = tableBounds(item);
const { first, last } = tableDataRows(item);
return (
position.row >= first &&
position.row <= last &&
position.column >= bounds.left &&
position.column <= bounds.right
);
});
if (!table) return;
const { first, last } = tableDataRows(table);
if (first === last) return;
for (let row = first; row <= last; row++)
if (
row !== position.row &&
cells[formatCellAddress(row, position.column)]?.value
)
return;
const fill: SpreadsheetCellEdits = {};
for (let row = first; row <= last; row++)
if (row !== position.row)
fill[formatCellAddress(row, position.column)] = { value };
return fill;
}
/** Edits with `more` written over them, cell by cell. */
function mergedEdits(
edits: SpreadsheetCellEdits,
more: SpreadsheetCellEdits
): SpreadsheetCellEdits {
const result = { ...edits };
for (const [address, edit] of Object.entries(more))
result[address] = edit && { ...result[address], ...edit };
return result;
}
/**
* Convert a range to a table whose columns take the types their values
* suggest, or the `columnTypes` given by column name.
*/
export function createTypedTable(
options: Parameters<typeof createTable>[0] & {
columnTypes?: Record<string, TableColumnType>;
}
): TableChange {
const change = createTable(options);
let edits = change.edits;
const cells = () => withEdits(options.cells, edits);
const table = findTable(change.metadata, options.name);
const detected = detectColumnTypes(
table,
cells(),
change.metadata.validations
);
let metadata = Object.keys(detected).length
? replaceTable(change.metadata, table.name, (current) => ({
...current,
columnTypes: detected,
}))
: change.metadata;
const names = tableColumnNames(table, cells());
const { left } = tableBounds(table);
for (const [column, type] of Object.entries(options.columnTypes ?? {})) {
const offset = names.findIndex(
(name) => name.trim().toLowerCase() === column.trim().toLowerCase()
);
if (offset < 0)
throw new Error(
`The table has no column “${column}”. Its columns are ${names.join(', ')}.`
);
const current = cells();
const typed = setTableColumnType({
metadata,
cells: current,
name: table.name,
column: left + offset,
type,
display: (row, at) => {
const address = formatCellAddress(row, at);
return (
options.display?.(address) ??
cellPlainText(current[address]?.value ?? '')
);
},
});
metadata = typed.metadata;
edits = mergedEdits(edits, typed.edits);
}
return { metadata, edits };
}