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.

30.3 KB
Raw Blame
import { getTokens, Model } from '@ironcalc/wasm';
import { type DrawingPoint, isChartLiteral } from './sheet-drawings';
import {
  boundsSqref,
  type CellBounds,
  sqrefBounds,
  subtractBounds,
} from './sheet-rules';
import type { SheetTable } from './sheet-tables';
import {
  columnName,
  formatCellAddress,
  parseCellAddress,
  SPREADSHEET_MAX_COLUMNS,
  SPREADSHEET_MAX_ROWS,
  type SpreadsheetCells,
} from './spreadsheet-document';
import { protectStructuredReferences } from './structured-references';
import type { SpreadsheetWorkbookSheet } from './workbook-document';
import type { WorkbookSheetMetadata } from './workbook-metadata';

export type AxisChange = {
  sheetId: string;
  axis: 'row' | 'column';
  index: number;
  count: number;
  kind: 'insert' | 'delete';
};

/** Trim partially deleted ranges before IronCalc turns a deleted endpoint into #REF. */
function prepareDeletion(
  formula: string,
  currentSheet: string,
  targetSheet: string,
  change: AxisChange
): string {
  if (change.kind !== 'delete') return formula;
  const characters = Array.from(formula);
  for (const { token, start, end } of getTokens(formula).reverse()) {
    if (typeof token !== 'object' || !('Range' in token)) continue;
    const range = token.Range;
    if (
      (range.sheet ?? currentSheet).toLowerCase() !== targetSheet.toLowerCase()
    )
      continue;
    const left = { ...range.left },
      right = { ...range.right };
    const referenceText = characters
      .slice(start, end)
      .join('')
      .split('!')
      .at(-1)!;
    const wholeRows = /^\$?\d+:\$?\d+$/.test(referenceText);
    const wholeColumns = /^\$?[A-Z]+:\$?[A-Z]+$/i.test(referenceText);
    if (
      (wholeRows && change.axis === 'column') ||
      (wholeColumns && change.axis === 'row')
    )
      continue;
    const low = Math.min(left[change.axis], right[change.axis]);
    const high = Math.max(left[change.axis], right[change.axis]);
    const first = change.index + 1,
      last = change.index + change.count;
    if (low >= first && high <= last) continue;
    let changed = false;
    for (const reference of [left, right]) {
      if (reference[change.axis] >= first && reference[change.axis] <= last) {
        reference[change.axis] =
          reference[change.axis] === low ? last + 1 : first - 1;
        changed = true;
      }
    }
    if (!changed) continue;
    const address = (ref: typeof left) => {
      let column = '';
      for (
        let index = ref.column;
        index > 0;
        index = Math.floor((index - 1) / 26)
      ) {
        column = String.fromCharCode(65 + ((index - 1) % 26)) + column;
      }
      if (wholeRows) return `${ref.absolute_row ? '$' : ''}${ref.row}`;
      if (wholeColumns) return `${ref.absolute_column ? '$' : ''}${column}`;
      const cell = `${column}${ref.row}`;
      return cell.replace(
        /^([A-Z]+)(\d+)$/,
        (_, column: string, row: string) =>
          `${ref.absolute_column ? '$' : ''}${column}${ref.absolute_row ? '$' : ''}${row}`
      );
    };
    const prefix = range.sheet ? `'${range.sheet.replaceAll("'", "''")}'!` : '';
    characters.splice(
      start,
      end - start,
      `${prefix}${address(left)}:${address(right)}`
    );
  }
  return characters.join('');
}

/**
 * Formulas kept outside cells, in a stable order: name definitions, the
 * formulas and threshold values of conditional formats and data validation,
 * then the ranges charts read.
 */
function sheetFormulas(metadata: WorkbookSheetMetadata | undefined) {
  const formulas = (metadata?.definedNames ?? []).map((name) => name.formula);
  for (const rule of [
    ...(metadata?.conditionalFormats ?? []),
    ...(metadata?.validations ?? []),
  ]) {
    formulas.push(...(rule.formulas ?? []));
    if ('thresholds' in rule)
      for (const threshold of rule.thresholds ?? [])
        if (threshold.value !== undefined) formulas.push(threshold.value);
  }
  for (const drawing of metadata?.drawings ?? [])
    if (drawing.type === 'chart')
      formulas.push(
        ...drawing.chart.references.filter(
          (reference) => !isChartLiteral(reference)
        )
      );
    else if (drawing.type === 'shape')
      for (const part of drawing.shape.parts)
        if (part.text?.link) formulas.push(part.text.link);
  for (const pivot of metadata?.pivotTables ?? [])
    if (pivot.source !== undefined) formulas.push(pivot.source);
  return formulas;
}

