feat(sheets): data-validation get / set / clear commands

[chrischall]

Summary

There is currently no way to read or write data-validation rules (dropdowns, checkbox, number/date constraints, etc.) on a sheet. gog sheets exposes no validation command, and sheets copy-paste --type DATA_VALIDATION does not round-trip table-managed dropdowns.

Why it matters

• Validations can't be backed up before a destructive op (e.g. before deleting a table — see the table-delete data-loss issue) nor recreated afterward. Dropdown columns survive only as plain text, permanently losing the pickers.
• Any task that must touch a validated column has no non-destructive path.

Requested

• gog sheets validation get <spreadsheetId> <range> — dump the DataValidationRule(s) covering a range.
• gog sheets validation set <spreadsheetId> <range> ... — set a rule (at minimum ONE_OF_LIST with values, plus the common condition types: NUMBER_BETWEEN, DATE_*, BOOLEAN, ONE_OF_RANGE), with --strict / --show-custom-ui toggles.
• gog sheets validation clear <spreadsheetId> <range>.
• Make copy-paste --type DATA_VALIDATION actually capture/restore validations.

These map directly onto the Sheets API setDataValidation / DataValidationRule and the DATA_VALIDATION paste type.

[clawsweeper]

Codex review: keeping this open for maintainer follow-up; there is still a little grit to resolve. Reviewed June 7, 2026, 9:57 PM ET / 01:57 UTC.

Summary
Keep open: current main has partial data-validation copy support, but it does not provide the requested sheets validation get/set/clear surface and the table-managed dropdown round-trip claim still needs live Sheets verification.

Reproducibility: yes. for the command-gap portion: source and generated docs show no current sheets validation get/set/clear command. The table-managed dropdown copy-paste failure is plausible but still needs live Sheets proof.

Next step
This is useful and aligned, but it adds a new Sheets API/CLI surface and needs maintainer direction plus live-provider validation before a repair lane should implement it.

Review details

Best possible solution:

Add a focused sheets validation command family that can JSON-dump rules, set and clear rules via Sheets batchUpdate, preserve parseable output/dry-run behavior, and include unit coverage plus live Google proof for dropdown/table cases.

Do we have a high-confidence way to reproduce the issue?

Yes for the command-gap portion: source and generated docs show no current sheets validation get/set/clear command. The table-managed dropdown copy-paste failure is plausible but still needs live Sheets proof.

Is this the best way to solve the issue?

Unclear until maintainers settle the command shape; a first-class validation subcommand mapping to Sheets data-validation APIs looks maintainable, but the copy-paste/table-dropdown part should be validated against Google before implementation.

AGENTS.md: found and applied where relevant.

Remaining risk / open question:

• The requested set command needs maintainer direction on CLI syntax, JSON shape, and which condition types are in scope for the first implementation.
• The claim that copy-paste --type DATA_VALIDATION cannot round-trip table-managed dropdowns needs live Google Sheets proof before choosing an API workaround.
• This is adjacent to the open table-delete data-loss report, but it is not a replacement for fixing destructive table deletion behavior.

Codex review notes: model gpt-5.5, reasoning high; reviewed against 197992a09f3d.

Label changes

Label changes:

• add P2: This is a normal-priority Sheets capability gap with meaningful backup/restore impact but no current core crash or urgent regression.
• add impact:data-loss: The issue is specifically about losing validation metadata and dropdown pickers during destructive or restorative Sheets workflows.
• add issue-rating: 🦞 diamond lobster: Current issue advisory state selects this label.
• add clawsweeper:source-repro: Current issue advisory state selects this label.
• add clawsweeper:no-new-fix-pr: Current issue advisory state selects this label.
• add clawsweeper:needs-maintainer-review: Current issue advisory state selects this label.
• add clawsweeper:needs-product-decision: Current issue advisory state selects this label.

Label justifications:

• P2: This is a normal-priority Sheets capability gap with meaningful backup/restore impact but no current core crash or urgent regression.
• impact:data-loss: The issue is specifically about losing validation metadata and dropdown pickers during destructive or restorative Sheets workflows.

Evidence reviewed

Acceptance criteria:

• go test ./internal/cmd -run 'TestSheets.*Validation|TestSheetsCopyPaste'
• make fmt
• make lint
• Live Google Sheets proof for get/set/clear and table-managed dropdown copy/restore behavior with private spreadsheet IDs redacted

What I checked:

