DEV Community

Pirate Prentice
Pirate Prentice

Posted on

n8n Google Sheets Node: Read, Write, and Sync Spreadsheet Data in Your Workflows (Free Workflow JSON)

#n8n #automation #googlesheets #webdev

Google Sheets is the default data store for small teams everywhere — lead lists, inventory trackers, project logs, CRM lite, budget sheets. The n8n Google Sheets node connects those sheets directly to your automation stack, letting you read rows, append new records, update cells, and delete stale data without ever opening a browser.

This guide covers every operation, common gotchas, three production-ready workflow patterns, and a free workflow JSON you can import today.

Prerequisites

  • n8n (self-hosted or cloud)
  • A Google Cloud project with the Google Sheets API enabled
  • A Google OAuth2 credential configured in n8n (or a Service Account for server-to-server access)

Google OAuth2 setup: in n8n go to Credentials → New → Google Sheets OAuth2 API. You'll need a Client ID and Secret from your Google Cloud Console. Authorize the credential; n8n stores the refresh token for you.

Operations at a Glance

Operation What It Does
Append or Update Row Write new row, or update if a matching key exists
Append Row Always add a new row at the end of the sheet
Clear Clear a defined range of cells
Create Create a new spreadsheet
Delete Delete rows matching a filter
Get Row(s) Read one or more rows
Update Row Overwrite specific columns in existing rows

Operation Deep-Dive

Append Row

The simplest write operation. Adds one row per input item to the sheet.

Key parameters:

  • Spreadsheet ID — the ID from the sheet URL (everything between /d/ and /edit)
  • Sheet Name — the tab name (case-sensitive; defaults to "Sheet1")
  • Columns — map your n8n data fields to sheet column headers

Use Mapping Column Mode: Map Each Column Manually when your column headers differ from your workflow field names. Use Auto-Map when they match exactly.

Gotcha: Append Row always adds a new row even if the record already exists. Use "Append or Update Row" when you want upsert behavior.

Append or Update Row

The upsert operation. Searches for a row where a specified column matches a value; if found, updates it; if not, appends a new row.

Key parameter: Column to Match On — pick a unique identifier (email, order ID, customer ID). Without a unique key, every run appends duplicates.

Get Row(s)

Reads rows from a sheet. Two sub-modes:

  • Get Many Rows — returns all rows (or up to a limit). Great for reading an entire list for processing.
  • Get Row by Row Number — fetch a specific row by index (1-based).

Filters: You can add a filter to return only rows where a column equals/contains a value. This runs client-side in n8n (all rows are fetched, then filtered) — not a server-side query. For large sheets (10,000+ rows) this can be slow; consider a more structured data store for high-volume lookups.

Output: Each row becomes one n8n item. Column headers become field names.

Update Row

Updates specific columns in a row you identify by row number. Useful when you already know the row index from a previous "Get Row(s)" step.

Delete Row

Deletes rows matching a filter. Supports "Delete Row" by row number or by filter match.

Warning: Deletions shift row numbers. If you delete row 5 in a loop, the old row 6 becomes row 5. Process deletions in reverse order (bottom to top) or collect all row numbers first, then delete in one batch.

Clear

Clears a defined range (e.g., A2:Z1000) without deleting the header row. Useful for resetting a reporting sheet before rewriting it.

Authentication: OAuth2 vs Service Account

OAuth2 (user credential): easiest setup, tied to your personal Google account. Works fine for personal or small-team use. Token expires and refreshes automatically; if the authorizing user's account is deleted, automations break.

Service Account: a non-human Google identity. Better for production. Steps:

  1. Create a Service Account in Google Cloud Console.
  2. Download the JSON key file.
  3. In Google Sheets, share the target spreadsheet with the service account's email address (looks like name@project.iam.gserviceaccount.com).
  4. In n8n, create a Google Sheets Service Account credential and paste the JSON key.

Service accounts don't expire and aren't tied to a person — strongly preferred for server-to-server automation.

3 Production Workflow Patterns

Pattern 1: Typeform → Google Sheets Lead Capture with Deduplication

Use case: A Typeform captures leads. You want them in Sheets, but re-submissions shouldn't create duplicate rows.

Workflow:

  1. Typeform Trigger — fires on new submission.
  2. Google Sheets – Get Row(s) — read the lead list, filter by email column matching the submission email.
  3. IF node — if the email already exists, skip; if not, continue.
  4. Google Sheets – Append Row — write the new lead.
  5. Gmail / Resend — send a welcome email.

Key config: In step 2, use Filters → Column email equals {{ $json.email }}. The IF node checks {{ $json["Google Sheets"].length }} > 0.

Pattern 2: Daily Stripe Revenue Sync to Google Sheets

Use case: Every morning, pull the prior day's Stripe charges and append them to a running revenue log in Sheets. Finance team reads the Sheet.

Workflow:

  1. Schedule Trigger — runs daily at 7 AM.
  2. Stripe – Get Charges — filter by created >= yesterday 00:00 UTC, < today 00:00 UTC. Paginate if needed.
  3. Set node — normalize fields: date, amount_usd (divide cents by 100), customer_email, description, status.
  4. Google Sheets – Append Row — write each charge to the revenue log tab.
  5. Google Sheets – Update Row — update a summary tab: total yesterday, running month total (calculate with a Code node before writing).

Tip: Use a separate "Summary" tab with formulas referencing the raw log. Let Sheets do the aggregation; n8n just feeds the data.

Pattern 3: Google Sheets as a Workflow Config Store

Use case: Non-technical team members need to control workflow behavior (e.g., which keywords to monitor, which clients are active, what email subject lines to use) without touching n8n.