/** Metadata with its outside-cell formulas replaced, in `sheetFormulas` order. */
function withSheetFormulas(
  metadata: WorkbookSheetMetadata,
  formulas: string[]
): WorkbookSheetMetadata {
  let next = 0;
  const take = () => formulas[next++];
  const result = { ...metadata };
  if (metadata.definedNames)
    result.definedNames = metadata.definedNames.map((entry) => ({
      ...entry,
      formula: take(),
    }));
  if (metadata.conditionalFormats)
    result.conditionalFormats = metadata.conditionalFormats.map((rule) => ({
      ...rule,
      ...(rule.formulas && { formulas: rule.formulas.map(take) }),
      ...(rule.thresholds && {
        thresholds: rule.thresholds.map((threshold) =>
          threshold.value === undefined
            ? threshold
            : { ...threshold, value: take() }
        ),
      }),
    }));
  if (metadata.validations)
    result.validations = metadata.validations.map((rule) => ({
      ...rule,
      ...(rule.formulas && { formulas: rule.formulas.map(take) }),
    }));
  if (metadata.drawings)
    result.drawings = metadata.drawings.map((drawing) => {
      if (drawing.type === 'chart')
        return {
          ...drawing,
          chart: {
            ...drawing.chart,
            // Fixed values stay as they are.
            references: drawing.chart.references.map((reference) =>
              isChartLiteral(reference) ? reference : take()
            ),
          },
        };
      if (drawing.type !== 'shape') return drawing;
      return {
        ...drawing,
        shape: {
          ...drawing.shape,
          parts: drawing.shape.parts.map((part) => {
            if (!part.text?.link) return part;
            const link = take();
            // A shape whose cell is deleted keeps the text it last showed.
            if (!link.includes('#REF!'))
              return { ...part, text: { ...part.text, link } };
            const { link: _link, ...text } = part.text;
            return { ...part, text };
          }),
        },
      };
    });
  if (metadata.pivotTables) {
    // A pivot table whose source cells were deleted cannot be rebuilt; its
    // values stay in the cells.
    result.pivotTables = metadata.pivotTables.flatMap((pivot) => {
      if (pivot.source === undefined) return [pivot];
      const source = take();
      return source.includes('#REF!') ? [] : [{ ...pivot, source }];
    });
    if (!result.pivotTables.length) delete result.pivotTables;
  }
  return result;
}

/**
 * A table after rows or columns move: it grows when they are inserted inside
 * it (not directly below or right of it) and shrinks when some of its own
 * are deleted. Deleting its header row or all its columns removes it, and
 * deleting its total row removes that row. Filters follow their columns.
 */
function movedTable(
  table: SheetTable,
  change: AxisChange,
  move: (index: number) => number | undefined
): SheetTable | undefined {
  const [start, end] = table.range.split(':').map(parseCellAddress);
  if (!start || !end) return;
  const axis = change.axis;
  const first = start[axis];
  const last = end[axis];
  let low = move(first);
  let high = move(last);
  let totalsRow = table.totalsRow;
  if (axis === 'row') {
    if (low === undefined) return;
    if (totalsRow && high === undefined) totalsRow = false;
    high ??= change.index - 1;
    if (high - low < (totalsRow ? 2 : 1))
      throw new Error(
        `Keep at least one data row in the table “${table.name}”, or delete its header row too to remove it.`
      );
  } else {
    if (low === undefined && high === undefined) return;
    low ??= change.index;
    high ??= change.index - 1;
    if (high < low) return;
  }
  start[axis] = low;
  end[axis] = high;
  const limit = axis === 'row' ? SPREADSHEET_MAX_ROWS : SPREADSHEET_MAX_COLUMNS;
  if (end[axis] >= limit)
    throw new Error(
      `This change would move the table “${table.name}” outside the supported sheet size.`
    );
  const moved: SheetTable = {
    ...table,
    totalsRow,
    range: `${formatCellAddress(start.row, start.column)}:${formatCellAddress(end.row, end.column)}`,
  };
  const movedColumns = <T>(record: Record<string, T>) =>
    Object.fromEntries(
      Object.entries(record).flatMap(([offset, value]) => {
        if (axis === 'row') return [[offset, value]];
        const column = move(first + Number(offset));
        return column === undefined ? [] : [[String(column - low), value]];
      })
    );
  if (table.filters) {
    const filters = movedColumns(table.filters);
    if (Object.keys(filters).length) moved.filters = filters;
    else delete moved.filters;
  }
  if (table.columnTypes) {
    const columnTypes = movedColumns(table.columnTypes);
    if (Object.keys(columnTypes).length) moved.columnTypes = columnTypes;
    else delete moved.columnTypes;
  }
  return moved;
}

