Skip to main content

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.

MethodKeys
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 tokenclient_id, client_secret, refresh_token
OAuth access tokenaccess_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:

MethodWhat it reads
readA sheet (tab), or an A1 range, as rows
get_valuesAn A1 range as rows (range is required)
list_sheetsThe sheets (tabs) of a spreadsheet: id, title, index, row and column counts
ArgumentWhat it doesDefault
spreadsheet_idThe spreadsheetThe connection's
sheetread: the sheet (tab) titleThe first sheet
rangeAn A1 range such as Sheet1!A1:Z500 (instead of sheet)
header_rowThe 1-based row that holds the column names. 0: no header; columns are named col_1, col_2, ...1
value_render_optionFORMATTED_VALUE, UNFORMATTED_VALUE or FORMULAFORMATTED_VALUE
batch_sizeRows per page1000
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​