On
n8n tutorial - Lesson 09: Google Sheets Automation with n8n: 4 Key Operations

Hi everyone, in this post we cover the four core Google Sheets operations in n8n — Append, Update, Read, and Lookup — using a real sandbox workflow built during Week 3 of our n8n workflow automation tutorial series. Mastering these four operations is the foundation you need before connecting Google Sheets to any AI pipeline.

How to do:

Step 1 — Create Your Google Sheet and Set Up the Sandbox Workflow

Before touching n8n, you need a Sheet with the right structure and a workflow wired to test all four operations in parallel.
  1. Create a new Google Sheet named T3-B1-Sheets-Playground with a tab called Orders.
  2. Add four column headers in row 1: id, customer, product, status.
  3. Enter three sample rows of data manually — these are your baseline records for testing Update and Lookup later.
  4. Copy the Sheet ID from the URL (the long string between /d/ and /edit) — you will need this value in every Google Sheets node. The Sheet used here has ID 1-R6kDiaLxQx02ZDGjjYptZYs6Xz0BDX8nkqOV-7JYjs.
  5. In n8n, create a new workflow named T3-B1-Sheets-Playground and add a Manual Trigger node as the entry point.
  6. From the Manual Trigger, add four separate Google Sheets nodes — one per operation — so they run as parallel branches (fan-out pattern).

Note — Keep this workflow in Manual / Sandbox state — do not activate it. Its only purpose is to let you test each operation in isolation before using Sheets inside a real production pipeline.

Step 2 — Append a New Row

The Append operation adds a brand-new row to the bottom of your sheet without touching existing data.
  1. On the first branch node, open the Google Sheets node and set Operation to Append Row.
  2. Set Document to By ID and paste your Sheet ID. Set Sheet to From List and select Orders.
  3. Under Fields to Send, map each column manually:
    • id → 4
    • customer → Trang
    • product → Giày
    • status → pending
  4. Click Execute Node and verify that row id=4 now appears at the bottom of the Orders tab.

Tip — Append Row never overwrites existing rows — it always inserts at the next empty row. This makes it the safest write operation for logging and data-collection use cases in any n8n google sheets pipeline.

Step 3 — Update an Existing Row

The Update operation finds a specific row by matching a column value and writes new data into it.
  1. On the second branch node, set Operation to Update Row.
  2. Set Document and Sheet the same way as Step 2.
  3. Set Column to Match On to id — this is the column n8n uses to find the target row. This field is required; leaving it blank will cause the node to fail.
  4. Set Match Value to 2 — this targets the row where id equals 2.
  5. Under Fields to Send, add only the field you want to change: status → delivered.
  6. Click Execute Node and open the Sheet to confirm row id=2 now shows delivered.

Production tip — The Update node has three important quirks to remember:

  • Column to Match On is mandatory — there is no default fallback.
  • Fields you leave blank are NOT overwritten — only explicitly mapped fields are updated, so partial updates are safe.
  • Upsert pattern: if you enable Append if Not Found, the node inserts a new row when no match is found — useful for sync pipelines.

Step 4 — Read All Rows

The Read (Get All) operation pulls every row from a sheet tab and converts each row into a separate n8n item — this fan-out behavior is the foundation of all downstream processing.
  1. On the third branch node, set Operation to Get Row(s).
  2. Set Document and Sheet as before. Leave the Filters section empty.
  3. Click Execute Node — the output should show 4 items, one per row in the sheet.
  4. Inspect each item in the output panel to confirm the structure: $json.id, $json.customer, $json.product, $json.status are all accessible as top-level keys.

Tip — The 1-row-to-1-item fan-out pattern is the most important concept in n8n google sheets work. Every downstream node — AI, HTTP Request, Send Email — receives one item per row and processes them independently, so your pipeline scales automatically as the sheet grows.

Step 5 — Lookup Rows by Filter (Simulated Lookup)

n8n does not have a separate "Lookup" action — you achieve it by combining Get Row(s) with a Filters condition, equivalent to a SQL WHERE clause.
  1. On the fourth branch node, set Operation to Get Row(s) — the same action as Step 4.
  2. Set Document and Sheet as before.
  3. Expand the Filters section and add one condition:
    • Column: status
    • Condition: equals
    • Value: pending
  4. Click Execute Node — the output should show 3 items (all rows where status = pending).

