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 { type SheetDrawing, validDrawings } from './sheet-drawings';
import {
type ConditionalFormat,
type ConditionalStyle,
type DataValidation,
MAX_SHEET_RULES,
validConditionalFormat,
validConditionalStyle,
validDataValidation,
validNotes,
} from './sheet-rules';
import {
MAX_SHEET_TABLES,
type SheetTable,
validSheetTable,
} from './sheet-tables';
import {
parseCellAddress,
SPREADSHEET_MAX_COLUMNS,
SPREADSHEET_MAX_ROWS,
} from './spreadsheet-document';
/** Excel layout and definitions retained independently from editable cell maps. */
export type WorkbookSheetMetadata = {
merges?: string[];
rowHeights?: Record<number, number>;
hiddenRows?: number[];
hiddenColumns?: number[];
hidden?: boolean;
freeze?: { rows: number; columns: number };
autoFilter?: string;
definedNames?: { name: string; formula: string; local?: boolean }[];
/** Legacy (Ctrl+Shift+Enter) array formulas: anchor address → fixed range. */
arrayFormulas?: Record<string, string>;
/** False when the sheet hides its gridlines (Excel's `showGridLines="0"`). */
gridlines?: boolean;
/** Sheet tab color as #RRGGBB. */
tabColor?: string;
/** The imported workbook's default font, for cells without their own. */
defaultFont?: { name: string; size: number };
/** Excel notes by cell address. */
notes?: Record<string, string>;
/** Excel data validation rules. */
validations?: DataValidation[];
/** Excel conditional formatting rules, highest priority first. */
conditionalFormats?: ConditionalFormat[];
/** Images and charts drawn over the sheet. */
drawings?: SheetDrawing[];
/** Excel pivot tables, kept so export writes them back. */
pivotTables?: SheetPivotTable[];
/** Named tables of the sheet's cells (Excel tables). */
tables?: SheetTable[];
};
/**
* An Excel pivot table, kept so export writes it back. Macro shows the
* table's last values as cells. Excel rebuilds a table over this workbook's
* cells when the exported file opens; one over another workbook or a data
* connection keeps the data Excel saved with it until Excel refreshes it.
*/
export type SheetPivotTable = {
/** The pivotTableDefinition part; export writes its cache and location. */
table: string;
/** The pivotCacheDefinition part; export writes its relationships. */
cache: string;
/** The cells the table covers on its sheet. */
location: string;
/** The cells it summarizes, such as `'Orders'!$A$1:$F$500`. */
source?: string;
/** The records Excel saved of a source Macro does not hold. */
records?: string;
/** The other workbook a cache reads, as Excel linked it. */
workbook?: string;
/** The data connection a cache reads: its `connection` element. */
connection?: string;
/** Custom number formats the parts use, by `numFmtId`. */
formats?: Record<string, string>;
/** The differential formats of its areas, by the `dxfId` the table uses. */
styles?: ConditionalStyle[];
};
export const MAX_SHEET_PIVOT_TABLES = 64;
export const MAX_PIVOT_PART_LENGTH = 300_000;
const MAX_PIVOT_LINK_LENGTH = 2_048;
const MAX_PIVOT_CONNECTION_LENGTH = 100_000;
function validPivotTable(value: unknown): boolean {
if (!value || typeof value !== 'object' || Array.isArray(value)) return false;
const pivot = value as Record<string, unknown>;
const formats = pivot.formats;
return (
Object.keys(pivot).every((key) =>
[
'table',
'cache',
'location',
'source',
'records',
'workbook',
'connection',
'formats',
'styles',
].includes(key)
) &&
typeof pivot.table === 'string' &&
pivot.table.length <= MAX_PIVOT_PART_LENGTH &&
pivot.table.includes('<pivotTableDefinition') &&
typeof pivot.cache === 'string' &&
pivot.cache.length <= MAX_PIVOT_PART_LENGTH &&
pivot.cache.includes('<pivotCacheDefinition') &&
validWorkbookRange(pivot.location) &&
(pivot.source === undefined ||
(typeof pivot.source === 'string' && pivot.source.length <= 1_000)) &&
(pivot.records === undefined ||
(typeof pivot.records === 'string' &&
pivot.records.length <= MAX_PIVOT_PART_LENGTH &&
pivot.records.includes('pivotCacheRecords'))) &&
(pivot.workbook === undefined ||
(typeof pivot.workbook === 'string' &&
pivot.workbook.length > 0 &&
pivot.workbook.length <= MAX_PIVOT_LINK_LENGTH)) &&
(pivot.connection === undefined ||
(typeof pivot.connection === 'string' &&
pivot.connection.length <= MAX_PIVOT_CONNECTION_LENGTH &&
/^<(?:[\w.-]+:)?connection\b/.test(pivot.connection))) &&
(pivot.styles === undefined ||
(Array.isArray(pivot.styles) &&
pivot.styles.length <= 200 &&
pivot.styles.every(validConditionalStyle))) &&
(formats === undefined ||
(!!formats &&
typeof formats === 'object' &&
!Array.isArray(formats) &&
Object.entries(formats).length <= 200 &&
Object.entries(formats).every(
([id, code]) =>
/^\d{1,5}$/.test(id) &&
typeof code === 'string' &&
code.length <= 255
)))
);
}
export function validWorkbookRange(range: unknown): range is string {
if (typeof range !== 'string') return false;
const parts = range.split(':');
const first = parseCellAddress(parts[0]);
const last = parseCellAddress(parts[1] ?? parts[0]);
return (
parts.length <= 2 &&
!!first &&
!!last &&
first.row <= last.row &&
first.column <= last.column
);
}
export function parseWorkbookMetadata(
encoded: unknown
): WorkbookSheetMetadata | undefined {
if (typeof encoded !== 'string' || encoded.length > 1_000_000) return;
try {
const value = JSON.parse(encoded);
if (!value || typeof value !== 'object' || Array.isArray(value)) return;
if (
Object.keys(value).some(
(key) =>
![
'merges',
'rowHeights',
'hiddenRows',
'hiddenColumns',
'hidden',
'freeze',
'autoFilter',
'definedNames',
'arrayFormulas',
'gridlines',
'tabColor',
'defaultFont',
'notes',
'validations',
'conditionalFormats',
'drawings',
'pivotTables',
'tables',
].includes(key)
)
)
return;
const indices = (values: unknown, limit: number) =>
values === undefined ||
(Array.isArray(values) &&
values.length <= limit &&
values.every((v) => Number.isInteger(v) && v >= 0 && v < limit));
if (
value.merges !== undefined &&
(!Array.isArray(value.merges) ||
value.merges.length > 100_000 ||
!value.merges.every(validWorkbookRange))
)
return;
if (
!indices(value.hiddenRows, SPREADSHEET_MAX_ROWS) ||
!indices(value.hiddenColumns, SPREADSHEET_MAX_COLUMNS)
)
return;
if (
value.rowHeights !== undefined &&
(!value.rowHeights ||
typeof value.rowHeights !== 'object' ||
Array.isArray(value.rowHeights) ||
!Object.entries(value.rowHeights).every(
([key, height]) =>
/^\d+$/.test(key) &&
+key < SPREADSHEET_MAX_ROWS &&
typeof height === 'number' &&
Number.isFinite(height) &&
height >= 0 &&
height <= 409.5
))
)
return;
if (value.hidden !== undefined && typeof value.hidden !== 'boolean') return;
if (value.gridlines !== undefined && typeof value.gridlines !== 'boolean')
return;
if (
value.defaultFont !== undefined &&
(!value.defaultFont ||
typeof value.defaultFont !== 'object' ||
Object.keys(value.defaultFont).some(
(key) => key !== 'name' && key !== 'size'
) ||
typeof value.defaultFont.name !== 'string' ||
!value.defaultFont.name ||
value.defaultFont.name.length > 128 ||
Array.from(value.defaultFont.name as string).some(
(character) => character.charCodeAt(0) < 32
) ||
!Number.isInteger(value.defaultFont.size) ||
value.defaultFont.size < 8 ||
value.defaultFont.size > 36)
)
return;
if (
value.tabColor !== undefined &&
(typeof value.tabColor !== 'string' ||
!/^#[0-9a-f]{6}$/i.test(value.tabColor))
)
return;
if (
value.freeze !== undefined &&
(!value.freeze ||
Object.keys(value.freeze).length !== 2 ||
!Number.isInteger(value.freeze.rows) ||
value.freeze.rows < 0 ||
value.freeze.rows > SPREADSHEET_MAX_ROWS ||
!Number.isInteger(value.freeze.columns) ||
value.freeze.columns < 0 ||
value.freeze.columns > SPREADSHEET_MAX_COLUMNS)
)
return;
if (value.autoFilter !== undefined && !validWorkbookRange(value.autoFilter))
return;
if (value.notes !== undefined && !validNotes(value.notes)) return;
if (value.drawings !== undefined && !validDrawings(value.drawings)) return;
if (
value.pivotTables !== undefined &&
!(
Array.isArray(value.pivotTables) &&
value.pivotTables.length <= MAX_SHEET_PIVOT_TABLES &&
value.pivotTables.every(validPivotTable)
)
)
return;
if (
value.tables !== undefined &&
!(
Array.isArray(value.tables) &&
value.tables.length <= MAX_SHEET_TABLES &&
value.tables.every(validSheetTable) &&
new Set(
value.tables.map((table: SheetTable) => table.name.toLowerCase())
).size === value.tables.length
)
)
return;
for (const [key, valid] of [
['validations', validDataValidation],
['conditionalFormats', validConditionalFormat],
] as const)
if (
value[key] !== undefined &&
(!Array.isArray(value[key]) ||
value[key].length > MAX_SHEET_RULES ||
!value[key].every(valid))
)
return;
if (
value.definedNames !== undefined &&
(!Array.isArray(value.definedNames) ||
value.definedNames.length > 10_000 ||
!value.definedNames.every((entry: unknown) => {
if (
!entry ||
typeof entry !== 'object' ||
Object.keys(entry).some(
(key) => !['name', 'formula', 'local'].includes(key)
)
)
return false;
const { name, formula, local } = entry as Record<string, unknown>;
return (
typeof name === 'string' &&
name.length > 0 &&
name.length <= 255 &&
typeof formula === 'string' &&
formula.length <= 10_000 &&
(local === undefined || typeof local === 'boolean')
);
}))
)
return;
if (
value.arrayFormulas !== undefined &&
(!value.arrayFormulas ||
typeof value.arrayFormulas !== 'object' ||
Array.isArray(value.arrayFormulas) ||
Object.keys(value.arrayFormulas).length > 100_000 ||
!Object.entries(value.arrayFormulas).every(
([anchor, range]) =>
!!parseCellAddress(anchor) &&
validWorkbookRange(range) &&
(range as string).split(':')[0] === anchor
))
)
return;
return value;
} catch {
return;
}
}
const SHEET_PREFIX = /^(?:\[0\]!)?(?:'((?:[^']|'')+)'|([^'!:\s()]+))!/;
/** A reference with its sheet renamed, when it names that sheet. */
function renamedReference(reference: string, from: string, to: string) {
const match = SHEET_PREFIX.exec(reference);
const sheet = match && (match[1]?.replace(/''/g, "'") ?? match[2]);
if (!match || sheet?.toLowerCase() !== from.toLowerCase()) return reference;
return `'${to.replaceAll("'", "''")}'!${reference.slice(match[0].length)}`;
}
/**
* Metadata whose charts and pivot tables follow a renamed sheet, or
* undefined when none refer to it. Formulas and names already block renames.
*/
export function renameSheetReferences(
metadata: WorkbookSheetMetadata | undefined,
from: string,
to: string
): WorkbookSheetMetadata | undefined {
if (!metadata) return;
const rename = (reference: string) => renamedReference(reference, from, to);
const escaped = (value: string) =>
value.replace(/&/g, '&').replace(/</g, '<');
const result: WorkbookSheetMetadata = {
...metadata,
...(metadata.drawings && {
drawings: metadata.drawings.map((drawing) => {
if (drawing.type === 'shape')
return {
...drawing,
shape: {
...drawing.shape,
parts: drawing.shape.parts.map((part) =>
part.text?.link
? {
...part,
text: { ...part.text, link: rename(part.text.link) },
}
: part
),
},
};
if (drawing.type !== 'chart') return drawing;
const { source } = drawing.chart;
return {
...drawing,
chart: {
...drawing.chart,
references: drawing.chart.references.map(rename),
// A pivot chart names its pivot table's sheet.
...(source && {
source: source.replace(
/(<(?:[\w.-]+:)?pivotSource>\s*<((?:[\w.-]+:)?name)>)(\[[^\]<]*\])?([^<]*)!([^<!]*)(<\/\2>)/,
(
whole,
open,
_name,
book = '',
sheet: string,
table,
close
) => {
const unquoted = sheet
.replace(/^'(.*)'$/, '$1')
.replace(/''/g, "'")
.replace(/'/g, "'")
.replace(/&/g, '&');
return unquoted.toLowerCase() === from.toLowerCase()
? `${open}${book}${escaped(/[^\w.]/.test(to) ? `'${to.replaceAll("'", "''")}'` : to)}!${table}${close}`
: whole;
}
),
}),
},
};
}),
}),
...(metadata.pivotTables && {
pivotTables: metadata.pivotTables.map((pivot) =>
pivot.source === undefined
? pivot
: { ...pivot, source: rename(pivot.source) }
),
}),
};
return JSON.stringify(result) === JSON.stringify(metadata)
? undefined
: result;
}