Hey!
I’m trying to define an external table from a Google sheet in Dataform - only a few columns from a specific sheet should be used.
Some AI tools are saying that ‘range’ can be used in OPTIONS, but I guess it’s misleading - I’m getting “Syntax error: Unexpected keyword RANGE”.
OPTIONS( range = ‘Sheetname!A1:E’ )
Is there a workaround or currently this is not something supported?
Thank you!
My recommendation is to approach the puzzle in two parts. Let us remember that in the end, Dataform results in SQL statements that are then executed. Your goal is to create a table definition in BigQuery that is of type “EXTERNAL TABLE” that references a Google Sheet that is located/stored on your Google Drive. You also want to specify a “range” from the sheet.
As such, you should first test out creating the table definitions without reference to Dataform. Try and get it working in BigQuery Studio. Once you have the SQL statement right, THEN you can translate this to Dataform.
Looking in the Google docs, I find:
As the definition of the statement that creates EXTERNAL TABLE definitions in BigQuery. Looking in those docs, we see that there is an option called “sheet_range” that specifies the range within the sheet.
And this relates to your question “range” is not a valid option but “sheet_range” is.
Hey,
Hope you’re keeping well.
RANGE is not a valid option in BigQuery’s CREATE EXTERNAL TABLE syntax, so Dataform will throw a syntax error if you try to use it in OPTIONS. For Google Sheets external tables, BigQuery only supports specifying the sheet name via skip_leading_rows or sheet_range in the connection UI, but sheet_range is only available when defining the table through the BigQuery Console or API, not directly via SQL DDL in Dataform.
If you need only certain columns, the typical approach is to define the external table over the whole sheet and then create a view in Dataform that selects the subset of columns you need. This keeps the external table definition valid while letting you work with just the desired range in downstream transformations.
Thanks and regards,
Taz