Skip to content

armgit / macro

Public Imported from GitHub
0

Macro is a unified workspace for teams: email, chat, docs, tasks, agents, calls, and CRM — @-linked together with shared AI memory.

24.1 KB
Raw Blame
import type { LoroDoc } from 'loro-crdt';
import type {
  SpreadsheetCalculateRequest,
  SpreadsheetCalculateResponse,
  SpreadsheetEditRequest,
  SpreadsheetEditResponse,
  SpreadsheetOperation,
  SpreadsheetReadCell,
  SpreadsheetReadRequest,
  SpreadsheetReadResponse,
  SpreadsheetReadValidation,
  SpreadsheetSheetSummary,
  SpreadsheetTableSummary,
  SpreadsheetValue,
} from './ai-types';
import type {
  CalculatedCell,
  SpreadsheetCalculator,
  WorkbookCalculation,
} from './calculation';
import { fillCopies } from './cell-copy';
import { insertCheckboxEdits } from './checkboxes';
import {
  dropdownRule,
  literalListItems,
  overlappingValidations,
  replaceValidations,
  validationReference,
} from './data-validation';
import { validateSpreadsheetDocument } from './document-validation';
import {
  type CellSelection,
  selectionAddresses,
  selectionBounds,
} from './grid-selection';
import { qualifyScratchFormula } from './scratch-formula';
import { tableColumnNames } from './sheet-tables';
import {
  appendSpreadsheetRows,
  formatCellAddress,
  isSpreadsheetCellStyle,
  MAX_COLUMN_WIDTH,
  MIN_COLUMN_WIDTH,
  parseCellAddress,
  parseColumnName,
  resizeSpreadsheetColumn,
  SPREADSHEET_DEFAULT_STYLE,
  SPREADSHEET_MAX_CELL_LENGTH,
  SPREADSHEET_MAX_ROWS,
  type SpreadsheetCell,
  type SpreadsheetCellEdits,
  type SpreadsheetCellStyle,
  writeSpreadsheetCells,
} from './spreadsheet-document';
import {
  addSpreadsheetSheet,
  createSpreadsheetTable,
  deleteSpreadsheetSheet,
  duplicateSpreadsheetSheet,
  readSpreadsheetWorkbook,
  renameSpreadsheetSheet,
  type SpreadsheetWorkbookSheet,
  setSpreadsheetValidations,
} from './workbook-document';

const MAX_READ_VALIDATIONS = 50;

export const SPREADSHEET_AI_LIMITS = {
  readCells: 500,
  formulas: 20,
  operations: 25,
  editCells: 2_000,
  responseBytes: 100_000,
} as const;

/** Strict native shape check: never reinterpret markdown as a blank sheet. */
export function assertSpreadsheetDocument(doc: LoroDoc): void {
  if (doc.getMap('spreadsheetMeta').get('formatVersion') !== 1)
    throw new Error('This is not a supported native Macro spreadsheet.');
}

function resolveSheet(
  workbook: SpreadsheetWorkbookSheet[],
  reference?: string
) {
  if (reference === undefined) return workbook[0];
  const sheet =
    workbook.find((item) => item.id === reference) ??
    workbook.find(
      (item) => item.name.toLowerCase() === reference.toLowerCase()
    );
  if (!sheet)
    throw new Error(
      `Sheet “${reference}” does not exist. Read the workbook to get its current sheet IDs.`
    );
  return sheet;
}

function address(value: string) {
  const normalized = value.trim().toUpperCase();
  const position = parseCellAddress(normalized);
  if (!position)
    throw new Error(
      `Invalid cell address “${value}”. Use A1-style addresses (columns A–XFD, rows 1–${SPREADSHEET_MAX_ROWS}) without a sheet prefix.`
    );
  return { normalized, position };
}

export function parseSpreadsheetRange(value: string): CellSelection {
  const parts = value.split(':');
  if (parts.length > 2 || !parts[0])
    throw new Error(`Invalid range “${value}”. Use A1 or A1:B10.`);
  const anchor = address(parts[0]).position;
  const focus = address(parts[1] ?? parts[0]).position;
  return { anchor, focus };
}

