top of page

Connected Sheets for BigQuery limits: 500,000-row extracts and query costs

2 hours ago
8 min read
Cover showing the title Connected Sheets for BigQuery limits: 500,000-row extracts and query costs on a teal data skyline, Data Studios original cover

Connected Sheets lets a Google Sheets user build pivot tables, charts, formulas and extracts on top of a BigQuery table without copying the table into the spreadsheet. The documented ceilings are specific: an extract can pull up to 500,000 rows or 10 MB, extracts above 50,000 rows are capped at 5 million cells, pivot tables are documented at up to 200,000 rows, and the sheet preview shows 500 rows while pivots, formulas and charts work on the full table. Every one of those objects runs as a BigQuery job billed to the Google Cloud project the user selects.


That last point decides the monthly bill. Google's documentation states that when a user filters or refreshes, Connected Sheets sends the query to BigQuery again against the selected project, so a shared workbook with several pivots and a team of analysts generates a steady stream of jobs. Under BigQuery on-demand pricing, those jobs are charged at $6.25 per TiB processed after the first free TiB each month, as listed on Google Cloud's BigQuery pricing page on November 16, 2026.


The facts below come from Google's Connected Sheets documentation for BigQuery (last updated November 10, 2026), the Google Docs Editors Help pages on analyzing, refreshing and troubleshooting BigQuery data in Sheets, the Google Workspace Admin Help page on Connected Sheets, and the BigQuery pricing page, all checked on November 16, 2026. The worked examples are illustrative and computed in Python.


··········


CONNECTED SHEETS FOR BIGQUERY: THE DOCUMENTED LIMITS.


The limits sit in two places: the BigQuery documentation and the Sheets help pages. They agree on extracts and preview size. They give two different figures for pivot tables, and the table shows both because Google publishes both.


........


Object or setting

Documented limit

Where Google states it

Sheet preview

500 rows shown

BigQuery Connected Sheets docs

Extract, up to 50,000 rows

No cell limit

BigQuery docs and Sheets help

Extract, 50,001 to 500,000 rows

5 million cells or fewer

BigQuery docs and Sheets help

Extract, over 500,000 rows

Not supported

BigQuery docs and Sheets help

Extract size

Up to 500,000 rows or 10 MB

Sheets help

Pivot table

Up to 200,000 rows

BigQuery docs and Sheets help

Pivot table failure

Over 100K results

Sheets troubleshooting page

Query result size

Fails above 10 MB

Sheets troubleshooting page

Whole spreadsheet

20 million cells

Google Workspace Updates, Sept. 2026


........


Google does not reconcile the two pivot figures. The help page describes a pivot as supporting up to 200,000 rows, while the troubleshooting page says a pivot fails when it returns more than 100K results and recommends filters, fewer rows per breakout or turning off totals. A pivot that sits near either figure is a candidate for pre-aggregation in BigQuery.


The spreadsheet-wide cell limit also applies to whatever Connected Sheets writes into the file. Google raised it to 20 million cells per spreadsheet in an announcement dated September 10, 2026, for all Workspace customers, Workspace Individual subscribers and personal accounts, while the Workspace Admin Help page on Connected Sheets still cites 10,000,000 total cells and a #NUM! error when an extract exceeds the limit. Admins on Scheduled Release domains should confirm which limit their tenant shows before planning large extracts.


··········


SIZING A CONNECTED SHEETS EXTRACT BY COLUMN COUNT.


Because the 5 million cell cap applies only above 50,000 rows, the maximum extract depends on how many columns the extract carries. Selecting fewer columns is the simplest way to get more rows into the sheet.


........


Columns in extract

Maximum rows

Cells used

8

500,000

4,000,000

10

500,000

5,000,000

12

416,666

4,999,992

20

250,000

5,000,000

40

125,000

5,000,000

120

50,000

6,000,000


........


Up to 10 columns, the 500,000-row ceiling is the binding limit. From 11 columns upward, the 5 million cell cap takes over, and a 12-column extract tops out at 416,666 rows. A very wide extract of 120 columns can still reach 50,000 rows because the cell cap does not apply at that size, though the separate 10 MB limit and the spreadsheet-wide cell limit still do. These figures assume the extract is the only large object in the file.


