How to connect BigQuery to Google Sheets without pulling the warehouse in

The instinct when somebody asks for warehouse data in a spreadsheet is to pull the table across. It works for a while, then the sheet gets slow, then it stops loading, and somewhere in between the person who owns the BigQuery bill starts asking questions.

The fix is almost never a better export. It is deciding what the sheet is actually for — a small aggregate somebody edits, a live slice they explore, or a scheduled extract feeding another process — because those three want three different mechanisms.

What we'll cover

• Three mechanisms, three purposes
• Aggregate in the warehouse
• Building it with CodeWords
• The cost of a shared sheet
• Row limits and what happens at them
• Types that do not survive the trip
• Keeping a scheduled extract honest
• Who can see the sheet, and who could not see the data
• Making it survive
• Limits worth knowing about
• What to set up first
• Frequently asked questions

Three mechanisms, three purposes

Connected Sheets queries BigQuery from inside the spreadsheet, with pivots and refreshes, without the data living in the sheet. It suits an analyst exploring, and every refresh is a query somebody is paying for.

A scheduled extract writes a result into a sheet on a schedule. It suits a report people read and a downstream process that reads the sheet, and the cost is one predictable query per run.

An ad hoc pull through the API suits a one-off, and it is the mechanism most likely to be left running for two years after the one-off.

Deciding which starts with a question: does anybody need to change the slice? If not, a scheduled extract is cheaper, faster to open, and works when the person viewing it has no BigQuery access at all — which is usually the point.

Aggregate in the warehouse

The single most important habit, and the one that makes everything else manageable.

Send results, not rows. A sheet wants the answer, and the warehouse is much better at computing it than a spreadsheet formula over fifty thousand rows.

Group, filter, and limit in SQL. Every row you do not send is cheaper to query, faster to write, and faster for the sheet to open.

Pre-aggregate into a table if the same query runs often. A scheduled query materialising a small daily summary, extracted from there, costs a fraction of querying the raw events each time.

Resist "just give me everything and I'll filter it". That request is how a sheet ends up with two hundred thousand rows and a filter view nobody can load on a laptop.

Ask what decision the sheet supports. Most answers turn out to need a few dozen rows, and the conversation is shorter than the export.

Building it with CodeWords

CodeWords connects to more than 3,000 integrations, and the connection is made once and reused.

1. Open CodeWords and start a new automation.
2. Describe what should happen in plain language to Cody, the automation builder: which query, how often, and which sheet it should land in.
3. Authorize the connection with a service account granted read access to the relevant dataset, and access to the target spreadsheet.
4. Describe the exceptions: a query returning nothing, a result larger than expected, a schema change in the source table.
5. Run it against a copy of the sheet and confirm the numbers against a period you already know.

You describe the outcome; Cody builds it, connects it, and deploys it. The free plan covers light use, with Pro at $39 per month and Business at $100 per month as usage grows; details are on the pricing page.

The cost of a shared sheet

The failure mode nobody anticipates, and it arrives as an invoice.

Connected Sheets refreshes are queries. A sheet shared with thirty people, each refreshing when they open it, is thirty queries — and BigQuery bills for bytes scanned, not for rows returned.

A SELECT * on a wide table scans everything, even if the sheet shows twelve columns. Selecting only the columns you need is the largest single saving available.

Partition and cluster filters matter enormously. A query filtered on a partitioned date column scans a fraction of what an unfiltered one does, and the difference is often two orders of magnitude.

Scheduled extracts are predictable, which is their main advantage — one query per run regardless of how many people open the sheet.

Set a maximum bytes billed on anything a person can trigger, so a mistake fails rather than costing.

Report cost by query. If you cannot see which sheet is expensive, you cannot fix it, and the usual culprit is one report nobody remembers creating.

Row limits and what happens at them

Spreadsheets have a hard cell limit, and a wide table reaches it far sooner than people expect — columns multiply against rows.

A write that would exceed it fails, usually partway, leaving a sheet in a state somebody will not notice is partial.

Truncation is worse than failure. A sheet quietly holding the first portion of a result is a sheet somebody will make a decision from.

Check the row count before writing, and refuse with a clear message rather than attempting it.

If the answer genuinely needs that many rows, the destination is wrong. Point a proper tool at the warehouse instead, or narrow the question.

Types that do not survive the trip

Worth knowing, because each produces a wrong number rather than an error.

Timestamps carry a timezone in BigQuery and land in a sheet's own timezone, which is a workbook setting most people have never looked at.

Large integers lose precision. Sheets holds numbers as floating point, so identifiers beyond a certain size are silently rounded — write them as text.

Numeric and decimal types round when they become spreadsheet numbers, which matters for anything financial.

Nested and repeated fields have no spreadsheet equivalent, so flatten deliberately in SQL rather than letting something guess.

