Supabase to Google Sheets: Connection Pooler Gotchas and the Right Endpoint to Use

Connecting Supabase to Google Sheets is not complicated: until it is. The most common failure mode is using the wrong endpoint for the wrong job, which either exhausts your connection pool on the free tier or silently breaks logical replication. This guide gives you the exact connection strings and methods that work, and explains why each one exists.

Why the Endpoint Choice Matters

Supabase exposes three distinct ways to connect to Postgres. Each one has a specific purpose. Using the wrong one for your use case is the single biggest cause of failed or unreliable Supabase-to-Sheets syncs.

The short version: use the transaction pooler on port 6543 for short-lived connections like Apps Script and scheduled Python scripts. Use the direct connection on port 5432 only when you need logical replication (CDC). Never use the session pooler for high-frequency automated queries on the free tier.

The Three Supabase Endpoints Explained

Direct Connection (Port 5432)

This connects directly to the underlying Postgres instance. It supports all Postgres features including logical replication, LISTEN/NOTIFY, and prepared statements with full session state.

postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres

Use this for: CDC tools, migrations, long-running scripts that need session-level features.

Do not use this for: Apps Script, short-lived Lambda functions, or anything that opens and closes connections frequently. Each connection persists and counts against your Postgres max_connections limit (which is 60 on the free tier).

Session Pooler (Port 5432 via PgBouncer in session mode)

Supabase also offers PgBouncer in session mode. Each client gets a dedicated server connection for the life of the session. This does not meaningfully help with connection limits for short-lived clients. You still tie up a server connection per session.

Transaction Pooler (Port 6543)

This is the right choice for most Google Sheets integrations. PgBouncer in transaction mode multiplexes many client connections over a small pool of server connections. A connection is only held while a transaction is in progress. The moment your query commits, the server connection returns to the pool.

postgresql://postgres.[project-ref]:[password]@aws-0-[region].pooler.supabase.com:6543/postgres

Limitations: No support for SET statements that persist across transactions, no LISTEN/NOTIFY, no prepared statements in the traditional sense (use ? parameter markers), and no logical replication.

Free Tier Pool Exhaustion: The Real Problem

Supabase free tier projects have a hard limit of 60 Postgres connections. This sounds like a lot until you realize:

• Supabase's own internal services (PostgREST, Auth, Realtime, Storage) consume roughly 10–20 of those connections permanently.
• Each direct connection from your script holds a slot open even during idle time, because Postgres does not release a connection until the client explicitly closes it.
• Google Apps Script runs in Google's infrastructure. Connection pooling state is not preserved between script executions. Every trigger run opens a fresh connection.

If you have a time-based trigger running every minute and each run takes 15 seconds to complete, you can end up with 4 concurrent open connections from Apps Script alone. Add a few other integrations and you hit the ceiling.

The fix: Always use port 6543 (transaction pooler) from Apps Script and any other stateless client. Your effective concurrent connection count drops dramatically because PgBouncer multiplexes across a small server-side pool.

Method 1: Apps Script JDBC with the Transaction Pooler

Apps Script has a built-in JDBC service that can connect to any Postgres-compatible database. The critical detail is the connection string format: Apps Script expects jdbc:postgresql:// not postgresql://.

Step 1: Get your connection string from Supabase

In your Supabase dashboard, go to Project Settings > Database > Connection string. Select URI and copy it. It will look like:

postgresql://postgres.[project-ref]:[password]@aws-0-us-east-1.pooler.supabase.com:6543/postgres

Step 2: Convert to JDBC format

Replace postgresql:// with jdbc:postgresql://:

jdbc:postgresql://aws-0-us-east-1.pooler.supabase.com:6543/postgres?user=postgres.[project-ref]&password=[password]

Note that the user credentials move to query parameters in the JDBC format. The username must include the project ref suffix (e.g., postgres.abcdefghijkl), not just postgres.

Step 3: Write the Apps Script

function syncSupabaseToSheets() {
  const connString = 'jdbc:postgresql://aws-0-us-east-1.pooler.supabase.com:6543/postgres';
  const user = 'postgres.YOUR_PROJECT_REF';
  const password = 'YOUR_PASSWORD';

  const conn = Jdbc.getConnection(connString, user, password);
  const stmt = conn.createStatement();
  const results = stmt.executeQuery('SELECT id, name, email, created_at FROM users ORDER BY created_at DESC LIMIT 1000');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Users');
  sheet.clearContents();

  // Write headers
  const meta = results.getMetaData();
  const numCols = meta.getColumnCount();
  const headers = [];
  for (let i = 1; i <= numCols; i++) {
    headers.push(meta.getColumnName(i));
  }
  sheet.appendRow(headers);

  // Write data
  while (results.next()) {
    const row = [];
    for (let i = 1; i <= numCols; i++) {
      row.push(results.getString(i));
    }
    sheet.appendRow(row);
  }

  results.close();
  stmt.close();
  conn.close();
}

Set a time-driven trigger on this function (e.g., every hour) via Triggers > Add Trigger in the Apps Script editor.

Performance tip: sheet.appendRow() inside a loop is slow for large result sets. For more than a few hundred rows, build a 2D array and write it in a single sheet.getRange().setValues() call.

Method 2: Python with supabase-py and gspread

If you need more control, custom transformations, error handling, or scheduling via cron or a serverless function. Python is the better choice.

Dependencies

pip install supabase gspread google-auth

Service Account Setup

1. Create a Google Cloud project and enable the Google Sheets API and Google Drive API.
2. Create a service account and download the JSON key file.
3. Share your Google Sheet with the service account email address (give it Editor access).

The Script