/** Keep cell identity, styles and all sheet references together during a structural edit. */
export function changeWorkbookAxis(
  sheets: SpreadsheetWorkbookSheet[],
  change: AxisChange
): SpreadsheetWorkbookSheet[] {
  const target = sheets.findIndex((sheet) => sheet.id === change.sheetId);
  const limit =
    change.axis === 'row' ? SPREADSHEET_MAX_ROWS : SPREADSHEET_MAX_COLUMNS;
  // Name definitions are moved through out-of-grid formulas below the last
  // row, in a column that a column deletion cannot remove.
  const helperColumn =
    change.axis === 'column' && change.kind === 'delete' && change.index === 0
      ? change.count
      : 0;
  if (
    target < 0 ||
    !Number.isInteger(change.index) ||
    !Number.isInteger(change.count) ||
    change.index < 0 ||
    change.count < 1 ||
    change.index + change.count > limit
  )
    throw new Error('Invalid row or column selection.');
  const move = (index: number): number | undefined => {
    if (index < change.index) return index;
    if (change.kind === 'insert') return index + change.count;
    return index < change.index + change.count
      ? undefined
      : index - change.count;
  };
  const movedRecord = (record: Record<number, number> | undefined) =>
    Object.fromEntries(
      Object.entries(record ?? {}).flatMap(([key, value]) => {
        const next = move(Number(key));
        return next === undefined || next >= limit ? [] : [[next, value]];
      })
    );
  const range = (value: string): string | undefined => {
    const [start, end = start] = value.split(':').map(parseCellAddress);
    if (!start || !end) throw new Error('Invalid sheet layout range.');
    const first = start[change.axis];
    const last = end[change.axis];
    if (
      change.kind === 'delete' &&
      first >= change.index &&
      last < change.index + change.count
    )
      return;
    start[change.axis] = move(first) ?? change.index;
    end[change.axis] = move(last) ?? change.index - 1;
    if (end[change.axis] >= limit)
      throw new Error(
        'This change would move a formatted range outside the supported sheet size.'
      );
    return `${formatCellAddress(start.row, start.column)}:${formatCellAddress(end.row, end.column)}`;
  };
  // Rule ranges are lists of cells and ranges; unlike formatted ranges they
  // are clipped at the sheet's edge rather than blocking an insertion.
  const ruleRange = (sqref: string): string | undefined => {
    const parts = sqref.split(/\s+/).flatMap((part) => {
      const [start, end = start] = part.split(':').map(parseCellAddress);
      if (!start || !end) return [];
      const first = move(start[change.axis]);
      const last = move(end[change.axis]);
      // A deletion removes the part only when it covers all of it.
      if (first === undefined && last === undefined) return [];
      start[change.axis] = first ?? change.index;
      end[change.axis] = Math.min(limit - 1, last ?? change.index - 1);
      if (start[change.axis] > end[change.axis]) return [];
      const from = formatCellAddress(start.row, start.column);
      const to = formatCellAddress(end.row, end.column);
      return [from === to ? from : `${from}:${to}`];
    });
    return parts.length ? parts.join(' ') : undefined;
  };
  const model = new Model('Sheet structure', 'en', 'UTC', 'en');
  const restorers = new Map<string, (text: string) => string>();
  try {
    model.pauseEvaluation();
    sheets.forEach((_, index) => {
      if (index) model.newSheet();
      model.renameSheet(index, `__macro_structure_${index}`);
    });
    sheets.forEach((sheet, index) => model.renameSheet(index, sheet.name));
    for (const [index, sheet] of sheets.entries()) {
      // A rule's formulas are relative to its first cell; references in an
      // out-of-grid cell move the same way.
      for (const [slot, formula] of sheetFormulas(sheet.metadata).entries())
        model.setUserInput(
          index,
          SPREADSHEET_MAX_ROWS + 1 + slot,
          helperColumn + 1,
          prepareDeletion(
            `=${formula.replace(/^=/, '')}`,
            sheet.name,
            sheets[target].name,
            change
          )
        );
      for (const name of sheet.metadata?.definedNames ?? []) {
        if (
          /^[=]?(?:[+-]?(?:\d+(?:\.\d*)?|\.\d+)(?:[eE][+-]?\d+)?|TRUE|FALSE|"(?:[^"\r\n]|"")*")$/i.test(
            name.formula
          )
        )
          continue;
        try {
          model.newDefinedName(
            name.name,
            name.local ? index : undefined,
            name.formula.replace(/^=/, '')
          );
        } catch {
          throw new Error(
            `Cannot move rows or columns while the named expression “${name.name}” uses an unsupported reference. No cells were changed.`
          );
        }
      }
      for (const [address, cell] of Object.entries(sheet.cells)) {
        const position = parseCellAddress(address)!;
        if (!cell.value.startsWith('=') || cell.format === 'text') continue;
        // Table references follow their table, which moves below.
        const references = protectStructuredReferences(cell.value);
        if (references.formula !== cell.value)
          restorers.set(`${index}:${address}`, references.restore);
        model.setUserInput(
          index,
          position.row + 1,
          position.column + 1,
          prepareDeletion(
            references.formula,
            sheet.name,
            sheets[target].name,
            change
          )
        );
      }
    }
    if (change.axis === 'row') {
      if (change.kind === 'insert')
        model.insertRows(target, change.index + 1, change.count);
      else model.deleteRows(target, change.index + 1, change.count);
    } else if (change.kind === 'insert')
      model.insertColumns(target, change.index + 1, change.count);
    else model.deleteColumns(target, change.index + 1, change.count);
    return sheets.map((sheet, index) => {
      const cells: SpreadsheetCells = {};
      for (const [address, cell] of Object.entries(sheet.cells)) {
        const position = parseCellAddress(address)!;
        if (index === target) {
          const next = move(position[change.axis]);
          if (next === undefined) continue;
          if (next >= limit)
            throw new Error(
              `This change would push cells past ${limit} ${change.axis}s. Clear the trailing cells first.`
            );
          position[change.axis] = next;
        }
        const restore = restorers.get(`${index}:${address}`);
        let value = cell.value;
        if (cell.value.startsWith('=') && cell.format !== 'text') {
          value = model.getCellContent(
            index,
            position.row + 1,
            position.column + 1
          );
          if (restore) value = restore(value);
        }
        cells[formatCellAddress(position.row, position.column)] = {
          ...cell,
          value,
        };
      }
      // IronCalc moves formula references but not its named-range definitions.
      // Read each definition from an out-of-grid formula after the same move.
      const metadata = withSheetFormulas(
        { ...sheet.metadata },
        sheetFormulas(sheet.metadata).map((_, slot) => {
          let row = SPREADSHEET_MAX_ROWS + slot;
          let column = helperColumn;
          if (index === target) {
            if (change.axis === 'row') row = move(row)!;
            else column = move(column)!;
          }
          return model
            .getCellContent(index, row + 1, column + 1)
            .replace(/^=/, '');
        })
      );
      let layout = sheet.layout;
      if (index === target) {
        if (metadata.merges)
          metadata.merges = metadata.merges.flatMap((value) => {
            const next = range(value);
            return next ? [next] : [];
          });
        if (metadata.autoFilter)
          metadata.autoFilter = range(metadata.autoFilter);
        if (metadata.notes)
          metadata.notes = Object.fromEntries(
            Object.entries(metadata.notes).flatMap(([address, note]) => {
              const position = parseCellAddress(address)!;
              const next = move(position[change.axis]);
              if (next === undefined || next >= limit) return [];
              position[change.axis] = next;
              return [[formatCellAddress(position.row, position.column), note]];
            })
          );
        if (metadata.conditionalFormats)
          metadata.conditionalFormats = metadata.conditionalFormats.flatMap(
            (rule) => {
              const next = ruleRange(rule.range);
              return next ? [{ ...rule, range: next }] : [];
            }
          );
        if (metadata.validations)
          metadata.validations = metadata.validations.flatMap((rule) => {
            const next = ruleRange(rule.range);
            return next ? [{ ...rule, range: next }] : [];
          });
        if (metadata.drawings) {
          // A drawing moves with its cells. One that stretches over cells
          // that are all deleted goes with them, as it would have no size.
          // A corner in deleted cells moves to where they were.
          const corner = (point: DrawingPoint) => {
            const index = move(point[change.axis]);
            return index === undefined
              ? {
                  ...point,
                  [change.axis]: change.index,
                  [change.axis === 'row' ? 'y' : 'x']: 0,
                }
              : { ...point, [change.axis]: Math.min(limit - 1, index) };
          };
          metadata.drawings = metadata.drawings.flatMap((drawing) => {
            const deleted = (point: DrawingPoint) =>
              move(point[change.axis]) === undefined;
            if (drawing.to && deleted(drawing.from) && deleted(drawing.to))
              return [];
            return [
              {
                ...drawing,
                from: corner(drawing.from),
                ...(drawing.to && { to: corner(drawing.to) }),
              },
            ];
          });
        }
        if (metadata.pivotTables) {
          metadata.pivotTables = metadata.pivotTables.flatMap((pivot) => {
            const location = ruleRange(pivot.location);
            return location ? [{ ...pivot, location }] : [];
          });
          if (!metadata.pivotTables.length) delete metadata.pivotTables;
        }
        if (metadata.tables) {
          metadata.tables = metadata.tables.flatMap(
            (table) => movedTable(table, change, move) ?? []
          );
          if (!metadata.tables.length) delete metadata.tables;
        }
        const hidden = change.axis === 'row' ? 'hiddenRows' : 'hiddenColumns';
        if (metadata[hidden])
          metadata[hidden] = metadata[hidden].flatMap((value) => {
            const next = move(value);
            return next === undefined || next >= limit ? [] : [next];
          });
        if (metadata.freeze) {
          const key = change.axis === 'row' ? 'rows' : 'columns';
          const edge = metadata.freeze[key];
          metadata.freeze = {
            ...metadata.freeze,
            [key]:
              edge === 0 || change.index >= edge
                ? edge
                : Math.min(limit, move(edge) ?? change.index),
          };
        }
        // Inserting extends the sheet; deleting keeps its size, as in Excel.
        let used = 0;
        for (const key of Object.keys(cells))
          used = Math.max(used, parseCellAddress(key)![change.axis] + 1);
        const grown = (count: number) =>
          Math.min(
            limit,
            Math.max(
              count + (change.kind === 'insert' ? change.count : 0),
              used
            )
          );
        if (change.axis === 'row') {
          if (metadata.rowHeights)
            metadata.rowHeights = movedRecord(metadata.rowHeights);
          layout = { ...layout, rowCount: grown(layout.rowCount) };
        } else
          layout = {
            ...layout,
            columnCount: grown(layout.columnCount),
            columnWidths: movedRecord(layout.columnWidths),
          };
      }
      return { ...sheet, cells, metadata, layout };
    });
  } finally {
    model.free();
  }
}

