Google Sheets
The google_sheets connector reads cell values from Google Sheets spreadsheets as rows, using the Sheets API v4. The
header row names the columns (duplicate names are made unique), so you can query the rows right away in your
transform SQL. It is read-only: it has no write operation.
Install
sw connectors -i google_sheets
Connect
source.yaml
kind: source
name: google_sheets_demo
spec:
config:
spreadsheet_id: { type: string, description: "Google Sheets document ID" }
secrets:
gcp_service_account: { type: string, description: "Service account JSON key" }
auth:
provider: google_sheets
service_account_json: "{{ secrets.gcp_service_account }}"
spreadsheet_id: "{{ config.spreadsheet_id }}"
Sign in one way. Every credential is a secret.
| Method | Keys |
|---|---|
| Service account (recommended) | service_account_json (or its alias credentials): the key's JSON text, or the path of a JSON key file |
| OAuth refresh token | client_id, client_secret, refresh_token |
| OAuth access token | access_token |
| API key (public spreadsheets only) | api_key |
spreadsheet_id on the connection is optional: it is the default for calls that do not set their own.
Share the sheet with the service account
Share the spreadsheet with the service account's client_email, with at least Viewer access.
With an OAuth refresh token:
source.yaml
kind: source
name: google_sheets_demo
spec:
secrets:
client_id: { type: string }
client_secret: { type: string }
refresh_token: { type: string }
auth:
provider: google_sheets
client_id: "{{ secrets.client_id }}"
client_secret: "{{ secrets.client_secret }}"
refresh_token: "{{ secrets.refresh_token }}"
Read data
A request uses service: spreadsheet:
| Method | What it reads |
|---|---|
read | A sheet (tab), or an A1 range, as rows |
get_values | An A1 range as rows (range is required) |
list_sheets | The sheets (tabs) of a spreadsheet: id, title, index, row and column counts |
| Argument | What it does | Default |
|---|---|---|
spreadsheet_id | The spreadsheet | The connection's |
sheet | read: the sheet (tab) title | The first sheet |
range | An A1 range such as Sheet1!A1:Z500 (instead of sheet) | |
header_row | The 1-based row that holds the column names. 0: no header; columns are named col_1, col_2, ... | 1 |
value_render_option | FORMATTED_VALUE, UNFORMATTED_VALUE or FORMULA | FORMATTED_VALUE |
batch_size | Rows per page | 1000 |
streams/budgets.yaml
requests:
- name: raw_budgets
operation:
service: spreadsheet
method: read
arguments:
sheet: "Budgets" # or range: "Budgets!A1:G1000"
transform:
mode: page
steps:
- name: budgets
select: |
SELECT
record->>'campaign_id' AS campaign_id,
record->>'month' AS budget_month,
(record->>'target_budget')::DOUBLE AS target_budget
FROM raw_budgets
WHERE record->>'campaign_id' IS NOT NULL
export:
campaign_budgets:
step: budgets
primary_key: [campaign_id, budget_month]
Listing the tabs of a spreadsheet:
streams/sheet_tabs.yaml
requests:
- name: raw_tabs
operation:
service: spreadsheet
method: list_sheets
transform:
mode: page
steps:
- name: sheet_tabs
select: |
SELECT
(record->>'sheet_id')::BIGINT AS sheet_id,
record->>'title' AS sheet_title,
(record->>'index')::INTEGER AS sheet_index,
(record->>'row_count')::INTEGER AS row_count,
(record->>'column_count')::INTEGER AS column_count
FROM raw_tabs
export:
workbook_sheets:
step: sheet_tabs
primary_key: [sheet_id]
In jobs, a step uses the same operation:. A Spark job reads natively; a DuckDB job runs it as a
call step:
steps:
- name: quarterly_targets
connection: reporting_sheets # provider: google_sheets
operation:
service: spreadsheet
method: read
arguments:
sheet: "Q3 Targets"
Good to know
- Spark. The rows are fetched on the Spark driver and pinned in the batch, so a replay reuses them and executors
never call Google. Install the Spark extra:
pip install "streamwright-google-sheets[spark]". - Vendor arguments. Sheets API parameters pass through unchanged.
See also
- DuckDB jobs: call steps.
- Starter template:
examples/sources/readers/google_sheets_demo.