collective
Browse / github.com/storytold/gridcraft / patch
Paste Special: a transposed paste turns formula references as Excel does (fixes #2450)
Paste Special with Transpose does not adjust formula references Paste Special with Transpose moves the values, but a formula keeps pointing at the cell above it instead of the cell it should refer to. **Expected (Excel):** the transposed copy adds 1 to the cell to its left and shows 11/1/2025. **Actual:** the transposed copy still points at the cell above it. In the example it becomes `=I5+1` (a blank cell above), so it shows 1/1/1900. It should be `=H6+1`, the cell to its left, showing 11/1/2025. **Steps** 1. Type 10/31/2025 in A3. 2. In A4, enter `=A3+1` (shows 11/1/2025). 3. Select A3:A4 and copy. 4. Select C3, then Paste Special > Transpose. 5. The r
- Status
- open
- Score
- 0.00
- Adopters
- 0 other operators
- Author
- zyzabruh (imported from pull request #2606, open)
- Base
45f5ab18e4e57bf50ee88d2526deb4b87c5b24cb- Size
- +128 −6 in 6 files
- Review
- pending
- Shared
- 2026-10-28
- Id
6ead5002b25a1c30aca35cab
How it went
No other operator has reported using it yet.
Diff
raw · ROADMAP.md crates/engine/src/cmd/edit/paste_special.rs crates/engine/src/cmd/edit/tests.rs crates/formula/src/adjust.rs crates/formula/src/tests.rs docs/ui-parity.md
@@ -2,7 +2,7 @@ **Stage: alpha** · next: beta, ~25 points of "ready for real work" and ~160–240 h away -> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right, and fills a selection's empty last row and column) · **Target:** Microsoft Excel (Microsoft 365)+> **Last reviewed:** 2026-10-11 · **Last updated:** 2026-10-26 · **Change:** minor (Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does) · **Target:** Microsoft Excel (Microsoft 365) GridCraft aims at full Microsoft Excel parity, and at being better in places: faster, open (XLSX is the native format), fully scriptable by agents (MCP, control channel, CLI), and on the web.@@ -135,6 +135,7 @@ Ranked; estimates in Opus 5.5 agent-hours. Detail and the full beta-gate list in | Date | What landed | |---|---|+| 2026-10-26 | Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does: the row offset becomes the column offset and the other way round, so `=A3+1` copied from A3:A4 and pasted at C3 reads the cell to its left (`=C3+1`) instead of keeping the offset it had in the column; a reference pinned with `$` is left standing, a whole row or column comes back as a cell range open to the sheet's edge, and one off the grid is `#REF!` (#2450) | | 2026-10-25 | AutoSum over a selection does what Excel does: a selected row gets one total to its right (in its last cell if that is empty), not a one-cell total under each cell; a block whose last column is empty gets each row's total there, one whose last row is empty each column's, and with both the corner gets the total of the block; otherwise totals go under each column as before | | 2026-10-24 | Go To lists the named ranges of every sheet (`Sheet2!Name` for a name scoped to another sheet; hidden names, constants and formulas left out) and the references entered before, a double-click goes to an entry, and the Reference box takes several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30`) | | 2026-10-24 | Status bar: a right-click opens a menu that sets which selection statistics show (Average, Count and Sum by default; Numerical Count, Minimum and Maximum are new), kept with the preferences (`view.statusBar`) |@@ -119,8 +119,12 @@ pub(super) fn paste_special(s: &mut Session, p: &Json) -> Result<Json> { moved.dynamic = f.dynamic; return Some(std::sync::Arc::new(moved)); }- let moved =- gridcraft_formula::adjust::shift_relative(e, dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64);+ let moved = if transpose {+ // A transposed paste turns the references instead of shifting them (#2450).+ gridcraft_formula::adjust::transpose_relative(e, sc, dest)+ } else {+ gridcraft_formula::adjust::shift_relative(e, dest.row as i64 - sc.row as i64, dest.col as i64 - sc.col as i64)+ }; Some(std::sync::Arc::new(Formula { dynamic: f.dynamic, ..Formula::from_expr(moved) })) }; if link {@@ -285,6 +285,39 @@ fn cut_paste_that_overflows_the_grid_moves_nothing() { assert_eq!(get(&mut s, "XFD2")["value"], json!(2.0)); } +#[test]+fn transposed_paste_turns_the_formula_with_the_cell_that_carries_it() {+ // #2450: the values moved but the formula kept the offset it had in the source column,+ // so it read the cell above instead of the cell to its left.+ let mut s = Session::new();+ s.new_workbook();+ s.execute("cell.set", json!({"cell": "A3", "input": "2025-10-31"})).unwrap();+ s.execute("cell.set", json!({"cell": "A4", "input": "=A3+1"})).unwrap();+ s.execute("edit.copy", json!({"range": "A3:A4"})).unwrap();+ s.execute("edit.pasteSpecial", json!({"what": "all", "transpose": true, "at": "C3"})).unwrap();+ let get = |s: &mut Session, a: &str| s.execute("cell.get", json!({"cell": a})).unwrap();+ let date = get(&mut s, "C3")["value"].as_f64().expect("the date pastes as its serial");+ assert_eq!(date, get(&mut s, "A3")["value"].as_f64().unwrap());+ assert_eq!(get(&mut s, "D3")["formula"], "=C3+1", "the formula reads the cell to its left, as Excel's does");+ assert_eq!(get(&mut s, "D3")["value"].as_f64().unwrap(), date + 1.0, "and shows the day after it");+}++#[test]+fn transposed_paste_turns_every_kind_of_reference_the_way_excel_does() {+ // Observed from Excel 16: relative parts turn a quarter turn, a reference pinned with $+ // is left standing, and a whole column comes back as a range open to the sheet's edge.+ let mut s = Session::new();+ s.new_workbook();+ s.execute("range.setValues", json!({"range": "A1", "values": [["=B1+1"], ["=$B$1"], ["=SUM(B1:C3)"], ["=SUM(B:D)"]]})).unwrap();+ s.execute("edit.copy", json!({"range": "A1:A4"})).unwrap();+ s.execute("edit.pasteSpecial", json!({"what": "all", "transpose": true, "at": "C10"})).unwrap();+ let formula = |s: &mut Session, a: &str| s.execute("cell.get", json!({"cell": a})).unwrap()["formula"].clone();+ assert_eq!(formula(&mut s, "C10"), "=C11+1");+ assert_eq!(formula(&mut s, "D10"), "=$B$1");+ assert_eq!(formula(&mut s, "E10"), "=SUM(C11:E12)");+ assert_eq!(formula(&mut s, "F10"), "=SUM($C11:$XFD13)");+}+ #[test] fn replace_case_insensitive() { assert_eq!(replace_ci("Apple apple APPLE", "apple", "pear"), "pear pear pear");@@ -1,7 +1,7 @@ //! Reference adjustment: copying formulas, inserting/deleting rows and columns, moving cells, //! renaming and deleting sheets. -use gridcraft_core::{CellError, MAX_COLS, MAX_ROWS, RangeRef};+use gridcraft_core::{CellError, CellRef, MAX_COLS, MAX_ROWS, RangeRef}; use crate::ast::{Anchor, Expr, RefKind, Reference, SheetSel, clamp_anchor}; @@ -36,6 +36,68 @@ pub fn shift_relative_wrapping(e: Expr, dr: i64, dc: i64) -> Expr { }) } +/// Paste Special ▸ Transpose: the cell holding the formula moves from `src` to `dest`, so every+/// reference turns a quarter turn with it — its row offset becomes its column offset and the other+/// way round, `(r, c)` → `dest + (c − src.col, r − src.row)`.+///+/// As Excel writes it out: a reference that pins a row or a column with `$` is left exactly as it+/// stands, a whole-row or whole-column reference comes back as a cell range whose open side runs+/// from the transposed far edge of the sheet, and a result that falls off the grid becomes `#REF!`.+pub fn transpose_relative(e: Expr, src: CellRef, dest: CellRef) -> Expr {+ e.map(&mut |x| match x {+ Expr::Ref(r) if !has_absolute(&r) => match transpose_ref(&r, src, dest) {+ Some(r) => Expr::Ref(r),+ None => Expr::Error(CellError::Ref),+ },+ other => other,+ })+}++/// Whether any end of the reference pins a row or a column with `$`.+fn has_absolute(r: &Reference) -> bool {+ let abs = |a: &Anchor| a.row_abs || a.col_abs;+ match &r.kind {+ RefKind::Cell(a) => abs(a),+ RefKind::Range(a, b) => abs(a) || abs(b),+ RefKind::Rows(_, a0, _, a1) | RefKind::Cols(_, a0, _, a1) => *a0 || *a1,+ }+}++/// Where an end lands after the turn: its column drives the new row, its row the new column.+fn transpose_anchor(a: &Anchor, src: CellRef, dest: CellRef) -> Option<(u32, u32)> {+ clamp_anchor(dest.row as i64 + a.col as i64 - src.col as i64, dest.col as i64 + a.row as i64 - src.row as i64)+}++/// Turns one reference with `src` → `dest`: `None` when an end falls outside the sheet, which+/// the caller turns into `#REF!`.+fn transpose_ref(r: &Reference, src: CellRef, dest: CellRef) -> Option<Reference> {+ let kind = match &r.kind {+ RefKind::Cell(a) => {+ let (row, col) = transpose_anchor(a, src, dest)?;+ RefKind::Cell(Anchor { row, col, ..*a })+ }+ RefKind::Range(a, b) => {+ let (row, col) = transpose_anchor(a, src, dest)?;+ let (row2, col2) = transpose_anchor(b, src, dest)?;+ RefKind::Range(Anchor { row, col, ..*a }, Anchor { row: row2, col: col2, ..*b })+ }+ // A whole row turns into a cell range: its rows become columns, while its open columns+ // become rows pinned with `$` — from the transposed first column down to the last row.+ RefKind::Rows(r0, _, r1, _) => {+ let (row, col) = clamp_anchor(dest.row as i64 - src.col as i64, dest.col as i64 + *r0 as i64 - src.row as i64)?;+ let (row2, col2) = clamp_anchor(MAX_ROWS as i64 - 1, dest.col as i64 + *r1 as i64 - src.row as i64)?;+ RefKind::Range(Anchor { row, col, row_abs: true, col_abs: false }, Anchor { row: row2, col: col2, row_abs: true, col_abs: false })+ }+ // A whole column likewise: its columns become rows, its open rows become columns up to XFD.+ RefKind::Cols(c0, _, c1, _) => {+ let (row, col) = clamp_anchor(dest.row as i64 + *c0 as i64 - src.col as i64, dest.col as i64 - src.row as i64)?;+ let (row2, col2) = clamp_anchor(dest.row as i64 + *c1 as i64 - src.col as i64, MAX_COLS as i64 - 1)?;+ RefKind::Range(Anchor { row, col, row_abs: false, col_abs: true }, Anchor { row: row2, col: col2, row_abs: false, col_abs: true })+ }+ };+ Some(Reference { sheet: r.sheet.clone(), kind })+}+ /// Whether the formula has a relative (no `$`) row or column anywhere. pub fn has_relative_refs(e: &Expr) -> bool { e.references().iter().any(|r| match r.kind {@@ -1,5 +1,5 @@ use super::*;-use crate::adjust::{Axis, Edit, adjust, rename_sheet, shift_relative};+use crate::adjust::{Axis, Edit, adjust, rename_sheet, shift_relative, transpose_relative}; fn rt(src: &str) -> String { print(&parse(src).unwrap_or_else(|e| panic!("{src}: {e}")))@@ -165,6 +165,27 @@ fn copy_shift() { assert_eq!(print(&shift_relative(e, 5, 1)), "SUM(B:B)"); } +#[test]+fn copy_transpose() {+ let at = gridcraft_core::CellRef::new;+ // The row of each reference drives its new column and the other way round (Excel, observed:+ // A1 → F6 carrying =B1+1 comes out as =F7+1).+ let e = parse("B1+SUM(B1:C3)+Sheet2!A1").unwrap();+ assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "F7+SUM(F7:H8)+Sheet2!F6");+ // The turn is around the two positions of the formula's cell, not around A1.+ let e = parse("G3+SUM(1:2)").unwrap();+ assert_eq!(print(&transpose_relative(e, at(1, 2), at(9, 5))), "G14+SUM(E$8:F$1048576)");+ // A reference pinned with `$` is left exactly as it stands.+ let e = parse("$B$1+B$1+$B1+SUM($B$1:$C$3)").unwrap();+ assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "$B$1+B$1+$B1+SUM($B$1:$C$3)");+ // Whole rows and columns come back as a cell range open to the sheet's edge.+ let e = parse("SUM(1:2)+SUM(B:D)").unwrap();+ assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "SUM(F$6:G$1048576)+SUM($F7:$XFD9)");+ // Off the grid is #REF!, like a copy that runs past the edge.+ let e = parse("A16384+B1").unwrap();+ assert_eq!(print(&transpose_relative(e, at(0, 0), at(5, 5))), "#REF!+F7");+}+ #[test] fn insert_delete() { let ins = Edit::Insert { axis: Axis::Rows, at: 2, count: 2 };@@ -1,6 +1,6 @@ # UI parity -> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-25 · **Change:** minor (AutoSum over a selected row puts its total to the right) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4+> **Last reviewed:** 2026-10-10 · **Last updated:** 2026-10-26 · **Change:** minor (Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel does) · **Target:** Microsoft Excel (Microsoft 365), Excel for Mac 16.113.4 Interaction, controls and feel, against Excel. Part of [target-app-parity.md](target-app-parity.md). **UI/UX fidelity: ~55% ready, 50–80 h** (estimated). Ribbon command coverage is measured@@ -187,6 +187,7 @@ scrollbar bugs 2–4 · pixel/feel pass against observed Excel 7–12. | Date | Change | Summary | |---|---|---|+| 2026-10-26 | minor | Paste Special ▸ Transpose turns a formula's references with the cell that carries it, as Excel's does (#2450): the row offset becomes the column offset and the other way round, so `=A3+1` copied from A3:A4 and pasted at C3 lands as `=C3+1` and shows the day after the cell to its left, instead of keeping the offset it had in the column and reading the cell above. A reference pinned with `$` is left exactly as Excel leaves it, a whole row or column comes back as a cell range open to the sheet's edge, and one that lands off the grid is `#REF!` | | 2026-10-25 | minor | AutoSum (Alt+=, Home › AutoSum) over a selected row puts its total to the right of the row, or in its last cell when that is empty, as in Excel; it wrote a one-cell total under each cell. A selection's empty last column takes each row's total and its empty last row each column's, with the block's total in the corner when both are empty | | 2026-10-25 | minor | Paste Name lays out only the names in view, so a workbook with thousands of names opens it at once; the arrow keys still scroll the chosen name into view. Go To matches a `Sheet2!Name` reference and hides a workbook name behind the active sheet's own name in any case and script (`Größe` = `GRÖSSE`), as formulas do. The two Page Layout view rows are one; the 0.7 in side margin used when Align with Page Margins is off is marked unverified | | 2026-10-24 | minor | Go To lists the named ranges on every sheet, as Excel's does ("The Go to popup window shows named ranges on every worksheet in your workbook"): a name scoped to another sheet appears as `Sheet2!Name`, and hidden names, constants and formulas are left out. It keeps the references entered (newest first, ten at most), and a double-click on an entry goes there. The Reference box and `edit.goTo` take several names or references separated by commas (`Price, Type`, `B14:C22,F19:G30,H21:H29`) and select them all; `edit.goTo {list: true}` returns the list |