··········


CONNECTED SHEETS REQUIREMENTS: BILLING PROJECT AND IAM ROLES.


The Sheets help page lists three things a user needs: access to Google Cloud, access to BigQuery, and a project with billing set up in BigQuery. The BigQuery documentation adds that the BigQuery API must be enabled on that project, which requires the serviceusage.services.enable permission, typically held through the Owner or Service Usage Admin role. Workspace admins must also have Google Cloud turned on for the organization.


On the BigQuery side, the Workspace Admin Help page states that a user needs either bigquery.user, or bigquery.jobUser together with bigquery.dataViewer, to add or use a BigQuery project in Sheets. The troubleshooting page phrases the same requirement as the BigQuery Job Creator role on the billing project plus the BigQuery Data Viewer role on the datasets that hold the table. Google is explicit that what a user can do depends on that user's own IAM role and spreadsheet permissions, not the spreadsheet owner's.


That rule shapes how a shared workbook behaves. Recipients without the BigQuery roles can view the analysis and use standard Sheets features, but they cannot refresh it, schedule refreshes or create their own connected sheet. Google also offers delegated access for Connected Sheets, which lets a sheet owner share query access with collaborators. The Workspace Admin Help page lists it only for Enterprise Standard, Enterprise Plus, Education Standard, Education Plus, Enterprise Essentials and Enterprise Essentials Plus, and it cannot be used on a sheet shared publicly by link.


........


Requirement

What Google documents

Google Cloud

Turned on for the Workspace organization

Billing project

A project with billing enabled and verified

BigQuery API

Enabled on the project

Run queries

bigquery.user, or bigquery.jobUser

Read the table

bigquery.dataViewer on the dataset

Delegated access

Enterprise, Education and Enterprise Essentials editions listed

No billing account

BigQuery sandbox, with its own limits


........


Google's help pages do not publish a general list of Workspace editions for Connected Sheets for BigQuery itself; the edition list on the admin page applies to delegated access. Users without a billing account can start in the BigQuery sandbox, which the sandbox documentation limits to a lifetime 10 GiB of storage, 1 TiB of query processing per month, tables that expire after 60 days, and no DML, streaming or Data Transfer Service.


··········


HOW CONNECTED SHEETS QUERIES ARE BILLED IN BIGQUERY.


Connected Sheets has no price of its own. The Connected Sheets documentation says queries run in BigQuery on the user's behalf, either on request or on a schedule, against the project selected in the sheet, and the BigQuery pricing page does not mention Connected Sheets at all. The cost is therefore whatever that project pays for BigQuery compute.


On on-demand pricing, the rate is $6.25 per TiB, with the first 1 TiB of query data processed per month free. Charges are rounded up to the nearest MB, with a minimum of 10 MB per table referenced and 10 MB per query. On BigQuery editions, the same jobs consume slots; the pricing page lists pay-as-you-go rates of $0.04 per slot-hour for Standard, $0.06 for Enterprise and $0.10 for Enterprise Plus, billed per second with a one-minute minimum. A fuller comparison of both models sits in the BigQuery on-demand vs editions pricing reference.


Scheduled refreshes run as the user who set up the schedule, so their jobs are attributed to that user and billed to the workbook's project. Another user can take over the schedule, and the schedule pauses when someone adds or updates a data source in the workbook. Admins on Enterprise or Enterprise Plus editions who want to keep spreadsheet traffic from crowding out pipelines can isolate the billing project with the BigQuery project caps on slots and concurrency.


··········


WORKED COST EXAMPLE: A SHARED SALES WORKBOOK ON ON-DEMAND PRICING.


The example is illustrative. A team of 8 analysts shares one workbook connected to a BigQuery sales table. The workbook holds 5 pivot tables and charts, and each one scans 6 GiB of referenced columns per query. A daily scheduled refresh re-runs all 5 objects for 30 days, and each analyst changes a filter or refreshes an object 5 times per working day across 21 working days, each action re-running one 6 GiB query.


........


Scenario

Queries per month

GiB processed

TiB processed

On-demand cost

Raw table, 6 GiB per query

