Skip to content

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.

Use a Google service account (recommended):

  1. Add a Google service account connection with the account’s JSON key (Collect data with a job).
  2. 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.

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 else

Add 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).

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, email or e-mail are 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.

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.

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).

  • 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.