Google Sheets

Google Sheets#

Querona connects to Google Sheets with a built-in provider. One connection covers as many spreadsheets as you list: each becomes a schema inside one virtual database, and its tabs become that schema’s tables — the same shape the Excel provider uses, where a workbook is a schema and a worksheet is a table. Because they share a database, you can join across spreadsheets in a single query. The connection is read-only.

SELECT id, name, price FROM [Sales 2026].[Sheet1] WHERE price > 100;

Note

There is no SQL push-down — Google Sheets has no SQL surface. Querona reads a whole tab and evaluates every filter, join and aggregate itself, so a WHERE clause narrows the result but not the amount of data fetched from Google.

Prerequisites#

Querona connects as a Google service account. If you do not already have one and its JSON key, follow How to create a Google service account and its JSON key first — it walks through creating the account, enabling the API, downloading the key, and handling it safely.

Three things must be true on the Google side, or every query fails:

  1. The Google Sheets API must be enabled on the Google Cloud project that owns the service account you connect with — console.cloud.google.com/apis/library/sheets.googleapis.com.

  2. You need the service account’s JSON key file — downloaded from APIs & Services → Credentials in the Google Cloud console, then <your service account> ‣ KEYS ‣ ADD KEY ‣ Create new key with key type JSON. Google offers that file once, at creation: it cannot be downloaded again, only replaced by a new key. This step does not apply if the engine authenticates through Application Default Credentials instead — see Authentication below.

  3. Every spreadsheet you list must be shared with the service account’s email address (the client_email field of its JSON key), Viewer is enough. Sharing is per file — adding a spreadsheet to an existing connection means sharing that one too.

Important

Forgetting to share a spreadsheet is by far the most common failure, and it surfaces as an HTTP 403. The service account can never own or create a spreadsheet of its own — an existing spreadsheet always has to be shared with it first. Testing the connection checks every spreadsheet in the list, so an unshared one is reported before you import anything.

Important

The service account needs no IAM roles — grant none. A spreadsheet is a Google Workspace asset rather than a Cloud resource, and per Google’s own guidance IAM roles configured in the Google Cloud console do not grant access to Workspace assets such as Sheets. Giving the account project roles like Viewer, Editor or Owner buys no access to any spreadsheet at all; it only widens what that account can do elsewhere in the project. Sharing the file is the whole mechanism.

Warning

The JSON key is a long-lived credential — anyone holding the file can act as the service account until the key is deleted. Never commit it to source control, and rotate it on a schedule.

Connection setup#

Go to the CONNECTIONS section and click ADD CONNECTION, then pick the Google Sheets provider. The provider form carries the read options only, and every one of them is optional — see Configuration options below.

The spreadsheets themselves go on the connection’s Source URIs screen, opened from the provider-parameters screen — the same shared data-sources screen the File provider uses for its files. Add one entry per spreadsheet, pasting the URI straight from your browser’s address bar — or click IMPORT and paste the whole list at once, separated by semicolons or line breaks. See also Create a connection.

EXEC sp_addlinkedserver
     @server = 'gs',
     @srvproduct = 'Google Sheets',
     @provstr = '{"dataSourcesConfiguration":"[{\"uriPath\":\"https://docs.google.com/spreadsheets/d/<spreadsheet-id>/edit\"},{\"uriPath\":\"https://docs.google.com/spreadsheets/d/<another-spreadsheet-id>/edit\"}]"}';

CREATE DATABASE [sheetdb] WITH CONNECTION = [gs];

EXEC sp_import_metadata @database = 'sheetdb';

USE sheetdb;
SELECT id, name, price FROM [Sales 2026].[Sheet1] WHERE price > 100;

This example authenticates through Application Default Credentials. To authenticate through a database scoped credential instead, see the exact sequence below — the credential lives inside sheetdb, so it can only be created after the database and must exist before the metadata import.

dataSourcesConfiguration lists the spreadsheets the connection exposes — one schema each, with that spreadsheet’s tabs as its tables. It is the engine’s shared data-source list, the same setting the File provider fills with file and folder URIs: a JSON array of objects, each carrying one uriPath. Paste a spreadsheet’s link exactly as your browser’s address bar shows it — Querona takes the spreadsheet id out of it for you, so the /edit, #gid= and ?usp=sharing decorations do no harm. A link is required: a bare spreadsheet id on its own is rejected, as are links to Drive folders, to other kinds of Google document, and published-to-web addresses. The provider cannot list or browse spreadsheets (the Sheets API has no such operation), so the list is always yours to supply.

Note

@provstr is a JSON object whose values are strings, so the list is a JSON array inside a string — its own quotes escaped as \", exactly as in the example above. The GUI writes that escaping for you.

For a connection over a single spreadsheet, @datasrc is a shorthand — give it that spreadsheet’s URI and it seeds a one-entry list for you:

EXEC sp_addlinkedserver
     @server = 'gs',
     @srvproduct = 'Google Sheets',
     @datasrc = 'https://docs.google.com/spreadsheets/d/<spreadsheet-id>/edit';

