Google Sheets

catalog.spreadsheet.tab

One Google Sheets catalog is one Drive folder. Every spreadsheet directly inside the folder becomes a schema named after the file, and every tab becomes a table named after the tab:

sales_sheets              the catalog: a Drive folder
└── Q1 2026               a schema: the spreadsheet's file name
    ├── Orders            a table: a tab
    └── Pipeline          another tab of the same file

Subfolders are not followed. If your spreadsheets live in several folders, create one catalog per folder.

What you need

Only the service account's email is kept in the clear, so you can see which identity a catalog runs as; the private key goes to the secrets manager.

A spreadsheet link (…/spreadsheets/d/…) is refused — the catalog is the folder, not the file. The folder must be shared with the service account's address: Viewer to query, Editor to export into a tab.

Google side, step by step

These steps are done once, by whoever owns the folder.

1. Enable the Drive and Sheets APIs in a Google Cloud project. Any project works, including one you already use for BigQuery: Drive API (opens in a new tab), Sheets API (opens in a new tab).

2. Create a service account. In IAM & Admin → Service accounts, create one; the name is cosmetic. It needs no project-level role — it reaches your data through the Drive share in step 4, not through IAM.

3. Create a JSON key. Open the service account → Keys → Add key → Create new key → JSON. The file downloads once: that is the credentials JSON you paste into Thunderduck.

4. Share the folder in Google Drive with the service account's address, which looks like [email protected] (the client_email in the JSON). Give it Viewer to read, Editor to also export into tabs. Uncheck Notify people — a service account has no inbox.

   • In a Shared Drive, add the address as a member of the Shared Drive itself (Viewer, or Contributor to write), or allow sharing with non-members and share the folder.
   • In a Google Workspace organization, an admin policy may block sharing with an address outside the domain. That one account has to be allowed.

5. Create the catalog. Name it (lowercase letters, digits and underscores), paste the folder link and the JSON. Collection starts immediately, so a folder that was not shared with the service account comes back marked offline with the reason, instead of failing at your first query.

There is no OAuth screen and no per-user consent: the catalog runs as the service account, so everyone in the company who can read the catalog reads exactly the files shared with that address.

Looking after the key

The JSON can read — and, with Editor, write — every folder shared with the service account. Treat it like a password.

• Rotate: create a second key, update the catalog's Credentials JSON, then delete the old key in Google Cloud.
• Revoke: un-share the folder, or delete the key or the service account. Catalogs using it go offline at the next refresh.

Querying a tab

SELECT * FROM sales_sheets."Q1 2026".Orders;

Quote a segment when the Drive name contains spaces or dots — "Q1 2026", "Sales.EU". Matching is case-insensitive either way.

-- filter and aggregate
SELECT region, sum(amount) AS revenue
FROM sales_sheets."Q1 2026".Orders
WHERE order_date >= DATE '2026-01-01'
GROUP BY region
ORDER BY revenue DESC;
 
-- join a sheet with a database table
SELECT o.order_id, o.amount, c.email
FROM sales_sheets."Q1 2026".Orders o
JOIN crm.public.customers c ON c.id = o.customer_id;

The data is live. Each query reads the tab from Google when it runs, so an edit in the spreadsheet shows up on the next query — no refresh needed. Only the names and column list are cached; that is what a refresh updates.

Column names and types come from the sheet

Row 1 holds the header, and the first data row decides each column's type. That is the same rule DuckDB's gsheets extension applies when it reads the tab, so the Tables page matches what a query returns.

• Numbers are always DOUBLE, ids included. Cast when you need an integer: CAST(id AS BIGINT).
• A tab with only a header, and no data row yet, is collected with every column as VARCHAR: there is no value to infer from.
• The header must be row 1.

Refreshing after a change in Drive

A new spreadsheet, a renamed spreadsheet or tab, or a changed header is picked up by refreshing the catalog (the refresh button on its group in Tables, or the scheduled refresh). Until then, the old names are all that can be addressed.

Unknown Google Sheets table "…" means the name is not in the cache yet: check the spelling, refresh, retry.

Reading is read-only

A Sheets catalog is not an attached database: there is no USE, no CREATE TABLE, no INSERT or UPDATE, and a bare catalog.tab reference is refused because a tab name alone does not say which spreadsheet it belongs to. Writing goes through exports, below.

Exporting into a tab

COPY (SELECT region, sum(amount) AS revenue
      FROM sales_sheets."Q1 2026".Orders
      GROUP BY region ORDER BY revenue DESC)
TO 'sales_sheets/Q1 2026/Revenue by region' (FORMAT gsheet);

Exactly three segments: the catalog, the spreadsheet, then the tab.

• The spreadsheet must already exist in the folder. The tab does not: CREATE_IF_NOT_EXISTS true is applied unless you say otherwise.
• An existing tab is replaced, header included, because OVERWRITE_SHEET defaults to true.
• This is the same COPY (…) TO … form as a file export, with Sheets names instead of a path. A tab title cannot contain /.
• Requires write access to the catalog in Thunderduck, and Editor on the folder. A catalog marked read-only refuses exports.

Writing a tab per period

The tab segment renders the usual export templates, so a scheduled job can name one tab per day or month:

COPY (SELECT * FROM sales_sheets."Q1 2026".Orders WHERE order_date = current_date - 1)
TO 'sales_sheets/Q1 2026/Daily{now,"yyyyMMdd"}' (FORMAT gsheet);

Templates are always UTC. Only the tab renders: the spreadsheet segment has to name a file that exists, so {today} there stays literal text.

Options

Set at most one of OVERWRITE_SHEET and OVERWRITE_RANGE. With both overwrite flags false, rows are appended to the range instead of clearing it:

COPY (SELECT * FROM sales_sheets."Q1 2026".NewOrders)
TO 'sales_sheets/Q1 2026/Orders'
(FORMAT gsheet, RANGE 'B2:F', OVERWRITE_RANGE true, HEADER false);

Limits

• 500 spreadsheets per folder. Beyond that the rest are skipped and the catalog logs a warning; split large collections into several folders.
• One service account per statement. The extension resolves a single credential, so a query that touches two Sheets catalogs with different service accounts is refused. Two folders shared with the same account are fine.
• Tab titles with ! are read by passing the title through; a title that contains both ! and a quote cannot be addressed by the extension and is refused by name. Rename that tab.
• No new spreadsheets. An export writes tabs inside a spreadsheet that already exists; creating the file is a Drive action this catalog does not do.

Troubleshooting

See Exporting files for templates, scheduling and the other destinations, and Catalogs for how addressing differs per type.