Create external table over google sheets

[dbrtly]

Describe the feature

Bigquery has functionality to create an external table over a google sheet. Dbt-external-tables should include ability to create these external tables.

sources:
  - name: gsheets
    tables:
      - name: my_table
        description: "External table google sheet"
        columns:
          - name: my_string
            data_type: string
          - name: my_bool
            data_type: boolean
          - name: my_integer
            data_type: int64
          - name: my_float
            data_type: float64
        external:
          options:
            format: google_sheet
            sheet_range: MY_NAMED_RANGE
            skip_leading_rows: 1
            uri: https://docs.google.com/spreadsheets/d/1SywQKI30ookqnfsp

Create the external table with:

dbt run-operation stage_external_sources \
  —args “select: gsheets”

As

create or replace external table `gcp-project.gsheets.my_table`
(
    my_string: string
    my_bool boolean
    my_integer  int64
    my_float float64
)
options(
    description= ‘External table google sheet’,
    format=‘’GOOGLE_SHEETS’,
    sheet_range=MY_NAMED_RANGE,
    skip_leading_rows=1,
    uris=[‘https://docs.google.com/spreadsheets/d/1SywQKI30ookqnfsp’]
)

Describe alternatives you've considered

Common patterns:

• YOLO: I’ll just do it in the UI, we’ll only need to do this once
• orchestrator task

Additional context

I have working code.

Who will this benefit?

Sometimes analytics teams can use dbt as the default tool and a context shift to create these external tools is a missed opportunity because it means omitting a small part of the solution from the habits (docs, tests, git) that dbt facilitates. Make it easy to follow good practices.