990

5,940

5.80

$36.25

Same, after the free 1 TiB

990

5,940

5.80

$30.00

Aggregated table, 0.25 GiB per query

990

247.5

0.24

$1.51


........


The scheduled refresh accounts for 900 GiB and the analysts' manual activity for 5,040 GiB, so most of the spend comes from interactive use. The free 1 TiB applies per billing account and is shared with every other query in it, which is why the table shows the cost with and without it. Pointing the workbook at a pre-aggregated or partitioned table that scans 0.25 GiB per query cuts the same activity to 247.5 GiB, which costs $1.51 even before the free tier.


The same arithmetic is useful as an alerting threshold. If the project's Connected Sheets jobs exceed the expected TiB for the month, a pivot was likely rebuilt on the raw table or a new object was added without filters. Writing the aggregation SQL first and checking its bytes scanned before connecting the sheet is covered in the guide to writing SQL with Gemini in BigQuery and checking bytes scanned.


··········


MONITORING CONNECTED SHEETS JOBS WITH INFORMATION_SCHEMA.


Google's documentation identifies Connected Sheets jobs by the job ID prefix sheets_dataconnector and provides example queries against the INFORMATION_SCHEMA.JOBS_BY_PROJECT and JOBS_BY_ORGANIZATION views. The query below follows that pattern for one project in the US multi-region and adds an on-demand cost column at the published rate, without subtracting the free tier.


SELECT
  user_email,
  COUNT(*) AS queries,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4), 3) AS tib_billed,
  ROUND(SUM(total_bytes_billed) / POW(1024, 4) * 6.25, 2) AS on_demand_usd
FROM `region-us`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE job_id LIKE 'sheets_dataconnector%'
  AND creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 30 DAY)
  AND state = 'DONE'
  AND error_result IS NULL
  AND statement_type != 'SCRIPT'
GROUP BY user_email
ORDER BY tib_billed DESC;

The region qualifier must match where the dataset lives. For a scheduled refresh, the user_email column shows the schedule owner, so a large figure against one person may reflect a daily refresh of the whole workbook. Teams that also use the Gemini-powered conversational analysis in the same sheets, described in the news on conversational analytics in Connected Sheets, face the same requirement, since Google's documentation says first-time users of that feature may be asked to authorize BigQuery access.


··········


VPC SERVICE CONTROLS AND SCHEDULED REFRESH FAILURES.


The BigQuery documentation states that VPC Service Controls does not support Sheets, so data inside a perimeter may be unreachable until the perimeter allows it. Google's sample configuration uses an access level or ingress rule for trusted users and devices plus an egress rule that lets query results be copied to spreadsheets.


Scheduled refreshes carry no end-user context such as IP address or device information. A perimeter that relies on that context will let manual refreshes through and block scheduled ones. The troubleshooting page lists the other common causes of a failed schedule: the owner revoked OAuth authorization, lost edit access to the sheet, is no longer eligible to use BigQuery in Sheets or had the account deleted, the table was deleted, or the schedule paused after a data source changed.


··········


CONNECTED SHEETS AND BIGQUERY COSTS: WHAT TO CHECK BEFORE ROLLOUT.


Google documents the extract ceiling of 500,000 rows or 10 MB, the 5 million cell cap above 50,000 rows, the 500-row preview and the IAM roles each user needs. Pivot limits appear as 200,000 rows in the main documentation and as a failure above 100K results on the troubleshooting page, and the spreadsheet-wide cell limit reads 20 million in the September 2026 announcement and 10 million on the admin page, so both deserve a test in the actual tenant.


Connected Sheets has no separate price, and Google does not publish one. Its cost is BigQuery compute in the selected billing project, at $6.25 per TiB on-demand after the free monthly TiB or at the edition slot-hour rate, and it scales with the number of objects, the columns they reference and how often people filter and refresh. Before opening a workbook to a team, choose the billing project deliberately, point pivots at aggregated or partitioned tables, and track the sheets_dataconnector jobs each month against the volume the workbook was expected to generate.


··········


FOLLOW US FOR MORE.


·····


DATA STUDIOS


·····


datastudios.org


bottom of page