function rangeName(selection: CellSelection) {
  const bounds = selectionBounds(selection);
  const first = formatCellAddress(bounds.top, bounds.left);
  const last = formatCellAddress(bounds.bottom, bounds.right);
  return first === last ? first : `${first}:${last}`;
}

function checkedRange(value: string, sheet: SpreadsheetWorkbookSheet) {
  const selection = parseSpreadsheetRange(value);
  if (selectionBounds(selection).bottom >= sheet.layout.rowCount)
    throw new Error(
      `Range ${value} exceeds ${sheet.name}'s ${sheet.layout.rowCount} rows. Append rows first.`
    );
  return selection;
}

function calculate(
  workbook: SpreadsheetWorkbookSheet[],
  calculator: SpreadsheetCalculator
) {
  return calculator.calculateWorkbook(
    workbook.map((sheet) => ({ ...sheet, rowCount: sheet.layout.rowCount })),
    { includeTypes: true }
  );
}

function typedValue(result?: CalculatedCell): SpreadsheetValue {
  if (!result) return { type: 'blank', value: null, display: '' };
  return {
    type:
      result.type ??
      (result.error
        ? 'error'
        : result.number === undefined
          ? 'text'
          : 'number'),
    value:
      result.value ?? result.number ?? (result.error ? null : result.display),
    display: result.display,
    ...(result.error && { error: result.error }),
  };
}

function usedRange(
  sheet: SpreadsheetWorkbookSheet,
  result: Record<string, CalculatedCell>
) {
  // Large sheets exceed the argument limit of Math.min(...positions).
  let bounds: CellSelection | undefined;
  const include = (key: string) => {
    const position = parseCellAddress(key);
    if (!position) return;
    if (!bounds) {
      bounds = { anchor: { ...position }, focus: { ...position } };
      return;
    }
    bounds.anchor.row = Math.min(bounds.anchor.row, position.row);
    bounds.anchor.column = Math.min(bounds.anchor.column, position.column);
    bounds.focus.row = Math.max(bounds.focus.row, position.row);
    bounds.focus.column = Math.max(bounds.focus.column, position.column);
  };
  for (const [key, cell] of Object.entries(sheet.cells))
    if (cell.value !== '') include(key);
  for (const key of Object.keys(result)) include(key);
  return bounds ? rangeName(bounds) : null;
}

function tableSummaries(
  sheet: SpreadsheetWorkbookSheet
): SpreadsheetTableSummary[] {
  return (sheet.metadata?.tables ?? []).map((table) => {
    const columns = tableColumnNames(table, sheet.cells);
    const filteredColumns = Object.keys(table.filters ?? {}).flatMap(
      (offset) => columns[Number(offset)] ?? []
    );
    const columnTypes = Object.entries(table.columnTypes ?? {})
      .sort(([a], [b]) => Number(a) - Number(b))
      .flatMap(([offset, type]) => {
        const column = columns[Number(offset)];
        return column === undefined ? [] : [{ column, type }];
      });
    return {
      name: table.name,
      range: table.range,
      columns,
      totalsRow: table.totalsRow,
      ...(filteredColumns.length && { filteredColumns }),
      ...(columnTypes.length && { columnTypes }),
    };
  });
}

function summaries(
  workbook: SpreadsheetWorkbookSheet[],
  results: WorkbookCalculation
): SpreadsheetSheetSummary[] {
  return workbook.map((sheet) => {
    const tables = tableSummaries(sheet);
    return {
      id: sheet.id,
      name: sheet.name,
      rowCount: sheet.layout.rowCount,
      columnCount: sheet.layout.columnCount,
      usedRange: usedRange(sheet, results[sheet.id] ?? {}),
      populatedCells: Object.values(sheet.cells).filter(
        (cell) => cell.value !== ''
      ).length,
      formulaCells: Object.values(sheet.cells).filter(
        (cell) => cell.format !== 'text' && cell.value.startsWith('=')
      ).length,
      errorCells: Object.values(results[sheet.id] ?? {}).filter(
        (cell) => cell.error
      ).length,
      ...(tables.length && { tables }),
    };
  });
}

