[Python] PyAirbyte - Collect data from a Google Sheet

I'm using PyAirbyte and Airflow to collect data from a basic Google Sheet into a Postgres table. My code runs successfully without any errors, but the tables are always created empty.

I've already confirmed that my service account has the necessary permissions, but I might be missing an extra step (maybe related to the Airbyte library or the source-google-sheets version?).

My code:

from airflow.decorators import task

@task.external_python(python="/venv_airbyte/bin/python")
def extract():
import airbyte as ab
def get_credentials():
with open(PATH_to_KEY) as f:
return f.read()

#VARIABLES
PATH_to_KEY = "my_service_account.json"
SPREADSHEET_ID = "1pYS24BwqRl5hIhVAjo1QFDsupTx67nYCTCTIF-PcVoY"
DB_HOST="localhost"
DB_PORT=5423
DB_USR=""
DB_PWD=""
DB_NAME=""


credentials = get_credentials() # get JSON Service Account credentials in STRING
source = ab.get_source(
"source-google-sheets",
pip_url="airbyte-source-google-sheets unidecode",
install_if_missing=True,
config={
"spreadsheet_id": SPREADSHEET_ID,
"credentials": {
"auth_type": "Service",
"service_account_info": credentials,
},
},
)
source.select_all_streams()

pg_cache = ab.caches.PostgresCache(
host=DB_HOST,
port=5432,
username=DB_USR,
password=DB_PWD,
database=DB_NAME,
schema_name="raw"
)
read_result = source.read(cache=pg_cache)

# Debugging: check the structure of read_result and streams
print("Read Result Streams:", read_result.streams)


An a example of my spreadsheet:

The versions that I am using:

Name: airbyte Version: 0.24.2 Summary: PyAirbyte Home-page: Author: Airbyte Author-email: contact@airbyte.io License: Location: /venv_airbyte/lib/python3.11/site-packages Requires: airbyte-api, airbyte-cdk, airbyte-protocol-models-pdv2, click, duckdb, duckdb-engine, google-auth, google-cloud-bigquery, google-cloud-bigquery-storage, google-cloud-secret-manager, jsonschema, orjson, overrides, pandas, psycopg, psycopg2-binary, pyarrow, pydantic, pydantic-core, python-dotenv, python-ulid, pytz, pyyaml, requests, rich, snowflake-connector-python, snowflake-sqlalchemy, sqlalchemy, sqlalchemy-bigquery, structlog, typing-extensions, uuid7 Required-by:
​

Continue reading...