feat(sheets): support Connected Sheets (BigQuery data sources)

[ryo-touch]

I run several reporting spreadsheets wired to BigQuery via Connected Sheets. Keeping the underlying SQL in sync and forcing a refresh from a script (cron / CI / an agent) currently requires opening the Sheets UI by hand — gog can't touch the data source side of a spreadsheet at all.

The gap

None of the ~35 sheets subcommands touch DataSource, BigQueryDataSourceSpec, or data execution status, and nothing in the docs mentions it either.

It's not just "no write path" — read is barely there either:

• Spreadsheet has top-level dataSources[] and dataSourceSchedules[] fields (per the Sheets API reference), but SheetsMetadataCmd's Fields(...) mask only requests spreadsheetId,title,locale,timeZone,sheets — dataSources is silently dropped.
• The only way to even see a connected sheet's dataSourceId today is an unfiltered gog sheets raw <id> dump. There's no structured list, and no way to inspect dataExecutionStatus to tell whether a refresh succeeded, failed, or is still running.
• There's no command to create a data source, change its query/table, delete it, or force a refresh — so any Connected Sheet has to be built and maintained by hand in the browser.

What already exists to build on

The underlying Sheets API v4 supports the full lifecycle via spreadsheets.batchUpdate:

• AddDataSourceRequest — attach a new BigQuery data source (custom SQL via BigQueryQuerySpec, or a native table via BigQueryTableSpec) and create the linked DATA_SOURCE sheet
• UpdateDataSourceRequest — change an existing data source's spec
• DeleteDataSourceRequest — remove a data source and its linked sheet
• RefreshDataSourceRequest — force/queue a re-execution of the data source's query

All of these types already exist in google.golang.org/api/sheets/v4, the SDK version gogcli is already pinned to (v0.286.0) — this is missing CLI surface, not an SDK gap.

Proposed CLI surface

gog sheets datasource list <spreadsheetId>
gog sheets datasource add <spreadsheetId> --project <gcpProjectId> --query <sql>
gog sheets datasource add <spreadsheetId> --project <gcpProjectId> --table-project <p> --dataset <d> --table <t>
gog sheets datasource refresh <spreadsheetId> <dataSourceId> [--force]
gog sheets datasource delete <spreadsheetId> <dataSourceId>

