SQL Database Connector for Google Sheets
Introduction
The SQL connector runs a read-only query through Google Apps Script's JDBC service and writes the result to Flookup Staging. You can then clean, deduplicate or match those rows with the rest of Flookup.
Included with the 1-Year Pass and the Team Pass. The 1-Month Pass and the free trial do not include connectors. See pricing for details.
Supported databases: PostgreSQL, MySQL, Microsoft SQL Server and Oracle.
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.
| Database | JDBC URL example |
|---|---|
| PostgreSQL | jdbc:postgresql://db.example.com:5432/analytics |
| MySQL | jdbc:mysql://db.example.com:3306/analytics |
| SQL Server | jdbc:sqlserver://db.example.com:1433;databaseName=analytics |
| Oracle | jdbc:oracle:thin:@//db.example.com:1521/ORCLPDB1 |
Cloud SQL instances work with the standard URL once the instance authorises Google's IP ranges.
Connect the Database
- Enter the JDBC URL, user and password in the SQL database card.
- Enter a single SELECT or WITH query.
- Click Test query to confirm the connection and see the column names.
- 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.
- Dates, numbers and booleans arrive as their native values, not text.
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.
- Fill in Table for write-back and Key column.
- Edit the staging sheet, or clean it with Standard Data Cleaning.
- Click Preview changes and review the new, changed and unchanged counts.
- 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.
Aliased columns are read-only for write-back, because the alias is not a real column name. PostgreSQL users may need
?stringtype=unspecified
on the JDBC URL so typed columns accept the values.
Troubleshooting
| Symptom | Cause and fix |
|---|---|
| Connection refused or timed out | Google's IP ranges are not allowlisted, the port is below 1025, or TLS is older than 1.2. |
| Password authentication failed | Check the user and password. Retry sparingly: Apps Script allows only 100 failed JDBC connections a day for consumer accounts. |
| The query must return the key column | Add the key column to the SELECT list, or choose a different key. |
| Column X is not in the saved query | The staging sheet has a column the query does not return. Remove it, or add it to the query and pull again. |
Frequently Asked Questions
Which databases can Flookup connect to?
PostgreSQL, MySQL, Microsoft SQL Server and Oracle, through Google Apps Script's JDBC service. The database must accept connections over TLS 1.2 or later on a port of 1025 or higher.
Why does the connection time out?
Apps Script's servers must be allowed through the database firewall. Authorise Google's published IP ranges and confirm the port is 1025 or higher.
Can Flookup change my database schema?
No. Pulls run a single SELECT. Write-back only inserts new rows and updates changed columns in the table you name, and it never deletes records.
Why does PostgreSQL reject a write-back value?
PostgreSQL can refuse text-bound values for typed columns. Add ?stringtype=unspecified to the JDBC URL to let the server infer the type.