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.- Create a new Google Sheet named T3-B1-Sheets-Playground with a tab called Orders.
- Add four column headers in row 1:
id,customer,product,status. - Enter three sample rows of data manually — these are your baseline records for testing Update and Lookup later.
- 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 ID1-R6kDiaLxQx02ZDGjjYptZYs6Xz0BDX8nkqOV-7JYjs. - In n8n, create a new workflow named T3-B1-Sheets-Playground and add a Manual Trigger node as the entry point.
- 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.- On the first branch node, open the Google Sheets node and set Operation to Append Row.
- Set Document to By ID and paste your Sheet ID. Set Sheet to From List and select Orders.
- Under Fields to Send, map each column manually:
id→4customer→Trangproduct→Giàystatus→pending
- Click Execute Node and verify that row
id=4now 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.- On the second branch node, set Operation to Update Row.
- Set Document and Sheet the same way as Step 2.
- 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. - Set Match Value to
2— this targets the row whereidequals2. - Under Fields to Send, add only the field you want to change:
status→delivered. - Click Execute Node and open the Sheet to confirm row
id=2now showsdelivered.
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.- On the third branch node, set Operation to Get Row(s).
- Set Document and Sheet as before. Leave the Filters section empty.
- Click Execute Node — the output should show 4 items, one per row in the sheet.
- Inspect each item in the output panel to confirm the structure:
$json.id,$json.customer,$json.product,$json.statusare 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 SQLWHERE clause.
- On the fourth branch node, set Operation to Get Row(s) — the same action as Step 4.
- Set Document and Sheet as before.
- Expand the Filters section and add one condition:
- Column:
status - Condition: equals
- Value:
pending
- Column:
- 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.- 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
- Create a new workflow named T3-B2-Receipt-Extractor with this 4-node chain:
- Manual Trigger
- Edit Fields (node name: Set Receipt Text) — paste sample receipt text into a field named
receipt_text. - Basic LLM Chain (node name: AI Extract Receipt)
- Google Sheets (node name: Append to Chi Tiêu)
- 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.
- 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_atto the expression$now.toFormat("yyyy-MM-dd HH:mm:ss")— this records when the extraction ran, not when the transaction happened.
- 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_summaryand 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
- No standalone "Lookup Row" action exists in n8n. Use Get Row(s) with a Filters condition — it maps directly to a SQL
WHEREclause. - 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.
- 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.
- Update Row only writes fields you explicitly map. Unmapped fields are never overwritten — partial updates are safe by default.
- 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.
- 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. - 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!
- Getting Started with n8n: Interface & Your First Manual Trigger
- n8n HTTP Request Node: Connect Any API Without Code
- Branching Workflows: IF, Switch & Merge Nodes in n8n
- n8n Expressions & Built-in Variables: The Complete Guide
- Comparing AI Models in n8n: Claude vs Gemini vs ChatGPT
- Connect Gmail to n8n: OAuth Setup & Reading Emails
- Build an AI Email Classifier with n8n
- Automated Email Digest to Telegram with n8n