function readCell(
  key: string,
  cell: SpreadsheetCell | undefined,
  result: CalculatedCell | undefined,
  includeStyles: boolean
): SpreadsheetReadCell {
  const source = cell?.value ?? '';
  const { value: _value, ...style } = cell ?? { value: '' };
  return {
    address: key,
    source,
    ...typedValue(result),
    ...(source.startsWith('=') &&
      cell?.format !== 'text' && { formula: source }),
    ...(includeStyles && { style }),
  };
}

function readValidations(
  sheet: SpreadsheetWorkbookSheet,
  selection: CellSelection
): SpreadsheetReadValidation[] {
  return overlappingValidations(
    sheet.metadata?.validations,
    rangeName(selection)
  )
    .slice(0, MAX_READ_VALIDATIONS)
    .map((rule) => {
      const [first] = rule.formulas ?? [];
      const items =
        rule.type === 'list' && first !== undefined
          ? literalListItems(first)
          : undefined;
      return {
        range: rule.range,
        type: rule.type,
        ...(items
          ? { items }
          : rule.type === 'list' && first !== undefined
            ? { source: first }
            : rule.formulas && { formulas: rule.formulas }),
        ...(rule.operator && { operator: rule.operator }),
        ...(rule.type === 'list' && { dropdown: rule.dropdown !== false }),
        rejectInvalid:
          !!rule.showError && (rule.errorStyle ?? 'stop') === 'stop',
        ...(rule.colors && { colors: rule.colors }),
      };
    });
}

export function readSpreadsheetForAi(
  doc: LoroDoc,
  revision: string,
  request: SpreadsheetReadRequest,
  calculator: SpreadsheetCalculator
): SpreadsheetReadResponse {
  assertSpreadsheetDocument(doc);
  const workbook = readSpreadsheetWorkbook(doc);
  const results = calculate(workbook, calculator);
  const sheets = summaries(workbook, results);
  const sheet = resolveSheet(workbook, request.sheetId);
  const requested = request.ranges?.length
    ? request.ranges
    : [sheets.find((item) => item.id === sheet.id)?.usedRange ?? 'A1'];
  if (requested.length > 20)
    throw new Error('Read at most 20 ranges at a time.');
  let remaining = SPREADSHEET_AI_LIMITS.readCells;
  let bytes = SPREADSHEET_AI_LIMITS.responseBytes;
  const ranges = requested.map((range) => {
    const selection = checkedRange(range, sheet);
    const cells: SpreadsheetReadCell[] = [];
    let truncated = false;
    for (const key of selectionAddresses(selection)) {
      const cell = sheet.cells[key];
      const result = results[sheet.id]?.[key];
      // Sparse output; style-only cells are relevant only when requested.
      if (!cell?.value && !result && !(request.includeStyles && cell)) continue;
      const item = readCell(key, cell, result, request.includeStyles ?? false);
      const size = new TextEncoder().encode(JSON.stringify(item)).length;
      if (!remaining || size > bytes) {
        truncated = true;
        continue;
      }
      cells.push(item);
      remaining--;
      bytes -= size;
    }
    const validations = readValidations(sheet, selection);
    return {
      sheetId: sheet.id,
      sheetName: sheet.name,
      range: rangeName(selection),
      cells,
      ...(validations.length && { validations }),
      truncated,
    };
  });
  return {
    action: 'read',
    revision,
    sheets,
    ranges,
    warnings: ranges.some((range) => range.truncated)
      ? [
          'Results were limited to 500 cells or 100,000 bytes. Read smaller ranges for the remaining cells.',
        ]
      : [],
  };
}

function validateInputs(
  cells: { address: string; value: string }[],
  sheet: SpreadsheetWorkbookSheet
) {
  const edits: SpreadsheetCellEdits = {};
  for (const cell of cells) {
    const key = address(cell.address);
    if (key.position.row >= sheet.layout.rowCount)
      throw new Error(
        `Cell ${key.normalized} exceeds ${sheet.name}'s ${sheet.layout.rowCount} rows. Append rows first.`
      );
    if (cell.value.length > SPREADSHEET_MAX_CELL_LENGTH)
      throw new Error(`Cell ${key.normalized} exceeds 10,000 characters.`);
    if (key.normalized in edits)
      throw new Error(
        `Cell ${key.normalized} is specified twice in one operation.`
      );
    edits[key.normalized] = { value: cell.value };
  }
  return edits;
}

