Build and refresh a Connected Sheets analysis

Quick answer

Connect an already-approved BigQuery table through Data > Data connectors > Connect to BigQuery, build a grouped analysis, and refresh it explicitly after the source changes. Connected Sheets stores query results in the spreadsheet; reopening the file is not a substitute for refreshing its data.

Prerequisites and cost boundary

Use Google Sheets on a computer with an account entitled to the feature in your organization. A complete general edition matrix is not established here, so this guide does not claim that every account qualifies or that the whole feature is Enterprise-only. Delegated Access has separate edition and administrator requirements and is not used in this exercise.

You need existing Google Cloud and BigQuery access, an approved billing project or an appropriately limited sandbox, and permission to query the intended data. Google’s troubleshooting guidance identifies BigQuery Job Creator on the billing project and Data Viewer on the dataset. Being able to see someone else’s cached analysis is not the same as having refresh permission.

Queries can incur charges. Before connecting or refreshing, obtain approval for the exact synthetic source and any associated query cost. Do not create a billing account, upgrade a sandbox, expand permissions, delegate credentials, or relax VPC Service Controls to complete this tutorial. If those prerequisites are absent, stop at the fixture specification and have the responsible administrator resolve access through your normal process.

Agree a tiny source fixture

Have the already-authorized source owner supply a dedicated practice table with these four rows and fields. order_id and region are text; amount is numeric. This table must not contain production records.

order_id	region	amount
T01	East	100
T02	West	80
T03	East	50
T04	West	20

Record the approved project, dataset and table identifiers privately in your exercise notes. The expected source count is four. East totals 150, West totals 100, and the overall amount is 250. These are independent arithmetic checks, not results of a query executed during authoring.

Do not type the fixture into an ordinary sheet and label it Connected Sheets. A normal range does not test the connection, permissions or refresh lifecycle. Conversely, do not upload arbitrary data into BigQuery merely to follow this exercise. Provisioning the source is a prerequisite handled by its authorized owner.

Connect and build a regional result

1. Open your private practice spreadsheet on a computer.
2. Choose Data > Data connectors > Connect to BigQuery.
3. Select the approved project and then the specified table or view. Check its identifier carefully, especially if similarly named production tables are visible.
4. Click Connect, then Start analyzing. Do not enable Delegated Access as a convenience workaround.
5. From the connected data, choose Pivot table, choose a destination, and click Create.
6. In the pivot settings, use region as the row field and amount as the value field, summarized by SUM rather than count. Apply the settings with Apply.

Check the output by labels, not by assuming East is always on a particular row. The East result must be 150 and West 100. If totals are shown, the grand total should be 250. A count result of 2 and 2 indicates that you counted records rather than summing amounts. Fix the aggregation before moving on.

Record the last successful refresh information shown by the interface, the source identifier, and those three expected amounts. Keep that record alongside the analysis so a later reader can distinguish old valid results from newly refreshed ones.

Make one controlled source change

Ask the authorized source owner to change only T04’s amount from 20 to 35 through their approved source-management process. Google Sheets cannot write that change back to BigQuery. Do not edit a displayed result cell and mistake it for a source update.

Before refreshing, the sheet may still show West 100 and total 250. That is the deliberately stale control. Once the source owner confirms the change, refresh the pivot using its Refresh control. Alternatively, open More > Refresh options next to Refresh and choose the intended objects. In this one-source practice file, Refresh all is acceptable only if all included queries are approved.

Wait for the refresh to finish successfully. The revised expectation is East 150, West 115, total 265, with four source records still present. The increase is exactly 15. If the source owner restores T04 to 20 afterward, refresh again and verify 150/100/250. A failure or pending query is not a successful refresh, even if the old numbers remain readable.

Diagnose the right layer

If connection selection is unavailable, check account eligibility and existing permissions with the administrator. If a query fails, capture the error, source identifier and attempted time. A renamed column, removed table, missing job permission or exceeded sandbox limit requires a different remedy; repeated refresh clicks do not distinguish them.

If only a scheduled refresh fails, investigate its owner, authorization and policy context separately. Schedules run under their owner, and end-user-context VPC restrictions can block them. Do not weaken security settings. This exercise deliberately uses manual refresh and does not create or take over a schedule.

Save, reopen and communicate freshness

After saving, reopen the sheet and compare the recorded numbers and refresh state. Cached results can persist without a new source query. Treat downloads as snapshots unless you separately verify a supported refresh workflow in the receiving application. Mobile Sheets can view and copy connected results but requires a computer for creation, editing and refresh.

No account connection, query, billing action or native test was performed for this manuscript.

Official references

Official references: getting started, analysis and refresh, access and refresh problems, sandbox limitations, and separate Delegated Access rules.