Smartsheet to BigQuery: How to Move Your Data

Moving Smartsheet into BigQuery takes data that a person maintains by hand and puts it where a query engine can reach it. That is genuinely valuable, because the sheet somebody updates every Friday is often the only place a piece of business context exists.

This guide covers the managed path with Airbyte. Two connector behaviours shape the work, and both come from the fact that a spreadsheet is not a database: almost every column arrives as a string, and column headers are an interface that a well-meaning edit can break.

Smartsheet to BigQuery at a glance:

Why move data from Smartsheet to BigQuery?

Two situations account for most of these pipelines.

The first is joining hand-maintained context to system data. Project budgets, account tiers, territory owners, a list of strategic customers. These live in Smartsheet because a person curates them, and they are exactly what your warehouse tables lack. A join between the two answers questions neither side can answer alone.

The second is history. Smartsheet shows you the sheet as it is now, and a warehouse can hold what it looked like every week for two years, which is how you answer questions about how a plan changed. If you only need the current contents for one dashboard, a Smartsheet report is simpler and this pipeline is unnecessary.

What do you need before you start?

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

An API access token. Generated from the account menu under apps and integrations, needing only read access on the sheets in question. The same token can be reused across every source you create.

The sheet ID, which is not in the URL. Unlike Google Sheets, you find it inside the Smartsheet app under File and then Properties. This catches everyone at least once.

One source per sheet. A Smartsheet holds a single sheet and a source instance syncs one of them, so replicating twelve sheets means twelve sources. They all draw on the same limit of 300 requests per minute per token, which is generous given the connector makes only a handful of calls per sync.

An agreement about column headers. Headers must contain only alphanumeric characters and underscores, so a rename from status to Status (Q3) breaks the stream. Tell whoever owns the sheet that headers are an interface, and consider a protected header row if your plan supports it.

On the BigQuery side you need a project, a dataset and a service account with the BigQuery User and BigQuery Data Editor roles. Set the dataset location to match whatever you intend to join against, since it cannot be changed after creation.

How do you build a Smartsheet to BigQuery pipeline in Airbyte?

Step 1: Agree the rules with the sheet owner

Explain that column headers are now an interface and that a rename breaks the sync. Ask what the columns are supposed to contain, because you will need that to write the casting layer, and hand-maintained data always has a few columns whose intent is not obvious from their contents. Ten minutes here saves a great deal later.

Step 2: Configure the Smartsheet source

Click Sources in the left navigation, then New Source, and select Smartsheet, following adding a source. Supply the access token and the sheet ID. While you are here, name the sheet-level and row-level metadata fields you want, because they are available but not included by default. Row metadata is usually worth taking, since it answers when a row last changed.

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 loading method and is ample here, because a Smartsheet is thousands of rows rather than millions and volume is never the constraint on this pipeline.

Step 4: Create the connection and choose a sync mode

Click Connections, then New connection, and choose a sync mode. Incremental with deduplication suits a sheet with a stable row identifier. Full refresh overwrite is perfectly reasonable on a small sheet and avoids reasoning about what a deleted row means. Full refresh append is the option worth considering if anyone wants history, since it accumulates a snapshot per sync.

That last option is the one people overlook. On a sheet somebody edits weekly, a daily snapshot in BigQuery gives you a change history the source does not keep, at almost no cost given the volume.

Why does everything arrive as a string?

Because only date and datetime columns get real types. Everything else, including Smartsheet's richer column types such as contacts, checkboxes and dropdowns, is treated as a string during schema generation. The table you land is therefore not the table anyone should query.

Build a modelled view over the raw table that casts each column to what it actually is, and make that layer fail loudly when a cast fails. This is not busywork. Hand-maintained data contains typos, and a numeric column with the word approx typed into one cell will silently become null in a permissive cast and a visible error in a strict one. You want the error, because the person who typed it can fix it in ten seconds if told.

BigQuery makes this pleasant enough. Use SAFE_CAST where a bad value should become null and a plain cast where it should break the build, and choose per column rather than applying one rule everywhere. A budget figure deserves the strict treatment; a free-text note does not.

What happens when someone edits a header?

Column headers must contain only alphanumeric characters and underscores, which is a constraint of the Airbyte protocol rather than of Smartsheet. Smartsheet itself is happy to let someone write Status (Q3 revised), and that is when the stream stops working.

The awkward part is that a person renaming a column is doing something entirely reasonable from their point of view. They are tidying a spreadsheet they own. Nothing in Smartsheet suggests the header is load-bearing, which is why the conversation in step 1 matters more than any configuration.

Add a freshness check on the destination table so a stalled sync surfaces within a day rather than when someone notices the numbers look old. On any pipeline whose upstream is a person, that check is worth having regardless of how carefully you explained things.

Frequently asked questions

Why are my numeric columns strings in BigQuery?

The connector types only date and datetime columns. Everything else is generated as a string, so cast it in a downstream view using SAFE_CAST or a strict cast depending on how you want bad values handled.

Can one Smartsheet source sync several sheets?

No. Each source instance handles one sheet. Create one source per sheet and reuse the same API access token across all of them.

Where do I find the sheet ID?

Inside the Smartsheet app under File and then Properties. Unlike Google Sheets, it does not appear in the browser URL.

My sync broke after someone renamed a column. Why?

Column headers must contain only alphanumeric characters and underscores. Spaces, brackets and punctuation in a header will break the stream.

Can I do this without writing code?

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

Get your Smartsheet data into BigQuery

The technical work here is small and the social work is not. Cast the strings in a modelled view, take the row metadata, consider append mode so you accumulate a history the sheet does not keep, and have the conversation about headers before someone renames one.

Airbyte's connector catalog includes 600+ pre-built connectors, so hand-maintained context can sit alongside your system data. For the same source feeding a lakehouse, see Smartsheet to Databricks, and for another source whose upstream is a person, Airtable to BigQuery.

Start syncing now →