SQL Database Connector for Google Sheets

Introduction

Live records usually sit in a database while the reconciliation happens in a spreadsheet. The SQL connector runs a read-only query through Google Apps Script's JDBC service and writes the result to Flookup Staging, so you can clean, deduplicate or match those rows with the rest of Flookup, then send the changes back through a key column.

Supported databases: PostgreSQL, MySQL, Microsoft SQL Server and Oracle.

It never changes your schema, never deletes rows and does not run on a schedule in this version.

Before You Start

A SQL pull depends on a network path, a database account and one query. Settle all three before you open the connector card.

• Network. The database firewall must accept Google's JDBC ranges, on a port of 1025 or higher, over TLS 1.2 or later. Google publishes the ranges in its Apps Script JDBC documentation.
• Query. Write a single SELECT or WITH statement that already returns the key column, because write-back cannot use a key the query does not return.
• Account. Use a user that can only read. Add INSERT and UPDATE on the one table you write to if you need write-back, and nothing wider.
• Pass. Connectors come with the 1-Year Pass and the Team Pass, not with the shorter pass or the trial.

If your database sits inside a private network with no public endpoint, the connector cannot reach it. Export the result there and use a CSV join instead.

Network Access

Apps Script connects from Google's servers, so your database firewall must allow Google's published IP ranges for JDBC. The security group also needs a port of 1025 or higher and TLS 1.2 or later.

Use a read-only database user for pulls. If you also want write-back, grant that user INSERT and UPDATE on the target table, and nothing wider.

Cloud SQL instances work with the standard URL once the instance authorises Google's IP ranges.

Connect the Database

1. Enter the JDBC URL, user and password in the SQL database card.
2. Enter a single SELECT or WITH query.
3. Click Test query to confirm the connection and see the column names.
4. Click Save.

Only one statement is allowed. Inserts, updates, deletes and stacked statements are rejected before anything reaches the database. The password is stored in your Apps Script user properties and is never returned to the sidebar.

Run the Query

Click Pull to staging. The query runs with a 5,000 row cap, and the result replaces the staging sheet.

• Column headers come from the query, so aliases are fine for a pull.
• The pull reports when the row cap stopped it. Add a filter or a smaller range instead of pulling everything.
• Values arrive as text, because every column is read as a string. Apply number or date formatting in the sheet after the pull, and remember that write-back sends the cell text back to the database.

Query design decides how much cleanup the staging sheet needs. Name the columns you want instead of selecting everything, because a wide table wastes the row budget and hides the fields you actually use.

Filter to the slice you need. A date range, a status or a customer segment keeps the result under the 5,000 row cap and makes each pull repeatable.

Choose a key that is unique, never reused and not edited by hand. An invoice number works; a customer name does not, because it changes and two customers can share one. Rows with a NULL key cannot be matched, so exclude them in the query.

Write Changes Back

Write-back needs three things: the table name, a key column that the saved query returns, and column headers that match real column names.

1. Fill in Table for write-back and Key column.
2. Edit the staging sheet, or clean it with Standard Data Cleaning.
3. Click Preview changes and review the new, changed and unchanged counts.
4. Click Apply changes.

Changed rows run as parameterised UPDATE … WHERE key = ? inside a transaction, and new keys run as INSERT. Rows missing from the staging sheet are never deleted. A run applies at most 500 changes.

Updates and inserts share one transaction. If a statement fails, the run rolls back instead of leaving part of the table changed. Apply is tied to the preview you just saw: if the staging sheet or the query result changes in between, the write is refused. A preview stays valid for 15 minutes and can be used once.

Each committed change is appended to a Flookup Changes sheet with the timestamp, table, key, field, before value and after value. A rolled-back run writes nothing to the log, because nothing changed.

Aliased columns are read-only for write-back, because the alias is not a real column name. If the saved query hits the 5,000 row cap, update-only runs still work, but new rows are refused, because the connector cannot check whether they already exist. PostgreSQL users may need ?stringtype=unspecified on the JDBC URL so typed columns accept the values.

Test on a copy first. Point Table for write-back at a scratch table with the same columns, apply one changed row, and check the result there. Pointing it at the live table before that test is how a cleanup pass becomes an incident.

When the test passes, change the table name and preview once more. The preview reads the live table, so a column that exists only in the copy will show up as a mismatch before anything is written.

Troubleshooting

Frequently Asked Questions

You Might Also Like

• Data Connectors
• Airtable Connector
• Import SQL Query Results Into Google Sheets Without a CSV
• Data Cleaning Automation
• Standard Data Cleaning