Each spreadsheet becomes one schema, named after the spreadsheet’s own title, normalized to fit the 128-character sysname limit. Rename that schema in Querona whenever the title makes an awkward one: the new name survives every later metadata import, because a schema is keyed internally by the spreadsheet’s permanent id rather than by the name on show. The same key means retitling a workbook in Google Drive neither renames nor forks its schema, and two spreadsheets that happen to share a title stay two separate schemas.

Note

The schema is never dbo, so qualify your queries — [<spreadsheet>].[<tab>] — or set the database’s default schema.

Authentication#

A connection authenticates as a Google service account, in one of two ways:

  • A DATABASE SCOPED CREDENTIAL holding the service account’s JSON key, named through the credential configuration key (see below). To obtain that key, see How to create a Google service account and its JSON key.

  • Application Default Credentials when no credential is named — the ambient Google credentials of the machine running the Querona engine (for example GOOGLE_APPLICATION_CREDENTIALS, or on a Google Cloud VM the instance’s attached service account). This avoids handling a key file at all, at the cost of every connection on that engine sharing one identity.

A credential belongs to exactly one virtual database, and the provider resolves it on the database that owns the connection — so the order matters: create the linked server (naming the credential), create the database, create the credential inside that database, and only then import metadata:

EXEC sp_addlinkedserver
     @server = 'gs',
     @srvproduct = 'Google Sheets',
     @provstr = '{"dataSourcesConfiguration":"[{\"uriPath\":\"https://docs.google.com/spreadsheets/d/<spreadsheet-id>/edit\"}]","credential":"gs_cred"}';

CREATE DATABASE [sheetdb] WITH CONNECTION = [gs];

USE sheetdb;
CREATE DATABASE SCOPED CREDENTIAL gs_cred
    WITH IDENTITY = '<service-account-email>', SECRET = '<service-account-json-key>';

EXEC sp_import_metadata @database = 'sheetdb';

The SECRET is the service account’s entire JSON key file, as one string. Naming a credential that does not exist in the owning database fails the metadata import with an error explaining exactly this.

Either way, the connection only ever requests the read-only https://www.googleapis.com/auth/spreadsheets.readonly scope — the narrowest scope that can still read a spreadsheet’s values and metadata.

Configuration options#

Set these through @provstr as JSON when creating the linked server, or through the matching fields in the GUI — the read options on the provider form, the spreadsheets on the connection’s Source URIs screen:

Option

GUI field

Default

Meaning

dataSourcesConfiguration

Source URIs

(required)

The spreadsheets this connection exposes, as a JSON array of objects — one schema each. Each object needs a uriPath: the spreadsheet’s link, as the browser shows it. A bare spreadsheet id is not accepted. @datasrc is a shorthand for a list of one.

credential

Database scoped credential

(none)

Name of the DATABASE SCOPED CREDENTIAL to authenticate with. Omit it to fall back to Application Default Credentials.

typeDetection

Type detection

rowScan

rowScan (Sample rows) samples rows to work out each column’s type (see below). none (No detection) skips sampling and imports every column as text — the escape hatch for a tab whose data doesn’t fit the inferred types.

scanRowsLimit

Scan rows limit

50

How many rows of a tab are sampled per column when typeDetection=rowScan.

useFirstRowHeaders

Use first row as column headers

true

Whether a tab’s first row supplies the column names. Set to false to treat every row as data — columns are then named c0, c1, and so on.

How columns get their types#

Metadata import reads a tab’s first row for column names, then samples up to scanRowsLimit data rows per column to infer a type:

  • A column whose sampled values agree gets that type: whole numbers become bigint, decimals and percentages become float, checkboxes become bit, and cells formatted as a date or time become a real date/time type.

  • A column whose sampled values disagree — or offer no signal at all — is imported as text (nvarchar(max)), so nothing is silently dropped.

  • An elapsed-time column — formatted as [h]:mm:ss, for example 25:30:00 for 25 hours 30 minutes — becomes a number of days, not a time value. SQL Server’s time type cannot hold 24 hours or more and has no interval type, so the elapsed time arrives as its fractional-day value; multiply it by 24 to get hours.

  • Error cells (#DIV/0!, #N/A, and the like) and blank cells are read as NULL.

Set typeDetection=none to bypass inference entirely and import every column as text — useful when a tab’s real-world data disagrees with what sampling guesses.

Limits worth knowing#

  • A query reads a whole tab — filters run inside Querona after the read, not inside Google Sheets, so a narrow WHERE clause does not reduce what is fetched.

  • Google’s Sheets API quota is 300 requests per minute per project and 60 per minute per user; heavy, repeated querying of the same tab can be throttled.

  • A tab name is capped at 100 characters.

  • A spreadsheet is capped at 10 million cells in total.

  • The provider cannot list or browse spreadsheets — you always supply the list. The Sheets API has no operation that enumerates them.

  • Metadata import costs roughly one API call per spreadsheet plus one per tab, so a connection listing many spreadsheets is likelier to meet the per-minute quota above.

See also#