zs-cli

# zs-cli — Zoho Sheet CLI Skill ## Overview `zs-cli` is a command-line interface that wraps the Zoho Sheet spreadsheet engine. It lets you create, read, write, and compute spreadsheets from the terminal, a script, or an AI agent — on Windows, macOS, and Linux. **Key principle**: Use `zs-cli` to manipulate the data, structure, formulas, and formatting of a spreadsheet — entirely from the command line. Always apply the Zoho Sheet green theme and formatting rules defined below. --- ## Design Guidelines & Formatting Rules > These rules apply to **every spreadsheet** produced by this skill unless the user explicitly overrides them. They are not optional — apply them unconditionally on every create or edit task. --- ### Zoho Sheet Green Theme — Mandatory All spreadsheets must follow the Zoho Sheet green colour palette. Never use Office Blue, grey corporate, or any other colour scheme. #### Hardcoded Colour Values | Element | Role | Hex | RGB | |---|---|---|---| | `#1A7346` | Primary header background | `#1A7346` | `26, 115, 70` | | `#FFFFFF` | Header text | `#FFFFFF` | `255, 255, 255` | | `#2E9E63` | Section / sub-header background | `#2E9E63` | `46, 158, 99` | | `#FFFFFF` | Section header text | `#FFFFFF` | `255, 255, 255` | | `#D6F0E0` | Alternate row fill (even rows) | `#D6F0E0` | `214, 240, 224` | | `#FFFFFF` | Alternate row fill (odd rows) | `#FFFFFF` | `255, 255, 255` | | `#1A7346` | Total / summary row background | `#1A7346` | `26, 115, 70` | | `#FFFFFF` | Total / summary row text | `#FFFFFF` | `255, 255, 255` | | `#A8D5B5` | Highlighted / input cell background | `#A8D5B5` | `168, 213, 181` | | `#0D3D26` | Input cell text | `#0D3D26` | `13, 61, 38` | | `#E8F7EE` | Chart / panel background tint | `#E8F7EE` | `232, 247, 238` | | `#145C38` | Border / divider colour | `#145C38` | `20, 92, 56` | | `#FF4C4C` | Error / negative value text | `#FF4C4C` | `255, 76, 76` | | `#0D3D26` | Default body text | `#0D3D26` | `13, 61, 38` | Apply these using `format fontcolor` and `format` background commands. Never substitute approximate or similar greens — use the exact hex values above. --- ### Typography Rules - **Font**: Use the default Zoho Sheet font (do not set a custom font unless the user asks) - **Header row font size**: 11 - **Body font size**: 10 - **Bold**: All header rows and total/summary rows — `format bold <range> true` - **Italic**: Use only for footnotes, disclaimers, or secondary labels — never for data cells - **All-caps headers**: Write header text in Title Case, not ALL CAPS --- ### Structure & Layout Rules - **Title row** (merged, full-width, e.g. `A1:L1`) — bold, `#1A7346` background, `#FFFFFF` text. This is the sheet/report title, NOT the table header. - **Table header row** — whatever row is the top row of the range passed to `table create <range> --headers` (this is often row 2, 4, or 5, depending on how many title/spacer rows precede it — never assume it's row 1). This row gets its OWN `#1A7346` background + `#FFFFFF` bold text, applied to its actual row number, not `A1:Z1`. - **Never conflate the two.** A merged title bar and a table header are different rows with the same color treatment — each must be targeted explicitly by its real row number. - **Freeze row 1** (and column A if the sheet has row labels) on every sheet — `freeze B2` - **Alternate row banding**: even rows get `#D6F0E0`, odd rows stay `#FFFFFF` - **Total / summary rows**: `#1A7346` background, `#FFFFFF` bold text — placed at the bottom of each data block - **Input / editable cells**: `#A8D5B5` background so users can instantly identify where to type - **Column widths**: always use `col resize <col> --auto` — never leave columns at default width - **Merged cells**: use only for sheet titles spanning the full width (row 1 title bar) — never merge data cells. **Always merge before formatting** — if you format first and merge second, the formatting applies only to the top-left cell - **Sheet tabs**: name every sheet clearly (no "Sheet1", "Sheet2") — tab names in Title Case --- ### Table Styling Rules When creating tables with `table create`: - Always pass `--headers` so the first row is treated as a header - **Immediately rename** the table after creation — never rely on the `TableN` auto-name beyond the rename line - Table names are auto-assigned **globally across the workbook** (`Table1`, `Table2`…), not per-sheet — count all `table create` lines from top to bottom to predict the correct auto-name - Apply `table style <name> 1` (Light2 — green-tinted light style) as default - Enable banded rows: `table options <name> 2 true` - Enable header row: `table options <name> 0 true` - Disable filter button on decorative tables: `table options <name> 6 false` - For summary tables, enable total row: `table options <name> 1 true` Standard table creation pattern: ``` table create A5:H10 --headers table rename Table1 EmpRoster table style EmpRoster 1 table options EmpRoster 0 true table options EmpRoster 2 true table options EmpRoster 6 false ``` --- # Header row of this table is row 5 (top of A5:H10) — format it explicitly, don't rely on A1:Z1 format fillcolor A5:H5 26 115 70 format fontcolor A5:H5 255 255 255 --- ### Formatting Application Order When building any spreadsheet, apply formatting in this exact order after all data and formulas are written: 1. Merge title/section heading rows first (`merge A1:L1`) 2. Freeze panes (`freeze B2`) 3. Header row: bold + `#1A7346` background + `#FFFFFF` font colour 4. Section/sub-header rows: `#2E9E63` background + `#FFFFFF` font colour 5. Alternate row banding across all data rows 6. Total/summary rows: `#1A7346` background + `#FFFFFF` bold font 7. Input cells: `#A8D5B5` background + `#0D3D26` font colour 8. Table style (if using `table create`) 9. Auto-size rows: `row resize <n> --auto` for every content row 10. Auto-size columns: `col resize <letter> --auto` for every used column > **Critical ordering:** Merge before formatting — if you format first and merge second, the > formatting applies only to the top-left cell, not the full merged area. Auto-size must be last > because it reads rendered content; running it before content/merge is set gives wrong sizes. The correct end-of-sheet pattern (repeat for every sheet section): ``` # 1. Merge all title/section heading rows merge A1:L1 merge A4:L4 # 2. Apply all formatting (bold, fontcolor, fillcolor, borders, etc.) format bold A1:L1 true format fontcolor A1:L1 255 255 255 # 3. Auto-height every row that has content row resize 1 --auto row resize 2 --auto row resize 3 --auto # 4. Auto-width every column used col resize A --auto col resize B --auto col resize C --auto ``` --- ### Mandatory Formatting Script — Run This After Every Build > ⚠️ **This is not optional.** After writing all data and formulas, you MUST execute these > formatting commands before saving. No spreadsheet may be saved without completing this block. > Adapt the ranges to match the actual sheet dimensions. ``` # ── Merge title rows (MUST come before formatting) ──────────────────────────── merge A1:L1 # Merge first — formatting applied after # ── Freeze panes ───────────────────────────────────────────────────────────── freeze B2 # ── Header row (row 1) ──────────────────────────────────────────────────────── format bold A1:Z1 true format fontsize A1:Z1 11 format fillcolor A1:Z1 26 115 70 format fontcolor A1:Z1 255 255 255 # #FFFFFF white text # ── Title row (row 1, merged full-width — the sheet title, not the table header) ── format fillcolor A1:Z1 26 115 70 format fontcolor A1:Z1 255 255 255 # #FFFFFF white text # ── Table header row (top row of the range used in `table create <range> --headers`) ── # ⚠️ Replace <headerRow> below with the ACTUAL row number where the table header lives. # This is NOT always row 1 — count title/spacer rows first. Example: if table create # was `table create A5:L20 --headers`, the header row is 5, so use A5:Z5. format fillcolor A<headerRow>:Z<headerRow> 26 115 70 format fontcolor A<headerRow>:Z<headerRow> 255 255 255 # #1A7346 green text # ── Body text ───────────────────────────────────────────────────────────────── format fontsize A2:Z500 10 format fontcolor A2:Z500 13 61 38 # #0D3D26 dark green body text # ── Section / category label rows (bold, slightly larger) ──────────────────── # Example: row 2 is "INCOME", row 8 is "EXPENSES" — bold these format bold A2 true format fontsize A2 11 format fontcolor A2 255 255 255 # #FFFFFF — section labels in white # ── Total / summary rows — bold with emphasis ───────────────────────────────── # Replace the row numbers below with your actual total rows format bold A6:Z6 true # e.g. Total Income row format fontcolor A6:Z6 255 255 255 # #FFFFFF format bold A22:Z22 true # e.g. Total Expenses row format fontcolor A22:Z22 255 255 255 # #FFFFFF # ── Input / editable cells — highlight so users know where to type ──────────── format fontcolor B3:B5 13 61 38 # #0D3D26 on input cells format fontcolor B9:B21 13 61 38 # ── NET / summary row at bottom ─────────────────────────────────────────────── format bold A24:Z24 true format fontsize A24:Z24 11 format fontcolor A24:Z24 26 115 70 # #1A7346 green on white for NET row # ── Error / negative values — apply after data is loaded ───────────────────── # Use conditional logic in your script to detect negatives and apply: # format fontcolor <cell> 255 76 76 # #FF4C4C red for negatives # ── Auto-size rows (MUST be after merge + formatting) ───────────────────────── row resize 1 --auto row resize 2 --auto # ... repeat for every row with content # ── Auto-size columns (MUST be last — reads final rendered content) ─────────── col resize A --auto col resize B --auto col resize C --auto col resize D --auto col resize E --auto # ... repeat for every column with content ``` **After running the above**, tell the user: > "The spreadsheet data and structure are complete. Column widths, row heights, font sizes, > and font colours have been applied via zs-cli. To complete the green fill colours > (header background `#1A7346`, banded rows `#D6F0E0`, section rows `#2E9E63`) open the file > in Zoho Sheet — background fill is a visual operation that zs-cli applies through Zoho Sheet's > engine on open." --- ### Column Width Reference > **Prefer `col resize <col> --auto` in all cases.** The values below are fallback minimums > only when `--auto` cannot be used. `col resize` uses character units (1 unit ≈ 2.5 px) — > manual pixel-like values produce truncated columns. | Column role | Minimum (character units) | |---|---| | Label / category (col A) | 88 | | Numeric data columns | 52 | | Percentage columns | 48 | | Date columns | 44 | | Notes / description columns | 80 | | ID / code columns | 36 | ### Row Height Reference > **Prefer `row resize <row> --auto` in all cases.** Use fixed values only when you need > explicit spacing control (e.g., spacer rows). | Row role | Height | |---|---| | Primary header (row 1) | `--auto` | | Section label rows | `--auto` | | Data rows | `--auto` | | Total / summary rows | `--auto` | | Spacer rows | 8 | --- ### Restrictions - **Never use red backgrounds** — red (`#FF4C4C`) is reserved for text only (errors / negatives) - **Never use yellow highlights** — not part of the Zoho green theme - **Never use blue text** — blue is an Office/Excel convention; this is a Zoho Sheet product - **Never leave header cells unformatted** — every sheet must have a formatted header row before saving - **Never use default grey table styles** — always apply a green-family table style - **Never hardcode colours outside the palette** — if a colour isn't in the table above, don't use it - **Never leave columns at default width** — always `col resize --auto` every column that contains data - **Never leave rows at default height** — always `row resize --auto` header, section, and total rows - **Never use `format wrap`** — deprecated; use `format textwrap <range> <mode>` instead - **Never use manual col resize values** — use `--auto` unless `--auto` is provably wrong for the case - **Never format before merging** — merge first, then apply formatting to the merged range - **Never auto-size before content is complete** — `--auto` reads rendered content; run it last - **Zero formula errors** — every file must be saved with zero `#REF!`, `#DIV/0!`, `#VALUE!`, `#N/A`, or `#NAME?` errors - **No empty sheet names** — rename every sheet before saving - **Preserve existing theme if editing** — if the user uploads a file that already uses the green theme, match it exactly; do not re-apply defaults that would overwrite their customisation --- ## Table vs. Normal Formatting — How to Pick the Commands > This section decides **which zs-cli commands you reach for**. It is the branch point between > `table create` (a real structured Table object) and plain `cell set` + `format` (a styled > range that merely *looks* like a table). Read this before building any sheet — the choice > changes the commands, the maintenance story, and what downstream tools can do with the data. ### Core principle **Use a Table when the data behaves like a dataset.** A dataset is a set of homogeneous rows (records) under a fixed set of columns (fields) that you will add to, sort, filter, aggregate, or feed into another tool. If that describes the data, create a real Table object with `table create … --headers`. If the data is just a static visual arrangement of values — a report, a layout, a one-time calculation — plain cell formatting is the correct and lighter-weight choice. When in doubt, ask: *"Is this a list of records, or a picture?"* ### When normal formatting is sufficient Reach for plain `cell set` + `format` (no `table create`) in these cases: - **Static reports** — a finished month-end summary or printout that won't gain rows. The shape is fixed; you only need headers, banding, and totals for readability. - **Small temporary lists** — a five-row scratch list that won't be filtered or reused. Wrapping it in a Table object is overhead with no payoff. - **One-off calculations** — a quick `=SUM` / `=AVERAGE` block answering a single question. The values are read once and discarded. - **Dashboard / presentation areas** — KPI tiles, headline numbers, chart captions, title bars, and merged heading blocks. These are *layout*, not records, and a Table would fight the layout (Tables forbid merged cells inside their range and force a rigid one-record-per-row grid). For all of the above use: `cell set`, `format bold`, `format fontcolor`, `format numformat`, `format border`, `merge`, and `freeze` — but **not** `table create`. ### When a Table should be used Create a real Table (`table create … --headers`, then `table rename`, `table style`, `table options`) whenever any of these is true: - **Growing datasets** — rows are added over time (logs, entries, transactions). Tables auto-expand to absorb new rows, so formatting and formulas extend without manual re-ranging. - **Data requiring filtering / sorting** — Tables ship a header filter button (`table options <name> 6 true`) and integrate cleanly with `sort` and `filter create`. - **Repeated analysis** — the same data is re-summarised regularly; a stable Table is a reliable anchor for those summaries. - **Pivot tables and charts** — pivots and charts bound to a Table refresh automatically as the Table grows, instead of pointing at a stale fixed range. - **Power Query / BI integrations** — external tools (Power Query, Power BI, connectors) consume named Tables far more reliably than bare `A2:F500` ranges. - **Automatic formula propagation** — a calculated column added to a Table fills down every row and stays filled as rows are added — no copy-paste maintenance. - **Structured references** — formulas can read `=SUM(Sales[Amount])` instead of `=SUM(C2:C500)`, which is self-documenting and survives inserted rows/columns. Pair with `name add` where useful. ### Normal Range vs. Table — comparison | Dimension | Normal Range (styled cells) | Table (`table create`) | |---|---|---| | **Best for** | Static reports, layouts, one-off math, dashboards | Datasets that grow, get filtered, sorted, or analysed | | **Benefits** | Total layout freedom; supports merged title bars and mixed blocks; minimal command overhead | Auto-expanding range; built-in filter; banded rows via one toggle; structured references; pivot/chart/BI friendly; formulas auto-fill | | **Limitations** | No auto-expand (new rows fall outside formatting/formulas); no structured references; BI tools see only a static range | No merged cells inside the range; rigid one-record-per-row grid; mixing layout/labels inside breaks the model | | **Maintenance effort** | High once data grows — every new row needs re-ranging, re-banding, re-copying formulas | Low — new rows inherit formatting and formulas automatically; downstream pivots/charts refresh on their own |