Read a Google Spreadsheet & Load it to BigQuery

Move data out of a shared Google Spreadsheet and into a query-ready BigQuery table without manual exports or copy-paste. This blueprint reads a sheet through the Google Workspace plugin, serializes the rows to CSV, and loads them into BigQuery with automatic schema detection, giving analysts and data teams a repeatable Sheets-to-warehouse pipeline they can schedule, retry, and audit.

How it works

1. read_gsheet (io.kestra.plugin.googleworkspace.sheets.Read) reads the target spreadsheet by spreadsheetId, with fetch: true and valueRender: FORMATTED_VALUE so the displayed cell values land in Kestra's internal storage.
2. write_csv (io.kestra.plugin.serdes.csv.IonToCsv) converts the fetched Ion data into a CSV file, reading from {{ outputs.read_gsheet.uris.Sheet }}.
3. load_biqquery (io.kestra.plugin.gcp.bigquery.Load) loads that CSV into the destinationTable with autodetect: true, format: CSV, and a comma fieldDelimiter, pulling the file from {{ outputs.write_csv.uri }}.

What you get

• A hands-off path from a Google Sheet to a structured BigQuery table.
• Automatic column and type inference via BigQuery autodetect.
• Intermediate CSV materialized in Kestra storage for traceability.
• A pipeline you can rerun on demand or on a schedule.

Who it's for

• Data engineers wiring spreadsheet sources into a warehouse.
• Analysts who maintain reference data in Sheets and need it in BigQuery.
• Operations teams consolidating manual inputs into analytics tables.

Why orchestrate this with Kestra

Google Sheets and BigQuery have no shared scheduler that ties a sheet read to a warehouse load. Kestra closes that gap with declarative YAML, passing outputs between tasks, retries on transient API failures, full execution logs and lineage, and event or schedule triggers so the load runs exactly when you need it rather than on a manual export.

Prerequisites

• A Google Cloud project with the BigQuery and Google Sheets APIs enabled.
• A service account with access to the spreadsheet (share the sheet with the service account email) and BigQuery write permissions.
• An existing BigQuery dataset for the destination table.

Secrets

This blueprint references no {{ secret('NAME') }} values as written. Supply Google credentials through the plugin serviceAccount property (or environment defaults). For production, store the service account JSON as a Kestra secret and reference it on the read_gsheet and load_biqquery tasks.

Quick start

1. Copy this flow into a Kestra namespace.
2. Set spreadsheetId on read_gsheet to your sheet (the id sits between /d/ and /edit in the URL).
3. Set destinationTable on load_biqquery to your project.dataset.table.
4. Provide service account credentials and run the flow.

How to extend

• Add a Schedule or webhook trigger to load the sheet automatically.
• Read a specific range or named sheet instead of the whole document.
• Swap autodetect for an explicit BigQuery schema to enforce types.
• Add a downstream BigQuery Query task to transform or merge the loaded rows.

Links

• Google Cloud plugin docs
• BigQuery orchestration with Kestra