# The `.pumapack` file format (PumaSheet, schema 2)

This document describes PumaSheet's backup files in enough detail to **edit
one by hand** or **generate one from scratch** that imports cleanly. The app
opens the result with no warnings, nothing dropped and nothing re-typed. It is
written for a reader, human or AI, who has no access to the app's source.

A PumaSheet backup is a UTF-8 JSON file. The app saves it with a `.json`
extension; the importer accepts `.json` and `.pumapack` alike. There are two
shapes, and the importer tells them apart by their keys:

| Shape | Top-level data key | Made by | On import |
|---|---|---|---|
| **Full backup** | `workbook` | the topbar **Export** button (`pumasheet-backup-YYYY-MM-DD.json`) and Cmd/Ctrl+S (`pumasheet-backup.json`) | **Replaces** the whole workbook, after a confirm dialog |
| **Single-file pack** | `file` | a file tab's right-click menu, **Export › JSON backup (this file)** (`<file-name>.json`) | **Adds** one file as a new tab, with no question |

The full backup is what a user normally exports and hands over, so this
document leads with it. The single-file pack holds exactly the same file
object, so everything in §4 applies to both.

PumaSheet imports a file in either of two ways:

- from the topbar **Import** button;
- by dropping the file anywhere on the window.

The same Import button also takes `.csv`, `.tsv` and `.xlsx` files. Those are
separate formats and are not described here.

---

## 1. The short version

If you only read one section, read this one.

1. Put `"app": "pumasheet"` first in the file and save it as `.json` or
   `.pumapack`. A file that is neither, and does not start that way, is read
   as **CSV**. See §2.3.
2. Choose the shape. `workbook` **replaces everything** the user has (they are
   asked first). `file` **adds one tab** alongside their work. When you are
   adding to someone's existing data, send a single-file pack.
3. A workbook is a list of **files** (the tabs along the top). A file is a list
   of **sheets** (the tabs along the bottom). A sheet is a grid of **cells**.
4. **Every cell value is a JSON string**, even numbers: `"12"`, not `12`. A
   number, `true` or `false` in `cells` makes the import fail part-way. See §8.
5. Cells are keyed `"row,col"`, **zero-based**: `"0,0"` is A1, `"1,0"` is A2,
   `"1,2"` is C2. Formulas use ordinary A1 references. See §6.1.
6. **Row 0 is the header row.** Put column titles there; data starts at row 1.
7. Give each column a type in `types` (`"number"`, `"money"`, `"date"` and so
   on). The type decides how the stored string is read and shown. See §6.2.
8. Write dates as `"YYYY-MM-DD"`, times as `"HH:MM"`, and date-times as
   `"YYYY-MM-DD HH:MM"`, with **no time zone**. See §6.3.
9. Set `rows` and `cols` large enough to contain every cell you write.
10. Write every key of every sheet, using `{}` or `[]` for "nothing". Never
    write `null` except where §4 says it is allowed.
11. Check the result against the checklist in §9.

§10 is a complete, valid example you can copy and adapt.

---

## 2. The envelope

### 2.1 Full backup

```json
{
  "app": "pumasheet",
  "schema": 2,
  "exportedAt": "2026-10-28T09:00:00.000Z",
  "workbook": { "...the workbook object, see §3..." },
  "accent": null
}
```

| Key | Value | Notes |
|---|---|---|
| `app` | `"pumasheet"` | Not checked on import, but write it, and write it first. It is one of the ways the file is recognized as JSON (§2.3). |
| `schema` | `2` | The current schema. It is **not checked**: a pack claiming `99` imports anyway. Write `2`. |
| `exportedAt` | ISO 8601 datetime | Informational. Replaced with the current time on the next export. |
| `workbook` | object | See §3. |
| `accent` | `null` or a color like `"#9070e0"` | The app's accent color (the highlight used on buttons and the active tab). If set, restoring the backup **changes the user's accent** to it. `null` or missing leaves their accent alone. Write `null` unless you mean it. |

The importer also accepts a workbook object with no envelope at all (a file
whose top level is `{ "v": 2, "files": [ … ] }`). Write the envelope anyway.

### 2.2 Single-file pack

```json
{
  "app": "pumasheet",
  "schema": 2,
  "exportedAt": "2026-10-28T09:00:00.000Z",
  "file": { "...one file object, see §4.1..." }
}
```

The single-file pack has no `accent` and no workbook-level keys. If a pack
carries both `file` and `workbook`, the `file` wins and `workbook` is ignored.

### 2.3 How the importer reads a file

The file is treated as a PumaSheet backup if **any** of these is true:

- its name ends in `.json` or `.pumapack`;
- its first 300 characters contain `"app": "pumasheet"`;
- its first 300 characters are an object that already mentions `"sheets":`.

Otherwise it is imported as **CSV**, and the JSON text lands in a new sheet as
rows of text, with a toast such as *"Imported 337 rows into “name”"*. Naming the file
`.json` or `.pumapack` avoids this.

Once it is read as JSON:

| Condition | What the user sees |
|---|---|
| Not valid JSON | *"Could not read backup: …"* followed by the browser's parse error. Nothing changes. |
| An object with `file` whose `sheets` is an array | The file is added as a new tab and becomes the active one. Toast: *"Imported file “Office move”"*. No dialog. |
| An object with `workbook.files` (or top-level `files`) holding at least one object | A confirm dialog: *"Restore this backup (2 files, 3 sheets)? This REPLACES your current workbook."* On OK the toast is *"Workbook restored"*. On Cancel nothing changes and nothing is said. |
| Anything else, including `"files": []` | *"Not a PumaSheet backup"*. Nothing changes. |

