Automate Data from n8n Form to Google Sheets & Airtable

1. Opening Problem Statement

Meet Sara, a small business owner who organizes online workshops. Every day, she receives dozens of participant submissions through her online form, containing their name, city, and email address. Currently, Sara copies this information manually into a Google Sheet and an Airtable database to keep track of attendees.

This manual process consumes over an hour daily and is prone to mistakes like missed entries or incorrect formatting. On top of that, Sara has to send a confirmation email manually to each participant, which is tedious and slows her down from focusing on her core work.

Sara needs a reliable way to automatically collect, format, and save her participants’ data into both Google Sheets and Airtable, plus send them personalized confirmation emails—without lifting a finger.

2. What This Automation Does

This n8n workflow triggers whenever someone submits the online form, then processes and distributes the data efficiently. Here’s what happens step-by-step:

• Receive Form Submission: Captures Name, City, and Email fields immediately.
• Extract Date and Time: Parses the submission timestamp into separate Date and Time fields for clarity.
• Format Data Consistently: Standardizes fields so that database and sheet entries are uniform.
• Save to Google Sheets: Appends new entries to a designated sheet for easy spreadsheet management.
• Save to Airtable: Creates records in a specified Airtable base and table with the same data.
• Send Confirmation Emails: Sends two customized Gmail messages — a basic test and a date-labeled follow-up — to the email provided.

By automating these tasks, Sara reduces hours of manual data entry and email management each week. She eliminates errors caused by copying and pasting data, making her workflow seamless and reliable.

3. Prerequisites ⚙️

• n8n account (cloud or self-hosted) 🔌
• Google Sheets account with a spreadsheet prepared to receive form data 📊
• Airtable account with a base and table ready to store records 📁
• Gmail account authorized for sending emails via OAuth2 📧
• Basic understanding of n8n’s user interface and workflow creation 🔑

If you’re interested in self-hosting n8n to run this automation securely on your own server, you might consider solutions like Hostinger to simplify hosting setup.

4. Step-by-Step Guide

Step 1: Set Up the n8n Form Trigger

Navigate to your n8n dashboard and click to create a new workflow. Add the n8n Form Trigger node:

• Go to Nodes > Triggers > n8n Form Trigger.
• Set the webhook path (e.g., c07c8eb6-cf56-4941-91cc-e3cb31c90b5c).
• Define form fields: “What’s your name ?”, “Where do you live ?”, and “Your Email ?”, all marked as required.
• Give the form a title, such as “Data Colleacation” (misspelling in original but can be corrected to “Data Collection”).

Once done, activate the webhook URL, which will listen for form submissions.

Common mistake: Forgetting to make fields required can cause incomplete data submissions.

Step 2: Extract Date and Time from Submission Timestamp

Add a Code node named “Extracting Date and Time Fields from ‘submittedAt’ Field” right after the form trigger.

In the code editor, paste the following JavaScript:

// Loop over input items and separate date and time into two new fields
for (const item of $input.all()) {
  const submittedAt = new Date(item.json['submittedAt']);
  const date = submittedAt.toISOString().split('T')[0]; // Extract date
  const time = submittedAt.toISOString().split('T')[1].split('.')[0]; // Extract time

  delete item.json['submittedAt'];

  item.json['Date'] = date;
  item.json['Time'] = time;
}

return $input.all();

This script transforms the submission timestamp into separate “Date” and “Time” fields for easier downstream use.

Common mistake: Not deleting the original submittedAt field can lead to confusion later.

Step 3: Format the Fields for Consistency

Add a Set node called “Format the Fields” connected to the code node. Configure it with these field mappings:

• Name => {{ $json['What's your name ?'] }}
• City => {{ $json['Where do you live ?'] }}
• Date => {{ $json.Date }}
• Time => {{ $json.Time }}
• Email => {{ $json['Your Email ?'] }}

This standardizes the fields, renaming user-submitted questions into database-friendly keys.

Common mistake: Typing incorrect field names can cause errors in Google Sheets and Airtable.

Step 4: Append Data to Google Sheets

Add a Google Sheets node called “Google Sheets” connected to the “Format the Fields” node.

• Choose the Google Sheets credentials with OAuth2.
• Set the Operation to “Append”.
• Select the target Spreadsheet and Sheet (e.g., spreadsheet ID “1Ss6AEwaXpAl54YQAQDf1z6SRyh6pj719-A9eOzf2Dv4” and sheet with gid=0).
• Map the columns: Name, City, Email, Date, and Time to the fields output by the Set node.

On each form submission, a new row will be added with the participant’s details and submission timestamp.

Common mistake: Selecting the wrong sheet or spreadsheet ID will cause data to be lost or appended incorrectly.

Step 5: Create a Record in Airtable

Add an Airtable node called “Airtable” also connected to the “Format the Fields” node.

• Set your Airtable Personal Access Token credentials.
• Enter your Base ID (e.g., “appIIeJ18fnPkNyNS”) and Table ID (e.g., “tblZvKuOMmtHnv5TH”).
• Map fields Name, City, Email, Date, and Time accordingly.
• Operation should be “Create” for new records.

This keeps Airtable synced with your form data, giving you the flexibility to utilize Airtable’s views and automation.

Common mistake: Not using the exact Base and Table IDs may cause records to fail.

Step 6: Send Confirmation Email (Gmail Node)

Add a Gmail node connected to the Airtable node. Configure it as follows:

• Use your Gmail OAuth2 credentials.
• Set Send To to {{ $json.fields.Email }} (taking the email from Airtable record).
• Set Subject: “Testing Text Message Delivery”.
• Compose a text message body personalized with the subscriber’s name.

This sends a quick confirmation to each submitter.

Common mistake: Incorrect email mapping results in undelivered messages.

Step 7: Send Follow-up Email (Gmail Node 2)

Add another Gmail node called “Gmail1” connected to the “Google Sheets” node.

• Set recipients to {{ $json.Email }} from the sheet data.
• Use a subject line incorporating the date field for context.
• Write a similarly personalized message body.

This ensures submitters get a timely, friendly follow-up.

5. Customizations ✏️

• Customize Form Fields: In the n8n Form Trigger, modify or add fields tailored to your data collection needs, like phone numbers or preferences.
• Change Email Templates: Update the Gmail nodes to include HTML content or additional personalized variables by editing the message field.
• Add Conditional Logic: Insert a IF node between formatting and Google Sheets/Airtable to filter submissions based on city or date.
• Use Airtable for Updates: Instead of only creating records, configure Airtable node to update records if the email already exists, avoiding duplicates.
• Enable Slack Notification: Insert a Slack node to notify your team on new submissions in real-time.

6. Troubleshooting 🔧

Problem: “Email not sending or showing invalid recipient”

Cause: The email field mapping might be referencing the wrong path (e.g., {{ $json.fields.Email }} vs {{ $json.Email }}).

Solution: Double-check the email field source in both Gmail nodes and match to the correct output from Google Sheets or Airtable node.

Problem: “Airtable API authentication failed”

Cause: Incorrect or expired Airtable Personal Access Token.

Solution: Re-authenticate Airtable credentials via n8n’s credential settings and verify the token is valid.

Problem: “Data not appending in Google Sheets”

Cause: Improper spreadsheet ID or sheet name (gid) configuration.

Solution: Cross-check the spreadsheet ID and sheet gid values in Google Sheets node settings.

7. Pre-Production Checklist ✅

• Test form submissions to confirm webhook triggers properly.
• Verify Date and Time extraction outputs expected formats.
• Ensure Google Sheets and Airtable receive new records correctly.
• Send test emails to confirm delivery and correct personalization.
• Backup all credentials and workflow export before going live.

8. Deployment Guide

Activate the workflow once fully tested by toggling it to “Active” on the top-right of the n8n interface. Monitor executions via the n8n dashboard for errors or bottlenecks. Logs from Gmail, Google Sheets, and Airtable nodes provide detailed information for troubleshooting.

Ensure credentials are kept secure and update tokens as needed to maintain uninterrupted operation.

9. FAQs

Q: Can I use Microsoft Excel instead of Google Sheets?
A: The workflow is built specifically for Google Sheets, but you could swap that node with Excel Online if available in n8n with appropriate authentication.

Q: Does sending emails consume API credits or cost extra?
A: Sending emails via Gmail node uses your Gmail account’s normal limits; no additional API credits are consumed.

Q: Is data stored securely?
A: Data security depends on your n8n hosting and credentials policies. OAuth2 tokens prevent password sharing, but proper server security is essential.

Q: Can this handle large volumes of form data?
A: Yes, but for very high volumes, consider rate limits on Gmail and API calls for Airtable and Google Sheets.

10. Conclusion

By following this guide, you’ve automated a typical data collection and confirmation process with n8n, Google Sheets, Airtable, and Gmail nodes. Sara would save countless hours weekly by eliminating manual entry and automating emails reliably.

This workflow is an excellent starting point for any user looking to centralize form data and improve communication without custom coding.

Next, you might explore adding Slack notifications for new submissions or integrating with CRM tools to take your automation further.

Take pride in your new automation — you’ve just freed up valuable time and boosted your productivity!