Note — Never assume an action exists in the UI without checking the actual action list first. The original plan assumed there was a standalone "Lookup Row" action — there is not. Always screenshot the Operation dropdown before documenting any node configuration in an n8n tutorial.

Step 6 — Build an AI Receipt Extractor That Appends to Sheets

This is the first production-grade pipeline in the series: raw receipt text goes in, structured data comes out, and a new row lands in your Sheet automatically.
  1. Create a new Google Sheet named T3-B2-Chi-tieu with a tab called Transactions. Add six column headers — no data rows, just headers:
    • date, vendor, category, items_summary, total, extracted_at
  2. Create a new workflow named T3-B2-Receipt-Extractor with this 4-node chain:
    1. Manual Trigger
    2. Edit Fields (node name: Set Receipt Text) — paste sample receipt text into a field named receipt_text.
    3. Basic LLM Chain (node name: AI Extract Receipt)
    4. Google Sheets (node name: Append to Chi Tiêu)
  3. Configure the AI Extract Receipt node:
    • Model: Claude Haiku (latest available)
    • Temperature: 0 — zero temperature for consistent, deterministic extraction
    • Prompt structure: XML with 4 blocks — <role>, <rules>, <examples>, <input>
    • Add 2 few-shot examples inside <examples> — two examples are enough to generalize across diverse receipt formats.
    • Add an Output Parser using Generate from JSON Example — paste a sample JSON with all five extraction fields.
    • The parser wraps all extracted fields under a key named output, so downstream you reference $json.output.date, $json.output.vendor, etc.
  4. Configure the Append to Chi Tiêu node:
    • Operation: Append Row
    • Document: By ID — use the Sheet ID 10g31frPtN91yxO1Xbi5rE5Y-jb1ugE8VgsNGXUKuubA
    • Sheet: Transactions
    • Map five fields from the AI output: $json.output.date, $json.output.vendor, $json.output.category, $json.output.items_summary, $json.output.total
    • Map the audit timestamp field extracted_at to the expression $now.toFormat("yyyy-MM-dd HH:mm:ss") — this records when the extraction ran, not when the transaction happened.
  5. Run a stress test with three diverse receipt types to verify extraction quality:
    • A short bank SMS from a ride-hailing service — AI must infer vendor name and parse a compact date format.
    • An e-commerce order confirmation — AI must include shipping fees in items_summary and use the final total.
    • A utility bill — AI should use a generic vendor name and take the invoice issue date as date.

Production tip — Google Sheets does not enforce column data types — there is no schema validation. The safe pattern is to always have the AI write numbers as plain numbers (e.g., 85000 not "85,000 VND") and dates in ISO format (yyyy-MM-dd). Google Sheets will auto-detect and format them correctly without any manual column-type configuration.

Key Lessons from This Session

  1. No standalone "Lookup Row" action exists in n8n. Use Get Row(s) with a Filters condition — it maps directly to a SQL WHERE clause.
  2. Always verify the UI action list before documenting it. Assuming an action exists without checking the dropdown is how incorrect documentation gets written and repeated.
  3. The 1-row-to-1-item fan-out pattern is the core mechanic of n8n Google Sheets integration. Every downstream node processes each row independently.
  4. Update Row only writes fields you explicitly map. Unmapped fields are never overwritten — partial updates are safe by default.
  5. Two few-shot examples in the prompt are enough to generalize across diverse receipt formats. Verified across four different receipt types in the stress test.
  6. Always add an audit timestamp field when appending AI-extracted data. Use $now.toFormat("yyyy-MM-dd HH:mm:ss") to record when the pipeline ran.
  7. Google Sheets does not enforce data types. Write numbers as plain integers and dates as ISO strings — the sheet handles formatting automatically.

Conclusion:

In this post we walked through all four core n8n Google Sheets operations — Append, Update, Read, and Lookup — using a parallel sandbox workflow, then applied them in a real AI receipt extraction pipeline that writes structured data directly into a sheet. These two workflows form the technical foundation for the more advanced pipelines coming up in this n8n workflow automation tutorial series, including lead capture, sales tracking, and the Week 3 final project: an automated weekly report that reads from Sheets, generates AI commentary, and outputs a Word document saved to Drive.

If you have any questions, feel free to leave a comment below. Thank you!

Tags: n8n google sheets, n8n tutorial, n8n workflow automation, google sheets automation, n8n AI extract, append row n8n, n8n beginner tutorial, workflow automation tutorial

Maybe you are interested!