A restore can be undone with Cmd/Ctrl+Z, like any other change.

**A bare file object with no wrapper is not a single-file pack.** A top level
of `{ "id": …, "name": …, "sheets": [ … ] }` is read as a workbook from an
older version of the app: it **replaces** the whole workbook, and the file's
`name`, `accent_color` and `settings` are thrown away (the file comes back as
"Untitled" with default settings). Always wrap a file in `{ "file": … }`.

---

## 3. The workbook object

```json
{
  "v": 2,
  "files": [ { "...file, see §4.1..." } ],
  "activeFile": "f-office",
  "theme": "dark"
}
```

| Field | Type | Notes |
|---|---|---|
| `v` | `2` | Overwritten with `2` on import. |
| `files` | array of file objects | One per top tab, in tab order. **Must hold at least one file.** Entries that are not objects are silently dropped. |
| `activeFile` | a file `id` | The tab that is open after the restore. If it matches no file, the first file opens. |
| `theme` | `"dark"` or `"light"` | **Restoring switches the app to this theme.** Anything else becomes `"dark"`. |

Keys the app does not know are kept, stored and exported again, but have no
effect. This holds at every level: workbook, file, sheet and cell format.

---

## 4. Record shapes

### Conventions for every record

- **`id`** is any non-empty string. File and sheet ids are **replaced with new
  ones on every import** (see §7), so their only job in a pack is to let
  `activeFile` name the open tab. Short readable ids (`f-office`, `sh-budget`)
  are fine.
- **Column and row numbers are zero-based integers.** Where they are used as
  object keys (in `types`, `widths`, `rowH`, `colFmt`, `validation`) they are
  written as strings, as JSON requires: `{ "3": "money" }`.
- **Defaults are filled in only for keys that are missing.** Omitting a key is
  safe for most sheet fields; §4.3 says which. A `null` is not always treated
  as missing, so do not write one except where a table says it is allowed.
- **Enum values are not validated on import.** A misspelled type, operator or
  date format is kept, and then behaves as described in §8.

### 4.1 File (one top tab)

```json
{
  "id": "f-office",
  "name": "Office move",
  "accent_color": "#3fb8e0",
  "sheets": [ { "...sheet, see §4.3..." } ],
  "activeSheet": 0,
  "settings": { "currency": "$", "dateFormat": "iso", "decimals": null, "thousands": true }
}
```

| Field | Type | Notes |
|---|---|---|
| `id` | string | Replaced on import. |
| `name` | string | Shown on the tab. Missing or `""` becomes `"Untitled"`. |
| `accent_color` | `null` or `"#rgb"` / `"#rrggbb"` | The tab's color dot. `null` uses the app's accent. A value that is not a hex color is kept, but the dot shows plain blue. |
| `sheets` | array of sheet objects | The bottom tabs, in order. Entries that are not objects are dropped. An empty array becomes one blank sheet named `"Sheet 1"`. |
| `activeSheet` | integer | Index into `sheets` of the sheet that opens first. Out of range or not a number becomes `0`. |
| `settings` | object | See §4.2. Missing keys take the defaults. |

### 4.2 `settings`

Display settings for every sheet in the file. They change how values **look**,
never what is stored.

| Field | Default | Values |
|---|---|---|
| `currency` | `"$"` | The symbol put in front of money values, e.g. `"€"`, `"£"`, `"CHF"`. The app's own dialog allows up to 3 characters. |
| `dateFormat` | `"med"` | `"med"` → `Oct 5, 2026`; `"iso"` → `2026-10-05`; `"us"` → `10/05/2026`; `"long"` → `October 5, 2026`. Any other value shows as `"med"`. |
| `decimals` | `null` | `null` (show as many decimals as the number has, up to 10) or an integer `0`–`4`. Applies to number and percent cells. Money always defaults to 2. |
| `thousands` | `false` | `true` groups number and percent cells as `1,234`. Money is grouped regardless, unless a column or cell turns it off. |

### 4.3 Sheet (one bottom tab)

```json
{
  "id": "sh-budget",
  "name": "Budget",
  "rows": 8,
  "cols": 6,
  "cells": { "0,0": "Item", "1,0": "Standing desk", "1,2": "12" },
  "types": { "2": "number", "3": "money" },
  "widths": { "0": 200 },
  "rowH": {},
  "fmts": { "7,0": { "b": 1 } },
  "colFmt": { "2": { "decimals": 0 } },
  "validation": { "1": { "values": ["Furniture", "IT", "Moving"] } },
  "sort": [],
  "filters": [],
  "cf": [],
  "freezeCol": 0,
  "freezeRows": 1,
  "hiddenRows": [],
  "hiddenCols": []
}
```

