BigQuery access Google Sheets for DBT

google_sheets_dbt.sql

{{ config(materialized = 'table') }}

{%- call statement('create_ext_table', fetch_result=False) -%}

    CREATE OR REPLACE EXTERNAL TABLE {{ this.database+"."+this.schema+"."+this.table+"_ref" }} (
        Student_Name STRING,
        Gender STRING,
        Class_Level STRING,
        Home_State STRING,
        Major STRING,
        Extracurricular_Activity STRING
    )

    OPTIONS (
        FORMAT = 'GOOGLE_SHEETS',
        URIS = [
            'https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit'
        ],
        MAX_BAD_RECORDS = 0,
        --SHEET_RANGE = "Class Data",
        SKIP_LEADING_ROWS = 1
    );

{%- endcall -%}

SELECT
    Student_Name AS student_name,
    Gender AS gender,
    Class_Level AS class_level,
    Home_State AS home_state,
    Major AS major,
    Extracurricular_Activity AS extracurricular_activity
FROM
    {{ this.database+"."+this.schema+"."+this.table+"_ref" }}
WHERE
    Student_Name IS NOT NULL