export function calculateSpreadsheetForAi(
  doc: LoroDoc,
  revision: string,
  request: SpreadsheetCalculateRequest,
  calculator: SpreadsheetCalculator
): SpreadsheetCalculateResponse {
  assertSpreadsheetDocument(doc);
  if (
    !request.formulas.length ||
    request.formulas.length > SPREADSHEET_AI_LIMITS.formulas
  )
    throw new Error('Calculate between 1 and 20 formulas at a time.');
  const workbook = readSpreadsheetWorkbook(doc);
  const sheet = resolveSheet(workbook, request.sheetId);
  let edited = 0;
  for (const override of request.overrides ?? []) {
    const target = resolveSheet(workbook, override.sheetId);
    edited += override.cells.length;
    if (edited > SPREADSHEET_AI_LIMITS.editCells)
      throw new Error('Override at most 2,000 cells at a time.');
    const edits = validateInputs(override.cells, target);
    for (const [key, edit] of Object.entries(edits))
      target.cells[key] = { ...target.cells[key], value: edit?.value ?? '' };
  }
  // Each formula gets an independent private sheet. There is no source-cell
  // collision, no result-to-result reference, and no accidental circular SUM.
  const scratchSheets = request.formulas.map(({ formula }, index) => {
    if (
      !formula.startsWith('=') ||
      formula.length > SPREADSHEET_MAX_CELL_LENGTH
    )
      throw new Error(
        'Scratch formulas must start with = and contain at most 10,000 characters.'
      );
    let name = `__MacroScratch_${index}`;
    while (
      workbook.some((item) => item.name.toLowerCase() === name.toLowerCase())
    )
      name += '_';
    const scratchId = `__macro_scratch_${crypto.randomUUID()}`;
    const scratch: SpreadsheetWorkbookSheet = {
      id: scratchId,
      name,
      cells: { A1: { value: qualifyScratchFormula(formula, sheet.name) } },
      layout: { rowCount: 200, columnCount: 26, columnWidths: {} },
      metadata: {
        definedNames: sheet.metadata?.definedNames?.filter(
          (entry) => entry.local
        ),
      },
    };
    return scratch;
  });
  const calculated = calculate([...workbook, ...scratchSheets], calculator);
  const results = request.formulas.map(({ label, formula }, index) => ({
    ...(label !== undefined && { label }),
    formula,
    ...typedValue(calculated[scratchSheets[index].id]?.A1),
  }));
  if (
    new TextEncoder().encode(JSON.stringify(results)).length >
    SPREADSHEET_AI_LIMITS.responseBytes
  )
    throw new Error(
      'Scratch results exceed 100,000 bytes. Calculate fewer formulas or return shorter results.'
    );
  return {
    action: 'calculate',
    revision,
    results,
    warnings: [
      'Scratch formulas return their top-left value and never change the workbook. Unqualified references use the selected sheet; INDIRECT is not supported in scratch calculations.',
    ],
  };
}

function knownStyle(style: SpreadsheetCellStyle) {
  if (
    !isSpreadsheetCellStyle(style) ||
    Object.keys(style).some(
      (key) => !Object.hasOwn(SPREADSHEET_DEFAULT_STYLE, key)
    )
  )
    throw new Error(
      'Invalid cell formatting. Use the supported spreadsheet style fields.'
    );
}

function operationRange(operation: SpreadsheetOperation): string | undefined {
  if ('range' in operation) return operation.range;
  if ('targetRange' in operation) return operation.targetRange;
  return undefined;
}