Nulls become empty cells, which a spreadsheet formula treats as zero, and that changes averages.

Keeping a scheduled extract honest

Write the run time into the sheet, visibly. A stale sheet looks identical to a current one and people will quote it.

Say which query produced it, so a disputed number can be checked.

Replace the range rather than appending, unless the sheet is deliberately a log, or duplicates accumulate quietly.

Report when the result is empty instead of writing an empty sheet, since an empty sheet reads as "zero" rather than "the query broke".

Alert on implausible magnitudes before writing, because a report that silently halves is worse than one that fails.

Who can see the sheet, and who could not see the data

The governance point, and it catches teams out because the two access systems are entirely separate.

A spreadsheet's sharing is independent of warehouse permissions. A dataset restricted to six people becomes visible to anybody the sheet is shared with, and nothing warns you.

Link sharing is the usual culprit. A sheet set to "anyone with the link" carries whatever the extract put in it.

Aggregate away the sensitivity where you can. A summary by region is shareable in ways a row per customer is not, and it is usually what the reader wanted anyway.

Exclude the columns nobody needs. Identifiers, email addresses, and free-text fields tend to arrive because they were in the table rather than because anybody asked.

Check the sheet's sharing when you build the extract, and again if the report becomes important, since access widens over time and nobody reviews it.

Note the retention difference. A deletion in the warehouse does not reach a spreadsheet copy, which matters for anything subject to a retention policy.

Making it survive

Make writes idempotent, replacing a named range rather than appending.

Handle schema changes in the source. A renamed column breaks the query, and reporting that clearly beats writing a sheet with a missing column.

Use a service account, not a person's credentials, or the report stops when they leave.

Set a query timeout and a bytes-billed cap, both of which are free to set and prevent one category of incident.

Report the outcome. Rows written, bytes scanned, and duration — the middle one is what keeps the cost conversation grounded.

Limits worth knowing about

Sheets has a total cell limit per spreadsheet, which wide tables reach quickly.

Sheets API writes are rate limited, so very large writes need chunking.

Connected Sheets has its own result limits and is not a substitute for a reporting tool on large data.

BigQuery bills on bytes scanned, so the cost is driven by the columns and partitions your query touches rather than by the rows it returns.

Query results have a size limit for direct retrieval, above which you write to a destination table first.

What to set up first

A scheduled extract of one small, aggregated result, with the run time written into the sheet. Start by replacing whichever report somebody currently produces by hand, because that one already has a definition and an audience.

Two habits make the difference. Aggregate in SQL so the sheet receives dozens of rows rather than thousands, since almost every problem on this page follows from sending too much. And put the run time in a cell where people will see it, because a sheet nobody knows is three weeks old is worse than no sheet.

Frequently asked questions

Connected Sheets or a scheduled extract?

Connected Sheets when somebody needs to change the slice themselves and has warehouse access. A scheduled extract for a report people read, because it costs one query per run regardless of how many people open it and it works for viewers with no BigQuery access.

Why is our BigQuery bill rising?

Usually a shared sheet refreshing per viewer, a SELECT * on a wide table, or a query with no partition filter. BigQuery bills on bytes scanned, so the columns and partitions you touch matter far more than the rows you return.

What happens when the result is too large?

The write fails, often partway, leaving a partial sheet that nobody notices is partial. Check the row count first and refuse with a clear message — and if the answer genuinely needs that many rows, a spreadsheet is the wrong destination.

Why are large identifiers wrong in the sheet?

Because Sheets stores numbers as floating point and loses precision beyond a certain size. Write identifiers as text rather than numbers, which also stops the spreadsheet reformatting them.

Why do timestamps look shifted?

BigQuery timestamps carry a timezone and land in the spreadsheet's own timezone, which is a workbook setting most people have never checked. State the timezone in the sheet so nobody has to guess.

Should the sheet append or replace?

Replace a named range, unless the sheet is deliberately a log. Appending accumulates duplicates on every retry, and retries happen more often than people expect.

How do I stop people quoting a stale sheet?

Write the run time into a visible cell on every run. A stale sheet looks exactly like a current one, and that is how a number from three weeks ago ends up in a board pack.

Does warehouse access control apply to the sheet?

No. Sheet sharing is entirely separate, so an extract from a restricted dataset becomes visible to everybody the spreadsheet is shared with. Check the sharing when you build the extract, and aggregate away anything that should not travel.

Does deleting data from the warehouse clear the sheet?

No. A spreadsheet is a separate copy that no retention policy or deletion request in the warehouse will reach, which is worth knowing before an extract includes anything personal.

Related reading

• BigQuery and CodeWords
• Connecting Snowflake to Google Sheets
• CodeWords integrations and templates