Workflow:

  1. Schedule Trigger (or webhook trigger at workflow start).
  2. Google Sheets – Get Row(s) — read a "Config" tab. Each row is a config key-value pair (key, value).
  3. Code node — convert rows into a lookup object: { keyword: "n8n", recipient: "ops@company.com", ... }.
  4. Subsequent nodes use {{ $node["Code"].json.keyword }} etc. as dynamic parameters.

Why this works: It gives stakeholders a UI (Sheets) without giving them n8n access. You update logic by changing a cell, not deploying code.

6 Common Gotchas

1. Column headers must exist before writing
The Sheets node maps data to column headers. If a column header doesn't exist in row 1, data for that field is silently dropped. Always set up your header row first.

2. Spreadsheet ID vs Sheet name
The Spreadsheet ID is the long string in the URL. The Sheet Name is the tab label (e.g., "Sheet1", "Leads", "Config"). These are different — confusing them breaks authentication silently (wrong spreadsheet) or returns empty data (wrong tab).

3. Rate limits: 300 requests/minute per project
Google Sheets API enforces a 300 req/min limit. If you're processing hundreds of rows in a loop, add a Wait node (0.2s) between iterations, or switch to batch writes. Hitting the limit returns a 429 and n8n retries with exponential backoff, but this can slow workflows significantly.

4. Empty rows are returned as items with no fields
"Get Row(s)" returns empty rows as items with no fields. Add an IF node after reading: {{ Object.keys($json).length > 0 }} to filter them out.

5. Type coercion: everything comes back as a string
Sheets stores numbers and dates as text when read via API without explicit type coercion. If you need a numeric value for math, parse it: {{ parseInt($json.amount) }} or {{ Number($json.price) }}. Dates come back as strings like "7/12/2026" — parse with Luxon in a Code node if you need proper date objects.

6. Concurrent writes cause race conditions
If two workflow executions try to append rows at the same time, you can get duplicate rows or missed writes. For high-concurrency scenarios, use an n8n queue (execution mode set to "Queue") or write to a staging area and merge with a single scheduled consolidation workflow.

Free Workflow JSON

This workflow implements Pattern 2 (daily Stripe → Sheets revenue sync). Import via n8n → Import from JSON.

{
  "name": "Daily Stripe Revenue → Google Sheets",
  "nodes": [
    {
      "parameters": {
        "rule": { "interval": [{ "triggerAtHour": 7 }] }
      },
      "name": "Schedule Trigger",
      "type": "n8n-nodes-base.scheduleTrigger",
      "typeVersion": 1,
      "position": [240, 300]
    },
    {
      "parameters": {
        "resource": "charge",
        "operation": "getAll",
        "returnAll": true,
        "filters": {
          "created": {
            "gte": "={{ $now.minus({days:1}).startOf('day').toUnixInteger() }}",
            "lt": "={{ $now.startOf('day').toUnixInteger() }}"
          }
        }
      },
      "name": "Stripe - Get Yesterday Charges",
      "type": "n8n-nodes-base.stripe",
      "typeVersion": 1,
      "position": [460, 300]
    },
    {
      "parameters": {
        "values": {
          "string": [
            { "name": "date", "value": "={{ $json.created_formatted }}" },
            { "name": "customer_email", "value": "={{ $json.billing_details.email }}" },
            { "name": "description", "value": "={{ $json.description }}" },
            { "name": "status", "value": "={{ $json.status }}" }
          ],
          "number": [
            { "name": "amount_usd", "value": "={{ $json.amount / 100 }}" }
          ]
        }
      },
      "name": "Normalize Fields",
      "type": "n8n-nodes-base.set",
      "typeVersion": 2,
      "position": [680, 300]
    },
    {
      "parameters": {
        "operation": "append",
        "documentId": { "value": "YOUR_SPREADSHEET_ID" },
        "sheetName": { "value": "Revenue Log" },
        "columns": {
          "mappingMode": "autoMapInputData"
        }
      },
      "name": "Google Sheets - Append Revenue",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "position": [900, 300]
    }
  ],
  "connections": {
    "Schedule Trigger": { "main": [[{ "node": "Stripe - Get Yesterday Charges", "type": "main", "index": 0 }]] },
    "Stripe - Get Yesterday Charges": { "main": [[{ "node": "Normalize Fields", "type": "main", "index": 0 }]] },
    "Normalize Fields": { "main": [[{ "node": "Google Sheets - Append Revenue", "type": "main", "index": 0 }]] }
  }
}
Enter fullscreen mode Exit fullscreen mode

Replace YOUR_SPREADSHEET_ID with your actual spreadsheet ID and configure your Google Sheets and Stripe credentials.

Wrapping Up

The n8n Google Sheets node is one of the most-used integrations in the ecosystem for good reason — Sheets is where small-team data already lives. The key patterns that hold up in production: use Append or Update Row with a unique key to prevent duplicates, authenticate with a Service Account for stability, batch your writes to stay under rate limits, and keep a "Config" tab pattern in your back pocket for ops-managed dynamic parameters.

Want the full n8n Workflow Starter Pack? 25+ production-ready workflow templates covering lead capture, Stripe automation, AI pipelines, and more — $29 on Gumroad.

Need a custom n8n workflow built for your business? Done-for-you service — $99. Describe your automation, get a working workflow delivered.

Top comments (1)

Collapse
 
pirateprentice profile image
Pirate Prentice •

Are you using the Google Sheets node to sync data from Stripe, Typeform, or other sources? Drop your use case in the comments — always curious what data pipelines people are building with Sheets as the output layer.