Google Sheets to BigQuery: How to Move Your Data

Moving Google Sheets into BigQuery takes data a person maintains by hand and puts it where your warehouse can join it. The sheet somebody updates every Friday is often the only place a piece of business context exists, and it stays invisible to every model you build until something loads it.

BigQuery can also read a sheet directly without copying anything, so the first question is whether you need a pipeline at all. The honest answer depends on whether you want a record of what the sheet used to say, and that section is below.

Google Sheets to BigQuery at a glance:

Why move data from Google Sheets to BigQuery?

Two situations account for most of these pipelines.

The first is joining hand-maintained context to system data. Account tiers, territory owners, budget targets, a curated list of strategic customers. These live in a spreadsheet because a person curates them, and they are exactly what your warehouse tables lack when somebody asks why a number looks the way it does.

The second is history, and on this destination it is the main argument. BigQuery already reads sheets live, so a pipeline earns its place when you want what the sheet said last month rather than what it says now.

What do you need before you start?

The setup is short, and one item is a conversation rather than a credential:

Authentication, and a spreadsheet shared with it. OAuth on Airbyte Cloud, or a service account key on self-managed deployments, which is the recommended route there. A service account needs Project Viewer permission, and unless the sheet is viewable by anyone with the link, somebody must share it with the service account. The Google Sheets source documentation covers both.

The spreadsheet link. Taken from Share and then Copy Link on the sheet itself. One source covers one spreadsheet, and each worksheet inside it becomes its own stream, so a workbook with eight tabs gives you eight tables.

A separate service account if you plan several of these. Airbyte recommends not using the same user or service account for more than three instances of this connector, because transfer speeds degrade beyond that. Plan the accounts before you build the fourth pipeline rather than after.

A BigQuery service account and a decided dataset location. With the BigQuery User and BigQuery Data Editor roles. Queries can only reference datasets in the same physical location and that cannot be changed after creation, so match it to whatever you intend to join against.

You also need an agreement with whoever owns the sheet, because column headers are now an interface and tab names become table names. Renaming either is a schema change rather than tidying.

How do you build a Google Sheets to BigQuery pipeline in Airbyte?

Step 1: Decide between a pipeline and an external table

Ask whether anybody will want to know what the sheet said last quarter. If the answer is no, a BigQuery external table reads it live with no pipeline to operate. If the answer is yes, or if the sheet is queried often enough that re-parsing it every time matters, build the pipeline.

Step 2: Configure the Google Sheets source

Click Sources in the left navigation, then New Source, and select Google Sheets, following adding a source. Authenticate, paste the spreadsheet link, and set the row batch size, which controls how many records the connector fetches at a time.

Step 3: Configure the BigQuery destination

Click Destinations, then New Destination, and select BigQuery, following adding a destination. Supply the project, dataset and service account key. Batched standard inserts is the default and is ample, because a spreadsheet is thousands of rows rather than millions.

Step 4: Create the connection and add a freshness check

Click Connections, then New connection, select the worksheets you need and a sync mode. Full refresh append accumulates a dated snapshot per sync, which is the whole reason to prefer a pipeline here. Then add a check that alerts if the table has not changed in a while.

Daily is generous for a sheet a person edits weekly, and a stalled sync looks exactly like an untouched sheet without that check.

Why not just use a BigQuery external table?

For many sheets you should. BigQuery reads Google Sheets directly, so a reference table somebody maintains and everybody queries live is well served without any pipeline at all, and that is less machinery to operate.

A pipeline wins on history. An external table shows whatever the sheet contains right now, so when somebody overwrites a target or deletes a row, your reporting changes retroactively and there is no record that it did. A synced table with append keeps every version, and on a hand-maintained source that is exactly the thing people later wish they had.

It also wins on resilience. An external table breaks when somebody moves the sheet, renames a tab or revokes a share, and it breaks at query time in front of whoever is running the report. A pipeline fails in your monitoring instead, which is a considerably better place for a failure to appear.

What should you do about types?

Treat the landed tables as staging and build a view over them, because a spreadsheet has no schema to carry. A cell containing 1,200 is text as far as the sheet is concerned, and the same column can hold a number on one row and the word pending on another without anything objecting.

BigQuery gives you a choice per column. A safe cast returns null when a value will not convert, while a plain cast fails the query. Use the strict version for anything financial, because a budget column with approx typed into one cell should break your build rather than silently become null and vanish from a sum.

Dates deserve particular care, since spreadsheet users write them in whatever format their locale suggests and a column can legitimately contain three conventions. Normalise them in the view and agree with the sheet owner that a single format is expected, rather than trying to parse every variant forever.

Frequently asked questions

Should I use an external table instead?

For a live reference table, often yes. Choose a pipeline when you want history, since an external table shows only the current contents and changes your reporting retroactively when somebody edits the sheet.

Why are my syncs getting slower?

Probably too many connector instances on one account. Airbyte recommends no more than three per user or service account, because Google's rate limits are counted per user rather than per pipeline.

Why is my sync returning nothing?

On a service account, check the spreadsheet has been shared with it, unless it is viewable by anyone with the link. Also confirm the sheets you want are grid sheets, since other types are not replicated.

Can I change the dataset location later?

No. It is fixed at creation, and queries can only reference datasets in the same physical location, so match it to whatever you intend to join against.

Can I do this without writing code?

The Airbyte setup is entirely UI-driven. You will want a BigQuery view casting the columns, which is the difference between a usable dataset and a raw one.

Get your Google Sheets data into BigQuery

Decide between an external table and a pipeline on whether anybody needs history, because that is the real difference here. Then plan service accounts in groups of three, have the conversation about headers, cast the columns in a view, and add a freshness check.

Airbyte's connector catalog includes 700+ pre-built connectors, so hand-maintained context can sit alongside your system data. For the same source in another warehouse, see Google Sheets to Snowflake, and for another source whose upstream is a person, Airtable to BigQuery.

Start syncing now →