/** Cells dragged to another place on their sheet. */
export type RangeMove = {
  sheetId: string;
  /** The zero-based cells that move. */
  source: CellBounds;
  /** How many rows and columns they move by. */
  rows: number;
  columns: number;
};

const containsBounds = (outer: CellBounds, inner: CellBounds) =>
  inner.top >= outer.top &&
  inner.bottom <= outer.bottom &&
  inner.left >= outer.left &&
  inner.right <= outer.right;

const overlapBounds = (a: CellBounds, b: CellBounds): CellBounds[] => {
  const overlap = {
    top: Math.max(a.top, b.top),
    left: Math.max(a.left, b.left),
    bottom: Math.min(a.bottom, b.bottom),
    right: Math.min(a.right, b.right),
  };
  return overlap.top <= overlap.bottom && overlap.left <= overlap.right
    ? [overlap]
    : [];
};

/**
 * A formula whose references to the moved cells point at their new place, as
 * after Excel's cut and paste. A range follows only when all of it moves;
 * references to the cells the move replaces become #REF!.
 */
function movedReferences(
  formula: string,
  currentSheet: string,
  move: { sheet: string; source: CellBounds; destination: CellBounds },
  rows: number,
  columns: number
): string {
  const characters = Array.from(formula);
  let changed = false;
  for (const { token, start, end } of getTokens(formula).reverse()) {
    if (typeof token !== 'object') continue;
    const reference =
      'Reference' in token
        ? {
            sheet: token.Reference.sheet,
            left: token.Reference,
            right: token.Reference,
          }
        : 'Range' in token
          ? token.Range
          : undefined;
    if (
      !reference ||
      (reference.sheet ?? currentSheet).toLowerCase() !==
        move.sheet.toLowerCase()
    )
      continue;
    const { left, right } = reference;
    const area = {
      top: Math.min(left.row, right.row) - 1,
      left: Math.min(left.column, right.column) - 1,
      bottom: Math.max(left.row, right.row) - 1,
      right: Math.max(left.column, right.column) - 1,
    };
    const text = characters.slice(start, end).join('');
    const leading = text.match(/^\s*/)?.[0] ?? '';
    const body = text.slice(leading.length);
    const prefix = body.slice(0, body.lastIndexOf('!') + 1);
    let replacement: string;
    if (containsBounds(move.source, area)) {
      const reference = body.slice(prefix.length);
      const wholeRows = /^\$?\d+:\$?\d+$/.test(reference);
      const wholeColumns = /^\$?[A-Z]+:\$?[A-Z]+$/i.test(reference);
      const bound = (point: typeof left) => {
        const row = `${point.absolute_row ? '$' : ''}${point.row + rows}`;
        const column = `${point.absolute_column ? '$' : ''}${columnName(point.column - 1 + columns)}`;
        return wholeRows ? row : wholeColumns ? column : `${column}${row}`;
      };
      replacement = `${prefix}${bound(left)}${'Range' in token ? `:${bound(right)}` : ''}`;
    } else if (containsBounds(move.destination, area)) replacement = '#REF!';
    else continue;
    characters.splice(
      start,
      end - start,
      ...Array.from(`${leading}${replacement}`)
    );
    changed = true;
  }
  return changed ? characters.join('') : formula;
}