| Field | Type | If missing | Notes |
|---|---|---|---|
| `id` | string | new id | Replaced on import anyway. |
| `name` | string | **stays missing** | Shown on the bottom tab. A missing name shows as a blank tab; always write one. |
| `rows` | integer 1–5000 | `30` | Grid height, **including** the header row. Above 5000 is cut to 5000; not a number, or below 1, becomes 30. |
| `cols` | integer 1–52 | `8` | Grid width (A to AZ at most). Above 52 is cut to 52; not a number, or below 1, becomes 8. |
| `cells` | object | `{}` | See §4.4. **Must be an object.** |
| `types` | object | `{}` | Column index → column type. See §4.5. |
| `widths` | object | `{}` | Column index → width in pixels. Default 104. The app's own resize allows 40–600; import does not check. |
| `rowH` | object | `{}` | Row index → height in pixels. Default 26. The app's own resize allows 22–600 and only stores heights above 26. |
| `fmts` | object | `{}` | Cell key → cell format. See §4.6. |
| `colFmt` | object | `{}` | Column index → number format. See §4.7. |
| `validation` | object | `{}` | Column index → allowed values. See §4.8. |
| `sort` | array | `[]` | See §4.9. |
| `filters` | array | `[]` | See §4.10. **Must be an array**; an object here makes the import fail. |
| `cf` | array | `[]` | Conditional-formatting rules. See §4.11. |
| `freezeCol` | `0` or `1` | `0` | `1` freezes column A while scrolling sideways. Only the first column can be frozen. Any truthy value becomes `1`. |
| `freezeRows` | integer ≥ 1 | `1` | Rows pinned at the top **counting the header**: `1` pins just the header, `3` pins the header and the first two visible data rows. |
| `hiddenRows` | array of row indexes | `[]` | Data rows hidden from view. Row 0 cannot be hidden. |
| `hiddenCols` | array of column indexes | `[]` | Columns hidden from view. |

### 4.4 `cells`

```json
{
  "0,0": "Item",
  "1,0": "Standing desk",
  "1,2": "12",
  "1,3": "489.00",
  "1,4": "=C2*D2",
  "1,5": "TRUE"
}
```

- The key is `"row,col"`: two zero-based integers joined by a comma, with **no
  space**. `"1,4"` is row index 1, column index 4, which is cell **E2**.
- The value is **always a string**: exactly what the user would type into the
  cell. Numbers, dates, booleans and formulas are all strings (`"12"`,
  `"2026-11-06"`, `"TRUE"`, `"=C2*D2"`).
- An empty cell is **absent**. Do not write `""` or `null` for blanks; leave
  the key out. (A `null` is harmless, and shows as blank, but the app never
  writes one.)
- How the string is read (as a number, a date, text) depends on the column's
  type. See §6.2.
- Nothing computed is stored. A formula's result, and every formatted value
  on screen, is worked out when the sheet is drawn.

### 4.5 `types`

`{ "<column index>": "<type>" }`. A column not listed is `"auto"`.

| Type | Column letter badge | Reads the cell string as |
|---|---|---|
| `"auto"` | none | a number if it looks like one, then `TRUE`/`FALSE`, otherwise text. **Dates are not recognized** in an auto column; they stay text. |
| `"text"` | TXT | text, always. Use it for codes like `"00123"` that must keep their zeros. |
| `"number"` | # | a number. |
| `"money"` | $ | a number, shown with the file's currency and 2 decimals. |
| `"percent"` | % | a number where `"12.5%"` means 0.125 and a bare `"0.125"` also means 0.125. Shown as `12.5%`. |
| `"date"` | DATE | a calendar date. |
| `"time"` | TIME | a time of day. |
| `"datetime"` | D·T | a date and a time. |
| `"boolean"` | T/F | `TRUE` or `FALSE`. |

A string that cannot be read as the column's type is **kept and shown as
typed**, as text. For example `"TBC"` in a money column shows `TBC`.

### 4.6 `fmts` (cell formatting)

`{ "<row>,<col>": { …format… } }`. Every key is optional; write only the ones
you use.

```json
{
  "6,3": { "t": "percent", "dec": 0 },
  "7,4": { "b": 1, "bd": 1 },
  "4,5": { "wrap": 1, "i": 1 },
  "1,4": { "fg": "#9aa3ad" }
}
```

| Key | Values | Meaning |
|---|---|---|
| `b` | `1` | Bold. |
| `i` | `1` | Italic. |
| `a` | `"l"`, `"c"`, `"r"` | Align left, center, right. Without it, numbers align right and text left. |
| `bg` | CSS color | Fill. The app's own fills are translucent, e.g. `"rgba(229,72,72,.30)"`, so they read in both light and dark themes. |
| `fg` | CSS color | Text color, e.g. `"#e05a5a"`. Not applied to a cell showing an error code. |
| `bd` | integer 1–15 | Borders, added together: top `1`, right `2`, bottom `4`, left `8`. `5` is top and bottom. |
| `wrap` | `1` | Wrap long text onto several lines. A value containing `\n` wraps anyway. |
| `t` | a type from §4.5 | This cell's type, overriding the column's. |
| `dec` | integer 0–10 | Decimal places for this cell. |
| `grp` | `1` or `0` | Thousands grouping on or off for this cell. |

**Colors** may use only letters, digits, `#`, parentheses, commas, dots, `%`,
spaces and hyphens, at most 64 characters. `"#e05a5a"`,
`"rgba(80,150,235,.32)"` and `"steelblue"` are fine. Anything else is ignored
and the cell shows with no color.

### 4.7 `colFmt` (column number format)

```json
{ "2": { "decimals": 0 }, "5": { "thousands": false } }
```

`decimals` (integer 0–10) and `thousands` (boolean), both optional. They
override the file's settings for that column.

Number formatting is resolved for each cell from the most specific source
that sets it:

| | Decimals | Thousands grouping |
|---|---|---|
| 1 | the cell's `fmts` `dec` | the cell's `fmts` `grp` |
| 2 | the column's `colFmt.decimals` | the column's `colFmt.thousands` |
| 3 | `2` if the cell is money | on if the cell is money |
| 4 | the file's `settings.decimals` | the file's `settings.thousands` |

### 4.8 `validation` (allowed values)

```json
{ "4": { "values": ["Open", "In progress", "Blocked", "Done"] } }
```

- Gives the column a pick-list.
- A cell whose string is **not exactly** one of the values (case matters) is
  **kept**, but marked in red as "Value not in the allowed list".
- Blank cells, formulas and the header row are never marked.
- `values` must be a non-empty array of strings; anything else is ignored.

### 4.9 `sort`

```json
[ { "col": 2, "dir": "asc" }, { "col": 0, "dir": "desc" } ]
```

- Sort levels, first one first. `dir` is `"asc"` or `"desc"`; anything other
  than `"desc"` sorts ascending.
- Blank cells always sort last.
- **Sorting is a view.** It reorders what is shown and never moves cells, so
  row numbers on screen can read out of order and formulas keep pointing at
  the same cells. Write `cells` in whatever order is natural.
- A single object (`{ "col": 2, "dir": "asc" }`) is accepted and treated as a
  one-level sort. `[]` means unsorted.

### 4.10 `filters`

```json
[
  { "col": 4, "op": "in", "values": ["Open", "In progress", "Blocked"] },
  { "col": 3, "op": "gt", "value": "100" }
]
```

A data row is shown only if it passes **every** filter.

| `op` | Needs | Row passes when the cell… |
|---|---|---|
| `"contains"` | `value` | shown text contains `value`, ignoring case |
| `"eq"` / `"neq"` | `value` | equals / does not equal `value`, by value or by shown text, ignoring case |
| `"gt"`, `"lt"`, `"gte"`, `"lte"` | `value` | is greater / less / at least / at most `value` |
| `"empty"` / `"notempty"` | nothing | is blank / is not blank |
| `"in"` | `values` | **shown text** is one of `values`; `""` in the list means blanks. This is what the column's ▾ checkbox list writes. |

- `value` is a string. It is compared as a number when it looks like one.
- `"in"` matches the text as displayed, so a money column needs `"$1,850.00"`,
  not `"1850"`.
- An unknown `op` lets every row through. An `"in"` filter with no `values`
  hides every row.
- Like sorting, filtering is a view and never changes `cells`.

### 4.11 `cf` (conditional formatting)

Each rule applies to **either** one whole column (`"col"`) **or** a fixed
rectangle (`"range"`), never both. Rules never touch the header row.

```json
[
  { "col": 4, "type": "value", "op": "eq", "v1": "Blocked", "v2": "",
    "bg": "rgba(229,72,72,.30)", "fg": null, "b": 0 },
  { "range": { "r1": 1, "c1": 1, "r2": 5, "c2": 1 }, "type": "dup",
    "bg": null, "fg": "#e05a5a", "b": 1 },
  { "range": { "r1": 1, "c1": 3, "r2": 4, "c2": 3 }, "type": "scale",
    "lo": "#fff7d6", "hi": "#e05a5a" }
]
```

| Field | Values |
|---|---|
| `col` | Column index. The rule covers rows 1 to the last row. |
| `range` | `{ "r1", "c1", "r2", "c2" }`, zero-based and inclusive, with `r1` ≤ `r2` and `c1` ≤ `c2`. Row 0 is skipped even if `r1` is 0. |
| `type` | `"value"`, `"dup"` or `"scale"`. |
| `op` | For `"value"`: `"gt"`, `"lt"`, `"eq"`, `"neq"`, `"gte"`, `"lte"`, `"between"`, `"contains"`, `"empty"`, `"notempty"`. An unknown `op` never matches. |
| `v1`, `v2` | Strings. `v1` is the value to compare with; `v2` is the upper end for `"between"` (inclusive). Write `""` when unused. |
| `bg`, `fg`, `b` | For `"value"` and `"dup"`: fill, text color (both CSS colors or `null`) and bold (`1` or `0`). |
| `lo`, `hi` | For `"scale"`: the colors for the lowest and highest number in scope, as **6-digit** hex (`"#e05a5a"`). A 3-digit hex or a color name is read as black. |
| `id` | Optional string. The app adds one to rules made in its dialog; it is not required. |

- `"value"` rules never paint a blank cell, except `"empty"`.
- `"dup"` paints every non-blank cell whose shown text appears more than once in
  the rule's scope.
- `"scale"` shades numeric cells from `lo` to `hi` across the smallest and
  largest number in scope. Non-numeric cells are left alone. A total row inside
  the scope will stretch the scale, so use a `range` that stops above it.
- Rules are checked in order and **the first one that matches wins** for a
  cell. A `bg` or `fg` set in `fmts` beats a rule's.
- Rules see every row in scope, **including rows hidden by `hiddenRows` or a
  filter**. A duplicate in a hidden row still makes its visible twin a
  duplicate.

---

## 5. Cross-references

Almost nothing in a sheet refers to anything by id. What must line up:

