Key Takeaways
- A single read only query runs from the sidebar and lands in a staging sheet, with no export or upload step.
- Updates and inserts run inside one transaction that rolls back on failure, so a table is never left half written.
- Only changed cells are sent back, missing rows are never deleted, and a preview gates every write.
- A pull stops at 5,000 rows and a run applies 500 changes, so a large result is worked in passes.
The usual route from a database to a spreadsheet is an export, a download and an import, repeated every time the numbers move. That route leaves CSV files on disk and stale copies in inboxes. This method keeps the round trip inside one spreadsheet: open Extensions > Flookup Data Wrangler > Data Connectors, run the query, clean the result and send the corrected rows back through the same connection.
Where the CSV export gets in the way
An exported file is a snapshot that starts aging the moment it is written. Formulas built on it point at a fixed range, the person who exported it remembers a filter nobody else can see, and a second export for comparison uses different columns. By the time three snapshots exist, the reconciliation between them is manual.
Running the query from the sheet removes the file from the flow. The result lands in a staging sheet with real column names and native values, the cleaning runs on those rows, and only the cells that were edited go back to the table. Nothing is exported, nothing is re-imported and no record is deleted.
Network access the database must allow
Apps Script connects from Google servers, so the firewall must allow Google published IP ranges for JDBC. The security group also needs a port of 1025 or higher and TLS 1.2 or later. Cloud SQL instances work with the standard URL once the instance authorises those ranges.
| 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 |
Use a read only database user for pulls. If the result will be written back, grant that user INSERT and UPDATE on the target table and nothing wider. Failed connections are counted against a daily Google quota of 100 for consumer accounts and 500 for Workspace, so a wrong password should be corrected rather than retried.
Connect the database and save the query
- 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 refused before anything reaches the database, so a mistyped query cannot become an unintended write. The password is stored in the Apps Script user properties of the Google account that set it up, is never returned to the sidebar and is removed by Clear on the card. The full control list is in the SQL connector documentation.
Run the query into a staging sheet
Click Pull to staging. The query runs with a 5,000 row cap and the result replaces the staging sheet, so the tab always holds exactly what the query returned. Column headers come from the query, which means aliases are fine for a pull, and dates, numbers and booleans arrive as their native values rather than as text.
When the cap stops the pull, the result says so. Narrow the query with a date range or a status filter and pull again, rather than pulling a table that is larger than the review can absorb. Every column header written here becomes a column that write back can recognise, so a header added later in the sheet is a column the query does not return and cannot be written.
Clean and reconcile the result
The staging sheet is an ordinary tab, so the whole toolset applies: Standard Data Cleaning for trimming, casing and standardising, fuzzy matching for names that are spelled differently, and profiling for the columns that are mostly empty. The threshold loop from fuzzy matching in Google Sheets applies unchanged to a query result.
Reconciliation is where this round trip earns its place. Match the pull against another tab, for example orders against shipping records or invoices against vendor records, flag the rows that disagree, then write the agreed values back into the table so the next query returns them. The sheet becomes the review layer rather than a copy of the truth.
Write changed rows back in one transaction
Write back needs three things: the table name, a key column that the saved query returns, and column headers that match real column names. Aliased columns are read only for write back, because an alias is not a column the table knows.
- Fill in Table for write-back and Key column.
- Click Preview changes and review the new, changed and unchanged counts.
- Click Apply changes.
The preview re-runs the query, compares every staged row against the result and shows a sample of the first rows. Apply sends changed rows as parameterised
UPDATE … WHERE key = ?
statements and new keys as
INSERT
statements inside a single transaction: autocommit is switched off, one commit closes the run, and a failure rolls everything back rather than leaving the table half changed. Identifiers are validated against
[A-Za-z_][A-Za-z0-9_]*
and values are bound with setters rather than pasted into the statement text. Rows missing from the staging sheet are never deleted, and a run applies at most 500 changes.
PostgreSQL installations that reject text bound values for typed columns need
?stringtype=unspecified
on the JDBC URL so the server can infer the type. That is the one setting that catches most first write backs.
Limits worth knowing
| Limit | Value and effect |
|---|---|
| Rows per pull | 5,000, reported when the cap stops the pull. Filter the query and pull again. |
| Changes per run | 500, updates before creations. Preview and apply again for the remainder. |
| Connections per day | 10,000 for consumer accounts and 50,000 for Workspace, counted against your Google account. |
| Failed connections per day | 100 for consumer accounts and 500 for Workspace. Correct a bad password instead of retrying it. |
The connector is included with the 1-Year Pass and the Team Pass. The 1-Month Pass and the free tier cover Standard Data Cleaning, which is enough to prove a reconciliation on a sample before anything is written to the table.
Frequently Asked Questions
Which databases can the query run against?
PostgreSQL, MySQL, Microsoft SQL Server and Oracle, through Google Apps Script's JDBC service, including Cloud SQL instances. The database must accept connections over TLS 1.2 or later on a port of 1025 or higher.
Does the connector change my database schema?
No. Pulls run a single SELECT or WITH statement, and anything else is refused before it reaches the database. Write back only inserts new rows and updates changed columns in the table that is named, and it never deletes records.
What happens if a write fails part way through?
Updates and inserts run inside a single transaction. A failure rolls the transaction back, so the table is left as it was rather than half changed.
Why does PostgreSQL reject a write back value?
PostgreSQL can refuse text bound values for typed columns. Add ?stringtype=unspecified to the JDBC URL so the server infers the type.
How many rows can one pull and one run handle?
A pull stops at 5,000 rows and reports the cap. A single write applies at most 500 changes, updates first, then tells you when the cap stopped the rest.