/**
 * Move a rectangle of cells to another place on its sheet, as Excel's cut and
 * paste does. Values, formats, notes and data rules go with the cells and
 * replace what was at the destination. References to the moved cells, from
 * any sheet's formulas, rules, names, charts and pivot tables, follow them;
 * references inside moved formulas keep pointing at the same cells.
 */
export function moveWorkbookRange(
  sheets: SpreadsheetWorkbookSheet[],
  move: RangeMove
): SpreadsheetWorkbookSheet[] {
  const { source, rows, columns } = move;
  const destination = {
    top: source.top + rows,
    left: source.left + columns,
    bottom: source.bottom + rows,
    right: source.right + columns,
  };
  const target = sheets.findIndex((sheet) => sheet.id === move.sheetId);
  if (
    target < 0 ||
    ![
      source.top,
      source.left,
      source.bottom,
      source.right,
      rows,
      columns,
    ].every(Number.isInteger) ||
    (!rows && !columns) ||
    source.top < 0 ||
    source.left < 0 ||
    source.top > source.bottom ||
    source.left > source.right ||
    destination.top < 0 ||
    destination.left < 0 ||
    destination.bottom >= SPREADSHEET_MAX_ROWS ||
    destination.right >= SPREADSHEET_MAX_COLUMNS
  )
    throw new Error('Invalid cell range.');
  const { name: sheetName, layout } = sheets[target];
  // Moving every row or column of the sheet's cells moves whole-row and
  // whole-column references too.
  const referenced = (area: CellBounds) => ({
    ...area,
    bottom:
      source.top === 0 && source.bottom >= layout.rowCount - 1
        ? Number.POSITIVE_INFINITY
        : area.bottom,
    right:
      source.left === 0 && source.right >= layout.columnCount - 1
        ? Number.POSITIVE_INFINITY
        : area.right,
  });
  const rewrite = (formula: string, currentSheet: string) =>
    movedReferences(
      formula,
      currentSheet,
      {
        sheet: sheetName,
        source: referenced(source),
        destination: referenced(destination),
      },
      rows,
      columns
    );
  const shift = (area: CellBounds) => ({
    top: area.top + rows,
    left: area.left + columns,
    bottom: area.bottom + rows,
    right: area.right + columns,
  });
  /** Where a cell of the moved sheet goes; undefined once replaced. */
  const place = (address: string) => {
    const { row, column } = parseCellAddress(address)!;
    const cell = { top: row, left: column, bottom: row, right: column };
    if (containsBounds(source, cell))
      return formatCellAddress(row + rows, column + columns);
    return containsBounds(destination, cell) ? undefined : address;
  };
  /** Merges, arrays and pivot tables move whole or not at all. */
  const placeRange = (range: string, name: string) => {
    const [area] = sqrefBounds(range);
    if (containsBounds(source, area)) return boundsSqref([shift(area)]);
    if (containsBounds(destination, area)) return;
    if (
      overlapBounds(area, source).length ||
      overlapBounds(area, destination).length
    )
      throw new Error(`This move would split ${name}.`);
    return range;
  };
  /** A rule that only partly moves is split in two. */
  const placeRules = <T extends { range: string }>(rules: T[]) =>
    rules.flatMap((rule) => {
      const areas = sqrefBounds(rule.range);
      if (
        !areas.some(
          (area) =>
            overlapBounds(area, source).length ||
            overlapBounds(area, destination).length
        )
      )
        return [rule];
      const kept = areas
        .flatMap((area) => subtractBounds(area, source))
        .flatMap((area) => subtractBounds(area, destination));
      const moved = areas
        .flatMap((area) => overlapBounds(area, source))
        .map(shift);
      return [kept, moved]
        .filter((part) => part.length)
        .map((part) => ({ ...rule, range: boundsSqref(part) }));
    });
  /** The moved sheet's cell ranges, notes and rules after the move. */
  const placeMetadata = (metadata: WorkbookSheetMetadata) => {
    const result = { ...metadata };
    if (result.merges)
      result.merges = result.merges.flatMap(
        (range) => placeRange(range, 'a merged cell') ?? []
      );
    if (result.arrayFormulas)
      result.arrayFormulas = Object.fromEntries(
        Object.values(result.arrayFormulas).flatMap((range) => {
          const next = placeRange(range, 'an array formula');
          return next ? [[next.split(':')[0], next]] : [];
        })
      );
    if (result.pivotTables) {
      result.pivotTables = result.pivotTables.flatMap((pivot) => {
        const location = placeRange(pivot.location, 'a pivot table');
        return location ? [{ ...pivot, location }] : [];
      });
      if (!result.pivotTables.length) delete result.pivotTables;
    }
    if (result.notes)
      result.notes = Object.fromEntries(
        Object.entries(result.notes).flatMap(([address, note]) => {
          const next = place(address);
          return next ? [[next, note]] : [];
        })
      );
    if (result.validations) result.validations = placeRules(result.validations);
    if (result.conditionalFormats)
      result.conditionalFormats = placeRules(result.conditionalFormats);
    return result;
  };
  return sheets.map((sheet, index) => {
    const cells: SpreadsheetCells = {};
    for (const [address, cell] of Object.entries(sheet.cells)) {
      const next = index === target ? place(address) : address;
      if (!next) continue;
      const value =
        cell.value.startsWith('=') && cell.format !== 'text'
          ? rewrite(cell.value, sheet.name)
          : cell.value;
      cells[next] = value === cell.value ? cell : { ...cell, value };
    }
    const metadata =
      sheet.metadata &&
      withSheetFormulas(
        sheet.metadata,
        sheetFormulas(sheet.metadata).map((formula) =>
          rewrite(formula, sheet.name)
        )
      );
    if (index !== target) return { ...sheet, cells, metadata };
    return {
      ...sheet,
      cells,
      metadata: metadata && placeMetadata(metadata),
      layout: {
        ...sheet.layout,
        rowCount: Math.max(sheet.layout.rowCount, destination.bottom + 1),
        columnCount: Math.max(sheet.layout.columnCount, destination.right + 1),
      },
    };
  });
}