| From | Field | Must point at |
|---|---|---|
| workbook | `activeFile` | a `files[].id` (else the first file opens) |
| file | `activeSheet` | an index into that file's `sheets` (else `0`) |
| sheet | keys of `cells` and `fmts` | positions inside `rows` × `cols` |
| sheet | keys of `types`, `widths`, `colFmt`, `validation`; `hiddenCols[]`; `col` in `sort`, `filters`, `cf` | column indexes below `cols` |
| sheet | keys of `rowH`; `hiddenRows[]`; `r1`/`r2` of a `cf` range | row indexes below `rows` |
| formula | A1 references | cells **in the same sheet** |

Formulas cannot reach another sheet or another file. `=Budget!A1` shows
`#ERROR!`.

A cell outside `rows` × `cols` is kept in the data but is not shown, and a
formula that references it still reads it. That is a hidden input nobody can
see or edit, so size the grid to cover every cell.

---

## 6. Values, dates and formulas

### 6.1 Two ways of naming a cell

| Where | Form | A1 | B2 | E8 |
|---|---|---|---|---|
| `cells` / `fmts` keys | `"row,col"`, zero-based | `"0,0"` | `"1,1"` | `"7,4"` |
| formulas and the screen | A1, rows from 1 | `A1` | `B2` | `E8` |

So the cell at key `"r,c"` is written in a formula as column letter `c`
(`0`=A, `1`=B, … `25`=Z, `26`=AA) followed by row number `r + 1`. Getting this
off by one is the commonest mistake in a generated sheet: a formula on key
`"1,4"` that should total its own row reads `=C2*D2`, not `=C1*D1`.

### 6.2 How a cell string is read

The stored string is never changed by the app. It is read, each time the
sheet is drawn, according to the cell's type (the cell's `fmts.t`, else the
column's `types` entry, else `"auto"`):

- **Numbers** (`number`, `money`, `percent`, and `auto`): digits with an optional
  sign, decimal point and exponent. `$`, commas and spaces are ignored, and
  `(50)` means -50. So `"1,850"`, `"$1,850.00"` and `"1850"` are all 1850.
  Other currency symbols are **not** ignored: `"€12"` is text. Write plain
  numbers, `"1850"`, and let the column type add the symbol.
- **Percent**: `"10%"` is 0.1 and `"0.1"` is 0.1. Either is fine; `"10%"` reads
  better. In a column that is not `percent`, `"10%"` is text.
- **Money**: stored as a plain number; the symbol comes from the file's
  `settings.currency`, so one pack can be shown in dollars or euros by
  changing that one setting.
- **Booleans**: `"TRUE"` / `"FALSE"`, any case.
- **Text**: anything that fails the above, and everything in a `text` column.

A formula's result is shown the same way, by the cell's type: a formula in a
money column shows as money.

### 6.3 Dates and times

| Type | Write | Also understood | Shown (with `dateFormat: "med"`) |
|---|---|---|---|
| `date` | `"2026-11-06"` | `"2026-11-6"`, `"11/06/2026"` (month first), `"11/06/26"` | `Nov 6, 2026` |
| `time` | `"14:00"` | `"14:00:30"`, `"7:00 am"`, `"2:15 pm"` | `14:00` (always 24-hour, no seconds) |
| `datetime` | `"2026-10-05 08:30"` | `"2026-10-05T08:30"`, `"2026-10-05 08:30:15"` | `Oct 5, 2026 08:30` |

- **All dates and times are wall-clock values with no time zone.** Never add
  `Z` or an offset: `"2026-10-05T08:30:00Z"` is read through the viewer's
  local zone and shows as `03:30` in Chicago. Write `"2026-10-05 08:30"`.
- Slashed dates are **month first**, US style. `"06/11/2026"` is June 11. Use
  `YYYY-MM-DD` and there is no ambiguity.
- Other spellings a browser can parse (`"14 Oct 2026"`) usually work, but only
  `YYYY-MM-DD` is guaranteed.
- A date is only recognized in a `date`, `time` or `datetime` cell. In an
  `auto` or `text` column it is plain text and sorts as text.
- Inside formulas a date is a **day number counted from 1 January 1970** (the
  time is the fraction of a day). This is **not** Excel's 1900-based serial:
  `=DATE(2026,1,1)` is 20454. Put date formulas in a date-typed column so they
  show as dates.

### 6.4 Formulas

A cell string that starts with `=` is a formula.

- **References**: `A1`, `$A$1` (the `$` is allowed and ignored), ranges
  `A2:A10`. Same sheet only.
- **Operators**: `+ - * / ^`, `&` (join text), and `= <> < > <= >=` (give
  `TRUE`/`FALSE`). Parentheses as usual.
- **Literals**: numbers, `"text in double quotes"` (a quote inside is `""`),
  `TRUE`, `FALSE`.
- **Functions** (names are case-insensitive):

| Group | Functions |
|---|---|
| Math | `SUM`, `PRODUCT`, `AVERAGE` (or `AVG`), `MIN`, `MAX`, `MEDIAN`, `ROUND`, `ROUNDUP`, `ROUNDDOWN`, `INT`, `TRUNC`, `ABS`, `SQRT`, `POWER`, `MOD`, `CEILING`, `FLOOR`, `SIGN`, `EXP`, `LN`, `LOG10`, `LOG`, `PI` |
| Counting | `COUNT`, `COUNTA`, `COUNTBLANK`, `COUNTIF`, `SUMIF` |
| Logic | `IF`, `IFERROR`, `AND`, `OR`, `NOT` |
| Text | `CONCAT`, `CONCATENATE`, `LEN`, `UPPER`, `LOWER`, `PROPER`, `TRIM`, `LEFT`, `RIGHT`, `MID`, `REPT`, `FIND`, `SEARCH`, `SUBSTITUTE`, `TEXTJOIN`, `TEXT`, `VALUE` |
| Lookup | `VLOOKUP`, `HLOOKUP`, `INDEX`, `MATCH` |
| Date | `TODAY`, `NOW`, `DATE`, `YEAR`, `MONTH`, `DAY`, `HOUR`, `MINUTE`, `SECOND`, `WEEKDAY`, `EDATE`, `EOMONTH` |