• Repository policy read: AGENTS.md was read fully; its parseable-output and testing guidance applies to any future CLI implementation. (AGENTS.md:18, 197992a09f3d)
• Current Sheets command tree lacks validation command: The SheetsCmd subcommands include copy-paste, table, raw, formatting, and related commands, but no validation command or get/set/clear validation subcommands. (internal/cmd/sheets.go:29, 197992a09f3d)
• Generated docs also lack validation subcommand: The generated gog sheets command index lists current Sheets subcommands and has no validation command, while documenting only the existing copy-paste command. (docs/commands/gog-sheets.md:17, 197992a09f3d)
• Existing copy-paste has partial DATA_VALIDATION support: sheets copy-paste accepts DATA_VALIDATION and sends a CopyPasteRequest, which covers part of the request but not rule inspection or explicit set/clear. (internal/cmd/sheets_copy_paste.go:19, 197992a09f3d)
• Existing update/append validation copying is narrow: sheets update and sheets append expose --copy-validation-from and call copyDataValidation, but this only copies validation from a source range after value writes. (internal/cmd/sheets.go:172, 197992a09f3d)
• Validation helper uses paste-copy only: copyDataValidation builds a CopyPasteRequest with PASTE_DATA_VALIDATION; there is no SetDataValidationRequest implementation here. (internal/cmd/sheets_validation.go:31, 197992a09f3d)

Likely related people:

• chrischall: Authored the merged copy-paste PR that added the current DATA_VALIDATION paste-type surface and opened this follow-up request about its remaining gaps. (role: prior feature contributor and reporter; confidence: high; commits: 6b2a0dfa554b; files: internal/cmd/sheets_copy_paste.go, docs/commands/gog-sheets-copy-paste.md)
• mahmoudashraf93: Authored the merged --copy-validation-from work that created the existing range-copy validation helper used by update/append. (role: introduced related behavior; confidence: high; commits: 46d6276f6ac8, ad2a4df73692; files: internal/cmd/sheets.go, internal/cmd/sheets_validation.go, internal/cmd/sheets_update_validation_test.go)
• steipete: Merged the related copy-paste and copy-validation work, authored table basics, and appears in local blame for the current shallow checkout’s Sheets files. (role: recent area contributor and merger; confidence: medium; commits: 14d3cc9ef8a7, 53d7eb316cb5, 9738b315ff02; files: internal/cmd/sheets.go, internal/cmd/sheets_validation.go, internal/cmd/sheets_table_columns.go)

How this review workflow works

• ClawSweeper keeps one durable marker-backed review comment per issue or PR.
• Re-runs edit this comment so the latest verdict, findings, and automation markers stay together instead of adding duplicate bot comments.
• A fresh review can be triggered by eligible @clawsweeper re-review comments, exact-item GitHub events, scheduled/background review runs, or manual workflow dispatch.
• PR/issue authors and users with repository write access can comment @clawsweeper re-review or @clawsweeper re-run on an open PR or issue to request a fresh review only.
• Maintainers can also comment @clawsweeper review to request a fresh review only.
• Fresh-review commands do not start repair, autofix, rebase, CI repair, or automerge.
• Maintainer-only repair and merge flows require explicit commands such as @clawsweeper autofix, @clawsweeper automerge, @clawsweeper fix ci, or @clawsweeper address review.
• Maintainers can comment @clawsweeper explain to ask for more context, or @clawsweeper stop to stop active automation.

[chrischall]

Live verification of the table-managed-dropdown round-trip claim (on gog v0.22.0).

Result: confirmed — copy-paste --type DATA_VALIDATION does not round-trip a table-managed dropdown. And the root cause is clear: a table's dropdown rule is stored at the table column level, not as per-cell dataValidation, so there is nothing at the cell level for the paste to copy.

Setup — a table with a DROPDOWN column (note the column needs a dataValidationRule.condition, otherwise addTable 400s with "Condition must be set for dropdown column type"):

gog sheets table create "$SID" "Sheet1!A1:B4" --name=DV --columns-json='[
  {"columnName":"Name","columnType":"TEXT"},
  {"columnName":"Status","columnType":"DROPDOWN","dataValidationRule":{"condition":{"type":"ONE_OF_LIST","values":[{"userEnteredValue":"Open"},{"userEnteredValue":"Closed"}]}}}
]'

Where the rule actually lives (from gog sheets raw --include-grid-data):

=== per-cell dataValidation in the Status (B) column ===
  (none — no per-cell dataValidation)

=== sheet.tables column config ===
  table DV, col Status DROPDOWN rule:
    {"condition":{"type":"ONE_OF_LIST","values":[{"userEnteredValue":"Open"},{"userEnteredValue":"Closed"}]}}

Then the paste:

$ gog sheets copy-paste "$SID" "Sheet1!B2:B4" "Sheet1!D2:D4" --type DATA_VALIDATION
  { "source": "Sheet1!B2:B4", "type": "PASTE_DATA_VALIDATION" }   # reports success

# re-read D2:D4 grid data:
  (none — dropdown was NOT copied to D2:D4)

So the paste succeeds (PASTE_DATA_VALIDATION) but the destination receives no validation, because the source cells carry no cell-level dataValidation — the rule is held by the table object.

Implication for this issue: the requested sheets validation get/set/clear surface would let callers read/write cell-level rules directly. For the table case specifically, either copy-paste DATA_VALIDATION would need to be table-aware (resolve a cell's effective rule from its enclosing table column), or validation get should surface the effective rule including table-managed ones.

Caveat: I could not test round-tripping a regular (non-table) cell validation, because there is currently no command to create one — which is exactly the validation set capability this issue requests. The table-managed case above is the part I could reproduce, and it is confirmed broken.