Sync a Google Sheet
A sync reads a sheet, maps its columns to fields, and writes each row to a destination by its key. This guide copies a Leads sheet into a Postgres table.
You need: an Owner or Admin role, and a runner group. Every runner provides syncs (sync@1); hosted runners work too, with TLS to the database.
1. Let SREInsights read the sheet
Section titled “1. Let SREInsights read the sheet”Use a Google service account (recommended):
- Add a Google service account connection with the account’s JSON key (Collect data with a job).
- In the sheet, Share it with the service account’s address as a Viewer. It doesn’t need to be readable by link.
Or read it by public link, with no connection, if the sheet is shared as “anyone with the link”. Anyone with the link can then read every column, so don’t use it for a sheet with anything private.
2. Prepare the destination
Section titled “2. Prepare the destination”Create the table with a sync_key text PRIMARY KEY column and the columns you will write, and a user that may do nothing else:
CREATE TABLE sales.leads (sync_key text PRIMARY KEY, company text, contact_date date, value numeric, synced_at timestamptz);CREATE ROLE leads_sync LOGIN PASSWORD '…';GRANT USAGE ON SCHEMA sales TO leads_sync;GRANT SELECT, INSERT, UPDATE ON sales.leads TO leads_sync; -- and nothing elseAdd a Postgres table connection with the host, database, that user, the TLS mode, the schema sales and the table leads. A superuser is refused.
For an HTTP API instead, add an HTTP API destination connection with the base URL and a bearer token. Your endpoint receives batches of up to 500 rows and answers what it did with each (sync protocol).
3. Create the sync
Section titled “3. Create the sync”Jobs → New sync. Start from Google Sheet → Postgres table, then edit the Spec (JSON):
{ "source": { "kind": "google_sheets", "spreadsheetId": "https://docs.google.com/spreadsheets/d/…/edit", "header": { "row": 1 } }, "columns": { "company": [{ "equals": "company" }], "contacted": [{ "contains": ["date"] }], "value": [{ "contains": ["value"] }] }, "fields": [ { "name": "company", "value": "col.company" }, { "name": "contact_date", "value": "col.contacted|date('%m/%d/%Y', '%Y-%m-%d')" }, { "name": "value", "value": "col.value|int" } ], "keep": [{ "if": "fields.company", "reason": "no company" }], "key": "tab.gid + ':' + fields.company|lower", "write": { "mode": "upsert" }, "destination": { "kind": "postgres" }}- Columns are found by header text. Only these columns are ever read; every other cell is dropped on the runner.
- Headers with
pass,user,login,emailore-mailare never read, whatever the spec says. - Key: what names a row. Never the row number; it changes when someone inserts a row.
Choose the source and destination connections, a Schedule (cron) such as 0 * * * *, and the runner group. Save. A sync is saved paused.
4. Preview
Section titled “4. Preview”Select Preview. A runner reads the sheet without writing (“Reading the sheet…”), and shows:
- each tab, its header row, and the columns it found,
- the rows it would leave out, and why (“no company”),
- the first 50 rows it would write, with what the destination would do with each: new, changed, unchanged or rejected. Postgres runs the writes and rolls them back.
Fix the spec until the preview looks right.
5. Switch it on
Section titled “5. Switch it on”Once the current version has a preview, switch the sync on, or use Run now. Each run reports rows created, updated, unchanged, rejected and skipped, and the first rows left out or rejected (as tab, row, key and reason, never their values). Rejected rows make the run partial.
Change the spec or its connections later, and the sync pauses at a new version until you preview it again (409 SYNC_NOT_PREVIEWED until then).
Good to know
Section titled “Good to know”- Rows deleted from the sheet stay in the destination.
- Only tabs in the sheet’s web view are read; hidden tabs only when you ask (
includeHidden, service account only). - The sync raises the usual job alerts: a failed run, and no new data if you set a freshness.
Every spec field: Sync spec.