Anything else shows `#NAME?`. The error codes a cell can show:

| Code | Cause |
|---|---|
| `#DIV/0!` | Division by zero, or an average of nothing. |
| `#VALUE!` | Text where a number was needed. |
| `#REF!` | A reference that cannot exist. |
| `#NAME?` | An unknown function or name. |
| `#CIRC!` | The formula depends on itself. |
| `#ERROR!` | Anything that does not parse, including `Sheet!A1`. |

Formulas read **every** cell they reference, including hidden and filtered-out
rows. A `SUM` under a filtered list totals the whole list, not the visible
part.

### 6.5 The header row

Row 0 is the header. It stays pinned at the top, is never sorted or filtered,
is never painted by a conditional-format rule or marked by validation, and is
the header line of a CSV export. Column types still apply to how it is shown,
so keep header text non-numeric: `"2026"` at the top of a money column shows as
`$2,026.00`.

---

## 7. Editing an existing export

The app stores nothing it can recompute, so an export holds only what the user
typed and chose. Editing it is mostly a matter of not breaking alignment.

**What the app replaces on import:**

- every file `id` and sheet `id` (new ones each time; `activeFile` is moved to
  the new id of the same file);
- `v`, which becomes `2`;
- `exportedAt`, the next time the user exports.

**What to preserve:**

- Everything else, including keys you do not recognize. They survive import
  and export untouched.
- Every cell string exactly as it is. Do not "clean up" `"489.00"` to `489` or
  `"7:00 am"` to `"07:00"`; the first breaks the import, the second is just a
  different way of writing the same value.

**Adding rows and columns.** Append new rows at the bottom and new columns on
the right, and raise `rows` / `cols` to match. **Inserting in the middle by
hand shifts nothing for you.** When the user inserts a row or column in the
app, the app moves every cell, rewrites every formula reference and shifts
every column- and row-keyed setting. A hand edit must do all of that itself:
cell keys, `fmts` keys, A1 references in every formula, `types`, `widths`,
`rowH`, `colFmt`, `validation`, `hiddenRows`, `hiddenCols`, and the `col` or
`range` in every `sort`, `filters` and `cf` entry. Missing one silently points
a setting or a formula at the wrong data.

**Which shape to send back.** An edited full backup replaces everything the
user has when they restore it, including files you did not touch and their
theme. That is right when you have edited their whole export. When you are
adding one new file to their work, send a single-file pack instead.

There are no hashes, device ids, checksums or timestamps inside the workbook
to maintain.

---

## 8. Things that go wrong

| Mistake | What happens |
|---|---|
| A cell value written as a JSON number or boolean (`"1,3": 42.5`) | The import fails with *"Could not read backup: raw.charAt is not a function"*. The broken file may still be half-added in the open session: reload the page before doing anything else, or the next change saves it. Always write strings. |
| `filters` that is not an array (`{}`) | The same failure: *"Could not read backup: s.filters.some is not a function"*, with the same half-added state. |
| A file object with no `file` wrapper | Read as an old-style workbook. It **replaces** everything, and the file's name, accent and settings are lost. |
| Saved as `.txt` (or another extension) without `"app": "pumasheet"` near the start | Imported as CSV: the JSON text appears as rows of text in a new sheet. |
| `"files": []`, or no `workbook`/`file` at all | *"Not a PumaSheet backup"*. |
| Invalid JSON | *"Could not read backup: …"* with the parse error. Nothing changes. |
| A full backup restored onto someone's existing work | Their whole workbook is replaced (after they confirm). |
| Off-by-one between cell keys and A1 references | Formulas quietly compute from the wrong cells. See §6.1. |
| A cell outside `rows` × `cols` | Kept, invisible, and still read by formulas. |
| `rows` above 5000 or `cols` above 52 | Cut down silently; cells beyond are kept but invisible. |
| A date in an `auto` column | Stays text: it does not show in the date format and sorts alphabetically. |
| A date or time with `Z` or an offset | Shifted into the viewer's time zone. |
| `"06/11/2026"` meant as 6 November | Read as June 11. |
| `"€12"` in a money column | Text, not a number. Write `"12"` and set `settings.currency` to `"€"`. |
| `"10%"` in a column that is not `percent` | Text. |
| Cross-sheet reference `=Other!A1` | `#ERROR!`. |
| A date compared in a filter or rule (`"v1": "2026-11-01"`) | Compared as text against the date's day number, so it does not do what it says. Filter and color dates by hand, or not at all. |
| Enum typo (`"type": "currency"`, `"op": "greater"`, `"dateFormat": "uk"`) | Kept. An unknown column type reads like `auto` with no badge; an unknown filter op lets every row through; an unknown rule op never matches; an unknown date format shows as `med`. |
| A scale color as `"#fff"` or `"white"` | Read as black. Use 6-digit hex. |
| A color with other characters (`"url(x)"`, `"#fff;"`) | Ignored; the cell has no color. |
| A validation list that does not include a value the column holds | The value is kept, and marked in red. |
| An `"in"` filter listing raw numbers in a money column | Hides those rows: it matches shown text such as `"$1,850.00"`. |
| A sheet with no `name` | A blank bottom tab. |
| `schema` set to anything | Ignored. |

