Google Sheets lead capture chatbot for n8n

A chat widget that answers visitor questions and saves leads in a spreadsheet you already have open. This is the plainest lead capture recipe: no CRM account, no API keys and no setup workflow.

What you'll build

The bot answers from your own content, and when it has a reason to, captures the visitor's email, name and interest as a row. Calling the capture tool again later in the same conversation updates the same row, so a visitor who gives their email first and their name a few messages later still ends up as one clean row.

How it works

• A webhook receives each widget message and checks a shared secret.
• An AI agent answers from your knowledge text and calls the capture_lead tool once it has at least an email.
• The tool validates the email and uses n8n's Google Sheets node with Append or Update Row, matching on the email column. That single detail is what makes calling it more than once safe.
• There is no list to protect, but the agent still asks once, separately from answering, whether it is okay to keep their details on file. It writes consent_given as true only on a clear yes, so the column tells you later which rows are safe to follow up.

What it costs

The workflow is free to download and use. Your AI provider charges per conversation, and your n8n hosting and the plan for the service you connect apply as normal. For N8N Chat UI plans, see pricing.

Set it up

1. Create your sheet. Make a Google Sheet with a header row timestamp, email, name, interest, summary, session_id, page_url and consent_given. Column order does not matter, names do. Name the tab Leads, or choose your own tab in the node in step 4.
2. Import the workflow. Download the file with the button at the top of this page and import it with Workflows → Import from File. It holds the main chatbot and its tool workflow on one canvas, each in a labelled section.
3. Move each tool into its own workflow. n8n tools call a separate workflow, so for each tool section below the main flow: select its nodes, copy them, create a new empty workflow and paste. Save it, then open the matching tool node under the agent and select that workflow.
4. Point the tool at your sheet. In the capture lead workflow, open Append or update row, connect a Google Sheets credential (sign in with the Google account that owns the sheet) and use the document and sheet pickers to select your spreadsheet.
5. Fill in Chatbot settings. Set company_name, knowledge and a random webhook_secret.
6. Add your AI credential. Open GPT-4.1 mini model and add your OpenAI credential. Swap the node for another chat model if you prefer.

Test it

Send a test message before you connect a widget. n8n's built-in chat panel needs a Chat Trigger, so it will not open for this workflow. Instead, click Test workflow, then send a POST request to the Test URL shown on the Chat webhook node:

curl -X POST 'YOUR_TEST_URL' \
  -H 'Content-Type: application/json' \
  -d '{"chatInput":"Hello","sessionId":"test-1","metadata":{"webhook_secret":"YOUR_SECRET"}}'

The test URL listens for one request each time you click Test workflow. For a longer conversation, activate the workflow and send requests to the production URL, then read each run under Executions. Reuse the same sessionId to continue a conversation, because memory is kept per session.

Things to try:

• Ask a question and give no email. No row should be created.
• Give an email only. One row appears with the other columns blank.
• A few messages later, mention your name. The same row is updated, not duplicated.
• Decline the keep-your-details question. consent_given stays false. Agree clearly and it becomes true.

Add it to your site

Activate the workflow and use its production webhook URL as your widget's chat endpoint. Send the same secret in the widget metadata, for example metadata: { page_url: window.location.href, webhook_secret: 'YOUR_SECRET' }. The metadata docs explain how metadata reaches n8n.

Any chat front end that can POST chatInput, sessionId and metadata to a webhook and read { "output": "..." } back will work. N8N Chat UI is built for exactly this.

Troubleshooting

Rows are duplicating instead of updating

Check the header row has a column named exactly email. A typo or extra space in the header breaks the match silently.

Rows are written outside my table boundary

This is a known issue if your sheet uses Google Sheets' newer Table feature, the formatted structured table, where Append or Update Row can write outside its bounds. A plain range, the default for most sheets, is not affected, and is the more reliable choice for this recipe.

The bot replies but the tool never runs

The tool node is not linked to its workflow. Open it and select the workflow you created from the tool section. A bare placeholder ID means it is still unlinked.

My conversations reset when n8n restarts

Memory is an in-memory buffer so the template works as soon as you import it. A restart or redeploy drops conversations in progress. Before a busy launch, swap Conversation memory for Postgres Chat Memory. Nothing else changes.

I get a 401 as soon as I test

The Request from your widget? check compares metadata.webhook_secret in the request with webhook_secret in Chatbot settings. If they differ, or the request has no metadata, the workflow returns a 401 before the agent runs.

Take it further

Every column, including consent_given, is there so you can export into a real CRM later. Rows where it is blank or false were not given consent and should not be imported as if they were. See the HubSpot, ActiveCampaign and Airtable versions.