Skip to main content

Google Sheets

The google_sheets reader streams cell values and metadata from Google Sheets workbooks into DuckDB SQL pipelines. Cell matrices are automatically mapped into structured records with deduplicated headers, making them immediately queryable in transform steps.

Installation​

Install the official Google Sheets connector distribution:

# Via shorthand CLI:
sw connectors install google_sheets

# Or via pip:
pip install streamwright-google-sheets

Authentication​

Generate a Google Cloud Service Account key in JSON format and pass it via a secret:

source.yaml
spec:
  config:
    spreadsheet_id: { type: string, description: "Google Sheets document ID" }
  secrets:
    gcp_service_account: { type: string, description: "Service Account JSON credentials" }

auth:
  provider: google_sheets
  service_account_json: "{{ secrets.gcp_service_account }}"
  spreadsheet_id: "{{ config.spreadsheet_id }}"
Share sheet with service account

Ensure your Google Sheet is shared with the service account's client_email with at least Viewer access.

OAuth 2.0 (Refresh Token)​

source.yaml
auth:
  provider: google_sheets
  client_id: "{{ secrets.client_id }}"
  client_secret: "{{ secrets.client_secret }}"
  refresh_token: "{{ secrets.refresh_token }}"

API Key (Public Spreadsheets)​

source.yaml
auth:
  provider: google_sheets
  api_key: "{{ secrets.google_api_key }}"

Stream definitions​

Reading sheet rows (read)​

Extract rows from a sheet tab or cell range:

streams/budgets.yaml
description: Extract campaign budgets and target allocations.

requests:
  - name: raw_budgets
    sdk: google_sheets
    service: spreadsheet
    method: read
    arguments:
      spreadsheet_id: "{{ config.spreadsheet_id }}"
      sheet: "Budgets"              # or range: "Budgets!A1:G1000"
      header_row: 1                 # 1-indexed (default: 1; 0 for auto col_1, col_2...)
      batch_size: 1000              # streaming batch size
      value_render_option: "FORMATTED_VALUE"

transform:
  mode: page
  steps:
    - name: budgets
      select: |
        SELECT
          record->>'campaign_id'              AS campaign_id,
          record->>'campaign_name'            AS campaign_name,
          record->>'month'                    AS budget_month,
          (record->>'target_budget')::DOUBLE  AS target_budget,
          record->>'owner'                    AS owner,
          current_timestamp                   AS _synced_at
        FROM raw_budgets
        WHERE record->>'campaign_id' IS NOT NULL

export:
  campaign_budgets:
    step: budgets
    primary_key: [campaign_id, budget_month]

Listing workbook tabs (list_sheets)​

Inspect and discover all tabs within a spreadsheet:

streams/sheet_tabs.yaml
description: Discover workbook tabs and sheet dimensions.

requests:
  - name: raw_tabs
    sdk: google_sheets
    service: spreadsheet
    method: list_sheets
    arguments:
      spreadsheet_id: "{{ config.spreadsheet_id }}"

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]

Running the pipeline​

streamwright run my_sheets_pipeline --output parquet:out/sheets