Note this needs a new OAuth scope (https://www.googleapis.com/auth/bigquery.readonly) added alongside the existing spreadsheets/drive scopes for the sheets service — existing users would need to re-consent.

Ref: spreadsheets.batchUpdate — AddDataSourceRequest / UpdateDataSourceRequest / DeleteDataSourceRequest / RefreshDataSourceRequest; Spreadsheet.dataSources[] / DataSource / BigQueryDataSourceSpec resources.

[steipete]

Thanks for the detailed proposal. The Sheets v4 API and current Go client do expose the Connected Sheets lifecycle, but we are deferring this unless someone sponsors the feature with ongoing ownership and a real Connected Sheets test environment.

This is a larger commitment than adding a few batch-update request types: BigQuery-backed operations require the additional bigquery.readonly OAuth scope and user re-consent, introduce BigQuery authorization and execution-status behavior, and need live proof across add, update, refresh, failure, and deletion paths. That is a fairly niche surface for gog to own without a maintainer who actively uses it.

I am keeping the issue open so a sponsor can step forward. A future proposal should start with a read-only list/status surface, make the extra scope explicitly opt-in, and include repeatable live Connected Sheets validation before adding mutating commands.

[clawsweeper]

Codex review: keeping this open for maintainer follow-up; there is still a little grit to resolve. Reviewed August 5, 2026, 2:17 PM ET / 18:17 UTC.

Summary
Keep open as a sponsor-gated feature request: current main still lacks a structured Connected Sheets surface and the required BigQuery scope, while a collaborator explicitly deferred it pending ongoing ownership and a live Connected Sheets environment.

Reproducibility: not applicable. as a failure reproduction: this is a request for new capability, and current source confirms the structured command and authorization surface are absent.

Ways to help us reproduce this

• Add a screenshot or short recording showing the behavior.
• Add expected vs actual behavior.

Maintainer decision needed

Question	Recommendation
Is there a sponsor willing to own an opt-in Connected Sheets surface and provide a repeatable live BigQuery-backed validation environment?	Sponsor a read-only first step: Approve a narrow opt-in list/status command with a supplied Connected Sheets test environment before considering mutations.

Why: The requested API adds a niche command surface, a new OAuth consent boundary, and provider-specific lifecycle behavior; source inspection cannot choose its long-term support commitment.

Next step
An explicit sponsor, OAuth scope policy, and repeatable live Connected Sheets environment are required before implementation can be safely scoped.

Security
Needs attention: The requested BigQuery read scope expands OAuth consent and needs an explicit opt-in design before implementation.

Review details

Best possible solution:

A sponsor should first define an explicitly opt-in, read-only data-source list/status command with live validation, then decide whether the mutating lifecycle is worth maintaining.

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

Not applicable as a failure reproduction: this is a request for new capability, and current source confirms the structured command and authorization surface are absent.

Is this the best way to solve the issue?

No—the full mutation lifecycle is not the best immediate path; the collaborator-approved direction is a sponsor-owned, opt-in read-only list/status surface backed by live Connected Sheets proof.

AGENTS.md: found and applied where relevant.

Remaining risk / open question:

• A default addition of the BigQuery read scope would expand existing Sheets consent; the feature needs an explicit opt-in and re-consent path.
• The requested add, update, refresh, failure, and deletion lifecycle cannot be safely accepted without a sponsor-owned live Connected Sheets and BigQuery validation environment.

Codex review notes: model internal, reasoning high; reviewed against 27af1477df4a.

Label changes

Label changes:

• add impact:auth-provider: Supporting Connected Sheets requires a BigQuery OAuth scope and user re-consent beyond the current Sheets authorization set.
• add clawsweeper:needs-security-review: Current issue advisory state selects this label.

Label justifications:

• P3: This is a niche new API surface explicitly deferred pending sponsorship rather than a regression in an existing command.
• impact:auth-provider: Supporting Connected Sheets requires a BigQuery OAuth scope and user re-consent beyond the current Sheets authorization set.

Evidence reviewed

Security concerns:

• [low] Scope expansion needs explicit opt-in — internal/googleauth/service.go:214
  Adding BigQuery read access to the normal Sheets authorization set would broaden token permissions for existing users, so consent and re-authorization behavior need maintainer-approved boundaries.
  Confidence: 0.98

What I checked:

• Current command surface: The Sheets command registry has metadata and raw inspection but no datasource command group; raw intentionally returns the full Sheets API response, while structured metadata JSON only emits spreadsheet identity, locale, time zone, and sheets. (internal/cmd/sheets.go:33, 27af1477df4a)
• OAuth boundary remains absent: The Sheets service currently requests Drive and Sheets scopes only, with no BigQuery read scope or Connected-Sheets-specific authorization path. (internal/googleauth/service.go:214, 27af1477df4a)
• Maintainer direction: The collaborator comment at feat(sheets): support Connected Sheets (BigQuery data sources) #938 (comment) explicitly keeps this issue open pending a sponsor with ownership and a real Connected Sheets test environment, and asks for an opt-in read-only list/status starting point.
• Vision fit: VISION.md supports incremental Sheets capabilities but puts niche, hard-to-live-test API surfaces and new command APIs in the discuss-first category; it also requires live Google proof for provider-dependent behavior. (VISION.md:23, 27af1477df4a)
• Recent area history: Recent Sheets work is commit a1637ea by Cathryn Lavery; current main is 27af147 and is not contained in a release tag, with no Connected Sheets implementation found in the inspected command, auth, and docs paths. (internal/cmd/sheets.go:33, a1637ea19c8d)
• Maturity classification unavailable: The required maturity shortlist helper was run, but this checkout has no qa/maturity-scores.yaml scorecard, so no stable-maturity label can be supported.

Likely related people:

• steipete: Posted the explicit sponsor, opt-in scope, and live-validation requirements, and is the recent author of the current-main release/toolchain history sampled for this surface. (role: product-direction owner; confidence: high; commits: 27af1477df4a, 4747fb05a429; files: VISION.md, internal/googleauth/service.go)
• Cathryn Lavery: Authored the recent focused Sheets fix a1637ea, making her a useful implementation-area routing candidate if a sponsor narrows the work. (role: recent Sheets contributor; confidence: medium; commits: a1637ea19c8d; files: internal/cmd/sheets.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.