import gspread
from google.oauth2.service_account import Credentials
from supabase import create_client

SUPABASE_URL = "https://YOUR_PROJECT_REF.supabase.co"
SUPABASE_KEY = "your_service_role_key"  # Use service role key for server-side scripts
SHEET_ID = "your_google_sheet_id"
SERVICE_ACCOUNT_FILE = "/path/to/service-account.json"

def sync_to_sheets():
    # Connect to Supabase
    sb = create_client(SUPABASE_URL, SUPABASE_KEY)

    # Fetch data (supabase-py uses the REST API, not a direct Postgres connection)
    response = sb.table("orders").select("*").order("created_at", desc=True).limit(5000).execute()
    rows = response.data

    if not rows:
        print("No data returned")
        return

    # Connect to Google Sheets
    scopes = [
        "https://www.googleapis.com/auth/spreadsheets",
        "https://www.googleapis.com/auth/drive"
    ]
    creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=scopes)
    gc = gspread.authorize(creds)
    sh = gc.open_by_key(SHEET_ID)
    worksheet = sh.worksheet("Orders")

    # Prepare data
    headers = list(rows[0].keys())
    values = [headers] + [[str(row.get(h, "")) for h in headers] for row in rows]

    # Write in one batch call
    worksheet.clear()
    worksheet.update("A1", values)
    print(f"Synced {len(rows)} rows")

if __name__ == "__main__":
    sync_to_sheets()

Note that supabase-py uses the PostgREST REST API by default, not a direct Postgres connection. This means you avoid the connection pooling issue entirely. The trade-off is that complex SQL (window functions, CTEs, multi-table joins) is harder to express. For those cases, use psycopg2 with the transaction pooler connection string directly.

Method 3: Third-Party Connectors

If you want a no-code or low-code solution with automated scheduling, several tools support both Supabase and Google Sheets:

• Fivetran / Airbyte: Full ELT pipelines. Overkill for most Sheets use cases but solid if you also need a data warehouse.
• Zapier: Works well for event-driven syncs (new row in Supabase → append to Sheet). Not ideal for bulk historical exports.
• Make.com (formerly Integromat): More flexible than Zapier, supports multi-step scenarios with transformations.
• Estuary Flow: Particularly good for CDC (change data capture), but see the next section for its specific endpoint requirements.
• brooked.io: Connector purpose-built for database-to-Sheets syncs, handles the connection string format and pooler configuration automatically.

CDC via Estuary: Why You Need the Direct Endpoint

Change data capture tools like Estuary Flow read the Postgres write-ahead log (WAL) to stream row-level changes in real time. Logical replication (the Postgres feature that makes this possible) requires a persistent, stateful connection to the primary database.

This is fundamentally incompatible with the transaction pooler. PgBouncer in transaction mode does not support the replication protocol. If you point Estuary (or Debezium, or any other CDC tool) at port 6543, you will get an error like:

ERROR: replication connections must not be proxied

For CDC, you must use the direct connection endpoint on port 5432:

postgresql://postgres.[project-ref]:[password]@db.[project-ref].supabase.co:5432/postgres

Note the hostname is different: it uses db.[project-ref].supabase.co, not the pooler hostname.

You also need to enable logical replication in Supabase (Project Settings > Database > Replication) and create a publication:

CREATE PUBLICATION supabase_realtime FOR TABLE your_table;

Estuary will create the replication slot automatically, but your Supabase plan must support it (Pro plan or higher for production CDC use).

Comparison Table

Troubleshooting

"too many connections" error You are using the direct connection (port 5432) from a stateless client. Switch to port 6543.

"prepared statement does not exist" error You are using the transaction pooler (port 6543) with a library that uses server-side prepared statements by default. Disable prepared statements: add ?prepareThreshold=0 to your JDBC connection string, or set options=-c default_transaction_isolation=read\ committed depending on your driver.

Apps Script throws "Invalid argument" on the connection string The JDBC format requires jdbc:postgresql:// not postgres:// or postgresql://. Also verify the username includes the project ref (e.g., postgres.abcdefgh).

Data writes to the sheet are slow You are likely calling appendRow() in a loop. Switch to building a 2D array and writing it with setValues() in a single call. This reduces API round trips from N to 1.

Estuary / CDC getting "replication connections must not be proxied" You are pointing the CDC tool at the pooler. Switch to the direct endpoint: db.[project-ref].supabase.co:5432.

Authentication fails with the correct password On the transaction pooler, the username must include the project ref as a suffix. Use postgres.abcdefghijkl not postgres.

Bottom Line

The right endpoint depends entirely on what your client needs. For Google Sheets syncs (whether through Apps Script, Python, or a connector) the transaction pooler on port 6543 is the correct choice in almost every case. It avoids free-tier pool exhaustion, handles stateless connection patterns gracefully, and is actively maintained by Supabase.

Reserve the direct connection on port 5432 for CDC tools like Estuary that require logical replication. Never point a CDC tool at the pooler, and never point Apps Script at the direct connection without aggressive connection management.

Get the connection string format right (JDBC requires jdbc:postgresql://, not postgresql://), include the project ref in your username, and you will avoid 90% of the errors people hit when connecting Supabase to Google Sheets.

Get Syncing Faster

brooked.io handles the connection string format, endpoint selection, and pooler configuration automatically. Connect your Supabase project in minutes and schedule syncs to Google Sheets without writing a line of code.

Related guides on brooked.io:

• Neon Postgres to Google Sheets: The Definitive Guide
• WooCommerce Orders to Google Sheets: Sync Your Store Data Automatically
• Postgres to Google Sheets: Complete Connection Guide

Troubleshooting quick reference

Frequently asked questions