Support for BigQuery external tables on Google Sheets using python

[daniel-bartley]

Describe the feature

It's not yet possible to create an external table using sql.
However Google provides Python script that does the job.

Describe alternatives you've considered

https://cloud.google.com/bigquery/docs/external-data-drive#create_external_tables

from google.cloud import bigquery
import google.auth

# Create credentials with Drive & BigQuery API scopes.
# Both APIs must be enabled for your project before running this code.
credentials, project = google.auth.default(
    scopes=[
        "https://www.googleapis.com/auth/drive",
        "https://www.googleapis.com/auth/bigquery",
    ]
)

# Construct a BigQuery client object.
client = bigquery.Client(credentials=credentials, project=project)

# TODO(developer): Set dataset_id to the ID of the dataset to fetch.
# dataset_id = "your-project.your_dataset"

# Configure the external data source.
dataset = client.get_dataset(dataset_id)
table_id = "us_states"
schema = [
    bigquery.SchemaField("name", "STRING"),
    bigquery.SchemaField("post_abbr", "STRING"),
]
table = bigquery.Table(dataset.table(table_id), schema=schema)
external_config = bigquery.ExternalConfig("GOOGLE_SHEETS")
# Use a shareable link or grant viewing access to the email address you
# used to authenticate with BigQuery (this example Sheet is public).
sheet_url = (
    "https://docs.google.com/spreadsheets"
    "/d/1i_QCL-7HcSyUZmIbP9E6lO_T5u3HnpLe7dnpHaijg_E/edit?usp=sharing"
)
external_config.source_uris = [sheet_url]
external_config.options.skip_leading_rows = 1  # Optionally skip header row.
external_config.options.range = (
    "us-states!A20:B49"  # Optionally set range of the sheet to query from.
)
table.external_data_configuration = external_config

# Create a permanent table linked to the Sheets file.
table = client.create_table(table)  # Make an API request.

# Example query to find states starting with "W".
sql = 'SELECT * FROM `{dataset_id}.{table_id}` WHERE name LIKE "W%"'

query_job = client.query(sql)  # Make an API request.

# Wait for the query to complete.
w_states = list(query_job)
print(
    f"There are {len(w_states)} states with names starting with W in the selected range."
)

Additional context

Yes. BigQuery specific.

Who will this benefit?

BigQuery external tables over Google Sheets provides a user friendly endpoint to and mechanism to get spreadsheet data into BigQuery. Creating the external tables in dbt makes them more reproducible in different environments.

[amirbtb]

Hi,
I found this gist from @thomashandorf :
https://gist.github.com/thomashandorf/0d54848ccd84448001abb2a51d48794a

It seems that it's now possible to use SQL to create BigQuery external tables over Google Sheets but I couldn't find anything about this in BigQuery documentation.

Thank you @thomashandorf for that gist 🙏🏽 !

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[geo909]

🆙

I would like to see this feature implemented, too.

[moseleyi]

Would be really useful to have this feature!

[oskarabc]

Hi team,

I can confirm this issue is still occurring and wanted to provide additional details that might help with the fix.

Environment:

• dbt version: 1.9.6
• dbt-bigquery: 1.9.2
• dbt-external-tables package version: 0.8.7
• BigQuery region: [any region]

Error Details:
When running dbt run-operation stage_external_sources for Google Sheets external tables, I'm getting:

Database Error: Braced constructors are not supported at [20:159]

Root Cause Analysis:
After investigating the create_external_table.sql macro in the BigQuery plugin, the issue appears to be in lines 39-43 where nested options like google_sheets_options are being rendered as simple key = value pairs:

{%- for key, value in options.items() %}
  {{ key }} = {{ value }}
  {%- if not loop.last -%},{%- endif -%}
{%- endfor %}

However, BigQuery expects google_sheets_options to be structured as a nested object, not a braced constructor.

Expected BigQuery Syntax:

OPTIONS (
  format = 'GOOGLE_SHEETS',
  uris = ['https://docs.google.com/spreadsheets/d/EXAMPLE_SHEET_ID'],
  google_sheets_options = {
    skip_leading_rows = 1,
    range = 'Sheet1!A1:Z1000'
  }
)

Current Generated SQL (causing error):
The macro appears to be generating something like:

google_sheets_options = {skip_leading_rows = 1, range = 'Sheet1!A1:Z1000'}

Which BigQuery rejects with "braced constructors are not supported".

Sources Configuration:

sources:
  - name: external
    tables:
      - name: example_table
        external:
          location: [region]
          options:
            format: 'GOOGLE_SHEETS'
            uris: ['https://docs.google.com/spreadsheets/d/EXAMPLE_SHEET_ID']
            google_sheets_options:
              skip_leading_rows: 1
              range: 'Sheet1!A1:H1000'

Suggested Fix:
The macro needs special handling for google_sheets_options to render it as a proper nested object structure that BigQuery accepts, rather than trying to render it as a braced constructor.

Would be happy to test any proposed fixes. This is blocking our ability to use Google Sheets as external tables in BigQuery, which can be very useful in early stages when building out a data function within a group/company.

Thanks!

[oskarabc]

Okay, ignore caught bad syntax on my part in my sources.yml, Sources Configuration should have been:

sources:

• name: external
  tables:
  • name: example_table
    external:
    location: [region]
    options:
    format: 'GOOGLE_SHEETS'
    uris: ['https://docs.google.com/spreadsheets/d/EXAMPLE_SHEET_ID']
    sheet_range: 'Sheet1!A1:H1000'
    skip_leading_rows: 1

[github-actions]

This issue has been marked as Stale because it has been open for 180 days with no activity. If you would like the issue to remain open, please comment on the issue or else it will be closed in 7 days.

[portalhacker]

I believe this is solved in recent versions and should be closed.

#347 (comment)