---

## 9. Checklist before handing a pack over

A pack that passes all of these imports with no warnings and shows exactly
what it says.

**Structure**
- [ ] The file is valid JSON, starts with `"app": "pumasheet"`, and is named
      `.json` or `.pumapack`.
- [ ] It uses one shape: `workbook` (replaces everything) or `file` (adds one
      tab), and you know which the recipient needs.
- [ ] `workbook.files` is a non-empty array; every file has a non-empty
      `sheets` array.
- [ ] Every sheet has every key from §4.3, with `{}` and `[]` for empty
      values and a real `name`.

**Cells**
- [ ] Every value in `cells` is a string. No numbers, no booleans, no nulls.
- [ ] Every key is `"row,col"`, zero-based, with no spaces, and inside
      `rows` × `cols`.
- [ ] Row 0 holds the column titles.
- [ ] Every formula's A1 references point at the intended cells (row number =
      key row + 1) and stay on the same sheet.

**Types and values**
- [ ] Every column that holds numbers, money, percentages, dates, times or
      booleans has that type in `types`.
- [ ] Dates are `YYYY-MM-DD`, times `HH:MM`, date-times `YYYY-MM-DD HH:MM`,
      with no time zone.
- [ ] Money cells are plain numbers; the symbol is in `settings.currency`.
- [ ] Every enum value is one of the exact values in §4.

**Views**
- [ ] Every column or row index in `types`, `widths`, `rowH`, `colFmt`,
      `validation`, `sort`, `filters`, `cf`, `hiddenRows` and `hiddenCols`
      exists.
- [ ] `filters` is an array, and any `"in"` values match the text as shown.
- [ ] Scale colors are 6-digit hex; other colors use only the characters in §4.6.
- [ ] `activeFile` names a file id.

---

## 10. A complete example

A full backup with two files. "Office move" has a budget sheet (money,
per-cell percent, totals, a pick-list and a highlight rule) and a task sheet
(dates, times, a sort, a filter and two rules). "Mileage" has one sheet of trips (date-times, a color scale, a hidden row) in
euros. It imports with no warnings. Because it is a full backup, the user is
asked to confirm, and it replaces whatever they had.