/** All mutations happen on a disposable fork; a failed batch changes nothing. */
export function prepareSpreadsheetEdit(
  doc: LoroDoc,
  request: SpreadsheetEditRequest,
  calculator: SpreadsheetCalculator,
  peerId?: bigint
) {
  assertSpreadsheetDocument(doc);
  if (
    !request.operations.length ||
    request.operations.length > SPREADSHEET_AI_LIMITS.operations
  )
    throw new Error('Edit between 1 and 25 operations at a time.');
  const fork = doc.fork();
  if (peerId !== undefined) fork.setPeerId(peerId);
  const before = doc.version();
  const changes: SpreadsheetEditResponse['changes'] = [];
  let edited = 0;
  const count = (amount: number) => {
    edited += amount;
    if (edited > SPREADSHEET_AI_LIMITS.editCells)
      throw new Error(
        'A batch may edit at most 2,000 cells. Split the change into smaller batches.'
      );
  };
  try {
    for (const operation of request.operations) {
      const workbook = readSpreadsheetWorkbook(fork);
      let sheet =
        'sheetId' in operation
          ? resolveSheet(workbook, operation.sheetId)
          : undefined;
      let summary: string;
      if (operation.type === 'add_sheet') {
        const id = addSpreadsheetSheet(fork, operation.name);
        sheet = resolveSheet(readSpreadsheetWorkbook(fork), id);
        summary = `Added sheet “${sheet.name}”.`;
      } else {
        if (!sheet) throw new Error('This operation requires a sheet.');
        switch (operation.type) {
          case 'set_cells': {
            count(operation.cells.length);
            writeSpreadsheetCells(
              fork,
              validateInputs(operation.cells, sheet),
              sheet.id
            );
            summary = `Set ${operation.cells.length} cell(s) in “${sheet.name}”.`;
            break;
          }
          case 'format_cells': {
            knownStyle(operation.style);
            const addresses = selectionAddresses(
              checkedRange(operation.range, sheet)
            );
            count(addresses.length);
            writeSpreadsheetCells(
              fork,
              Object.fromEntries(
                addresses.map((key) => [key, operation.style])
              ),
              sheet.id
            );
            summary = `Formatted ${addresses.length} cell(s) in “${sheet.name}”.`;
            break;
          }
          case 'clear_cells': {
            const addresses = selectionAddresses(
              checkedRange(operation.range, sheet)
            );
            count(addresses.length);
            writeSpreadsheetCells(
              fork,
              Object.fromEntries(
                addresses.map((key) => [
                  key,
                  operation.clearFormatting ? null : { value: '' },
                ])
              ),
              sheet.id
            );
            summary = `Cleared ${addresses.length} cell(s) in “${sheet.name}”.`;
            break;
          }
          case 'fill_cells': {
            const source = checkedRange(operation.sourceRange, sheet);
            const target = checkedRange(operation.targetRange, sheet);
            const copies = fillCopies(sheet.cells, source, target);
            count(copies.length);
            const edits = calculator.copy(copies, {
              sheetNames: workbook.map((item) => item.name),
              activeSheet: workbook.findIndex((item) => item.id === sheet?.id),
            });
            writeSpreadsheetCells(fork, edits, sheet.id);
            summary = `Filled ${copies.length} cell(s) from ${operation.sourceRange} in “${sheet.name}”.`;
            break;
          }
          case 'rename_sheet':
            renameSpreadsheetSheet(fork, sheet.id, operation.name);
            summary = `Renamed “${sheet.name}” to “${operation.name}”.`;
            break;
          case 'duplicate_sheet': {
            count(Object.keys(sheet.cells).length);
            const id = duplicateSpreadsheetSheet(fork, sheet.id);
            if (operation.name !== undefined)
              renameSpreadsheetSheet(fork, id, operation.name);
            sheet = resolveSheet(readSpreadsheetWorkbook(fork), id);
            summary = `Duplicated sheet as “${sheet.name}”.`;
            break;
          }
          case 'delete_sheet':
            count(Object.keys(sheet.cells).length);
            deleteSpreadsheetSheet(fork, sheet.id);
            summary = `Deleted sheet “${sheet.name}”.`;
            break;
          case 'append_rows':
            if (
              !Number.isInteger(operation.count) ||
              operation.count < 1 ||
              sheet.layout.rowCount + operation.count > SPREADSHEET_MAX_ROWS
            )
              throw new Error(
                `Append a positive row count without exceeding ${SPREADSHEET_MAX_ROWS} rows.`
              );
            appendSpreadsheetRows(fork, operation.count, sheet.id);
            summary = `Added ${operation.count} rows to “${sheet.name}”.`;
            break;
          case 'resize_columns':
            if (!operation.columns.length || operation.columns.length > 100)
              throw new Error('Resize between 1 and 100 columns.');
            for (const column of operation.columns) {
              const index = parseColumnName(column.column.toUpperCase());
              if (
                index === undefined ||
                !Number.isInteger(column.width) ||
                column.width < MIN_COLUMN_WIDTH ||
                column.width > MAX_COLUMN_WIDTH
              )
                throw new Error(
                  `Columns must be A–XFD and widths must be ${MIN_COLUMN_WIDTH}–${MAX_COLUMN_WIDTH} pixels.`
                );
              resizeSpreadsheetColumn(fork, index, column.width, sheet.id);
            }
            summary = `Resized ${operation.columns.length} column(s) in “${sheet.name}”.`;
            break;
          case 'set_dropdown': {
            const range = rangeName(checkedRange(operation.range, sheet));
            if (
              (operation.items === undefined) ===
              (operation.source === undefined)
            )
              throw new Error(
                'A dropdown needs exactly one of items (typed choices) or source (a range of cells holding the choices).'
              );
            const sourceSheet =
              operation.source === undefined
                ? undefined
                : validationReference(operation.source)?.sheet;
            if (sourceSheet !== undefined) resolveSheet(workbook, sourceSheet);
            const rule = dropdownRule({
              ...(operation.items === undefined
                ? { range: operation.source ?? '' }
                : { items: operation.items }),
              rejectInvalid: operation.rejectInvalid,
              colors: operation.colors,
            });
            setSpreadsheetValidations(
              fork,
              sheet.id,
              replaceValidations(sheet.metadata?.validations, range, rule)
            );
            summary = `Added a dropdown to ${range} in “${sheet.name}”.`;
            break;
          }
          case 'clear_validation': {
            const range = rangeName(checkedRange(operation.range, sheet));
            setSpreadsheetValidations(
              fork,
              sheet.id,
              replaceValidations(sheet.metadata?.validations, range)
            );
            summary = `Removed data validation from ${range} in “${sheet.name}”.`;
            break;
          }
          case 'insert_checkboxes': {
            const selection = checkedRange(operation.range, sheet);
            const edits = insertCheckboxEdits(sheet.cells, selection);
            count(Object.keys(edits).length);
            writeSpreadsheetCells(fork, edits, sheet.id);
            summary = `Added checkboxes to ${rangeName(selection)} in “${sheet.name}”.`;
            break;
          }
          case 'create_table': {
            const selection = checkedRange(operation.range, sheet);
            const range = rangeName(selection);
            const { top, bottom, left, right } = selectionBounds(selection);
            // A conversion writes the header and total rows, and the data
            // cells of the columns it types.
            count(
              (right - left + 1) * (operation.totalsRow ? 2 : 1) +
                (operation.columnTypes?.length ?? 0) * (bottom - top)
            );
            const name = createSpreadsheetTable(fork, sheet.id, {
              range,
              name: operation.name,
              totalsRow: operation.totalsRow,
              columnTypes: Object.fromEntries(
                (operation.columnTypes ?? []).map(({ column, type }) => [
                  column,
                  type,
                ])
              ),
            });
            summary = `Converted ${range} in “${sheet.name}” to the table “${name}”.`;
            break;
          }
        }
      }
      const range = operationRange(operation);
      changes.push({
        type: operation.type,
        sheetId: sheet.id,
        summary,
        ...(range && { range }),
      });
    }
    fork.commit({ origin: 'spreadsheet-ai-edit' });
    validateSpreadsheetDocument(fork);
    const workbook = readSpreadsheetWorkbook(fork);
    const results = calculate(workbook, calculator);
    const sheets = summaries(workbook, results);
    const errors = sheets.reduce((sum, item) => sum + item.errorCells, 0);
    return {
      update: fork.export({ mode: 'update', from: before }),
      changes,
      sheets,
      warnings: errors
        ? [
            `The resulting workbook contains ${errors} formula error(s). Read the affected ranges to inspect them.`,
          ]
        : [],
    };
  } finally {
    fork.free();
  }
}