```json
{
  "app": "pumasheet",
  "schema": 2,
  "exportedAt": "2026-10-28T09:00:00.000Z",
  "workbook": {
    "v": 2,
    "files": [
      {
        "id": "f-office",
        "name": "Office move",
        "accent_color": "#3fb8e0",
        "sheets": [
          {
            "id": "sh-budget",
            "name": "Budget",
            "rows": 8,
            "cols": 6,
            "cells": {
              "0,0": "Item", "0,1": "Category", "0,2": "Qty", "0,3": "Unit cost", "0,4": "Total", "0,5": "Ordered",
              "1,0": "Standing desk", "1,1": "Furniture", "1,2": "12", "1,3": "489.00", "1,4": "=C2*D2", "1,5": "TRUE",
              "2,0": "Monitor arm", "2,1": "Furniture", "2,2": "12", "2,3": "79.50", "2,4": "=C3*D3", "2,5": "TRUE",
              "3,0": "Docking station", "3,1": "IT", "3,2": "12", "3,3": "215", "3,4": "=C4*D4", "3,5": "FALSE",
              "4,0": "Movers (half day)", "4,1": "Moving", "4,2": "1", "4,3": "1850", "4,4": "=C5*D5", "4,5": "FALSE",
              "6,0": "Contingency", "6,3": "10%", "6,4": "=SUM(E2:E5)*D7",
              "7,0": "Total", "7,4": "=SUM(E2:E5)+E7"
            },
            "types": { "2": "number", "3": "money", "4": "money", "5": "boolean" },
            "widths": { "0": 200, "1": 120 },
            "rowH": {},
            "fmts": {
              "6,3": { "t": "percent", "dec": 0 },
              "7,0": { "b": 1 },
              "7,4": { "b": 1, "bd": 1 }
            },
            "colFmt": { "2": { "decimals": 0 } },
            "validation": { "1": { "values": ["Furniture", "IT", "Moving"] } },
            "sort": [],
            "filters": [],
            "cf": [
              { "col": 5, "type": "value", "op": "eq", "v1": "FALSE", "v2": "", "bg": "rgba(222,184,40,.32)", "fg": null, "b": 0 }
            ],
            "freezeCol": 1,
            "freezeRows": 1,
            "hiddenRows": [],
            "hiddenCols": []
          },
          {
            "id": "sh-tasks",
            "name": "Tasks",
            "rows": 6,
            "cols": 6,
            "cells": {
              "0,0": "Task", "0,1": "Owner", "0,2": "Due", "0,3": "Call at", "0,4": "Status", "0,5": "Notes",
              "1,0": "Book the movers", "1,1": "Dana", "1,2": "2026-11-06", "1,3": "09:30", "1,4": "Done", "1,5": "Half day, two crew",
              "2,0": "Order desks", "2,1": "Priya", "2,2": "2026-11-02", "2,3": "14:00", "2,4": "In progress",
              "3,0": "Label every crate", "3,1": "Dana", "3,2": "2026-11-13", "3,4": "Open", "3,5": "Use the floor plan codes",
              "4,0": "Move the network rack", "4,1": "Sam", "4,2": "2026-11-12", "4,3": "7:00 am", "4,4": "Blocked", "4,5": "Waiting on building access\nfor the weekend",
              "5,0": "Hand back old keys", "5,1": "Priya", "5,2": "2026-11-20", "5,4": "Open"
            },
            "types": { "2": "date", "3": "time", "4": "text" },
            "widths": { "0": 190, "5": 220 },
            "rowH": { "4": 44 },
            "fmts": {
              "1,4": { "fg": "#9aa3ad" },
              "4,5": { "wrap": 1, "i": 1 }
            },
            "colFmt": {},
            "validation": { "4": { "values": ["Open", "In progress", "Blocked", "Done"] } },
            "sort": [ { "col": 2, "dir": "asc" } ],
            "filters": [ { "col": 4, "op": "in", "values": ["Open", "In progress", "Blocked"] } ],
            "cf": [
              { "col": 4, "type": "value", "op": "eq", "v1": "Blocked", "v2": "", "bg": "rgba(229,72,72,.30)", "fg": null, "b": 0 },
              { "range": { "r1": 1, "c1": 1, "r2": 5, "c2": 1 }, "type": "dup", "bg": null, "fg": "#e05a5a", "b": 1 }
            ],
            "freezeCol": 0,
            "freezeRows": 1,
            "hiddenRows": [],
            "hiddenCols": []
          }
        ],
        "activeSheet": 0,
        "settings": { "currency": "$", "dateFormat": "iso", "decimals": null, "thousands": true }
      },
      {
        "id": "f-mileage",
        "name": "Mileage",
        "accent_color": null,
        "sheets": [
          {
            "id": "sh-trips",
            "name": "Trips",
            "rows": 6,
            "cols": 5,
            "cells": {
              "0,0": "Left at", "0,1": "From", "0,2": "To", "0,3": "km", "0,4": "Claim",
              "1,0": "2026-10-05 08:30", "1,1": "Office", "1,2": "Client A", "1,3": "42.5", "1,4": "=D2*0.3",
              "2,0": "2026-10-09 13:15", "2,1": "Office", "2,2": "Warehouse", "2,3": "18", "2,4": "=D3*0.3",
              "3,0": "2026-10-14 07:45", "3,1": "Home", "3,2": "Client B", "3,3": "96", "3,4": "=D4*0.3",
              "4,0": "2026-10-20 10:00", "4,1": "Office", "4,2": "Cancelled trip", "4,3": "0", "4,4": "=D5*0.3",
              "5,3": "=SUM(D2:D5)", "5,4": "=SUM(E2:E5)"
            },
            "types": { "0": "datetime", "3": "number", "4": "money" },
            "widths": { "0": 160 },
            "rowH": {},
            "fmts": {
              "5,3": { "b": 1, "bd": 1 },
              "5,4": { "b": 1, "bd": 1 }
            },
            "colFmt": {},
            "validation": {},
            "sort": [],
            "filters": [],
            "cf": [
              { "range": { "r1": 1, "c1": 3, "r2": 4, "c2": 3 }, "type": "scale", "lo": "#fff7d6", "hi": "#e05a5a" }
            ],
            "freezeCol": 0,
            "freezeRows": 1,
            "hiddenRows": [4],
            "hiddenCols": []
          }
        ],
        "activeSheet": 0,
        "settings": { "currency": "€", "dateFormat": "med", "decimals": 1, "thousands": false }
      }
    ],
    "activeFile": "f-office",
    "theme": "dark"
  },
  "accent": null
}
```

What the app shows for this file, as a check on your own reasoning. These
values were read off the rendered sheets after importing this exact file:

- **Budget.** Unit costs show as `$489.00`, `$79.50`, `$215.00` and
  `$1,850.00`. The totals in column E are `$5,868.00`, `$954.00`,
  `$2,580.00` and `$1,850.00`. D7 is typed percent with 0 decimals and shows
  `10%`, so the contingency in E7 is `$1,125.20` and the grand total in E8 is
  `$12,377.20`. The two `FALSE` cells in *Ordered* are tinted yellow. Qty
  shows `12`, not `12.00`, because of the column's `decimals: 0`.
- **Tasks.** "Book the movers" is filtered out (its status is Done). The four
  visible tasks are sorted by due date: Order desks (`2026-11-02`, because the
  file's date format is `iso`), Move the network rack, Label every crate, Hand
  back old keys. "Blocked" is tinted red. `7:00 am` shows as `07:00`. Dana and
  Priya are shown red and bold as duplicates. Dana's other row is the
  filtered-out one, which rules still see.
- **Mileage.** The cancelled trip (row 5) is hidden. The first trip shows as
  `Oct 5, 2026 08:30`. km values show one decimal (`18.0`, from the file's
  `decimals: 1`); claims show in euros with two (`€12.75`). The km total in D6
  is `156.5` and the claim total in E6 is `€46.95`; both include the hidden
  row's zero. The km cells above the total are shaded from pale to red.

After import, the stored workbook matches this file exactly except for the
five new file and sheet ids (and `activeFile`, which follows them). Exporting
it again gives back the same file apart from those ids and `exportedAt`.