Send form leads to Google Sheets automatically, without losing any

A customer calls to ask why nobody answered the quote request they sent on Tuesday. You open the leads sheet. Tuesday has two rows, both from other people. Their message never arrived, and nothing told you it was missing.

Most guides on sending form leads to Google Sheets end when the first test row appears. That part is easy, and it needs no code. The problems start later: a phone number loses its first 0, a connection stops working after a password change, or a bot finds the form. None of these show an error. The row is simply wrong, or not there.

This guide covers both parts: the quickest way to connect your form, and the checks that keep the sheet complete and correct. Everything here is done by clicking through settings. It is written for small businesses that collect enquiries on their website and work through them in a spreadsheet.

The short answer

1. If you can use Google Forms, link it to a sheet from its Responses tab. It is free and the most reliable option.
2. If your form is on your website, look in your form builder’s or website builder’s settings for a Google Sheets option and turn it on.
3. If neither works, connect the form to the sheet with Zapier, Make or a similar service.
4. Whichever you choose, do the four setup steps and the five tests below before you rely on it.

Pick the connection that fits your form

Google Forms is the safest choice because Google runs both ends. Nothing sits in between that can disconnect. If your website’s own form matters to you, the other two options work well too, as long as you check them the way this guide describes.

Five ways a lead goes missing or arrives wrong

These come up again and again in help forums. Each one passes a quick test with one made-up lead, which is why they are found late.

Step 1: Prepare the sheet before the first lead

If you link Google Forms, it creates the sheet for you. With any other connection, create a new Google Sheet, name the first tab Leads and type your column names in row 1, for example:

Then, whichever connection you use:

• Click the letter above the Phone column to select it, then choose Format, Number, Plain text. Phone numbers that arrive later keep their 0 and their +.
• Do the same for any column that holds codes with leading zeros, such as postcodes or customer numbers.
• Open File, Settings and check the time zone. The time each lead arrived is shown in this zone, so set it to the one your team works in.

Step 2: Connect the form

With Google Forms, open the form, go to the Responses tab and click Link to Sheets. Choose Create a new spreadsheet. From then on, every answer appears as a new row within seconds.

With a form builder or website builder, look in the form’s settings for Integrations, Connections or Storage, choose Google Sheets and sign in with the Google account that owns the sheet. If it lets you match form fields to columns, match each one to the column with the same name.

With Zapier, Make or a similar service, choose your form as the trigger and Google Sheets, Add row as the action. Pick the Leads tab and match each form field to its column.

In all three cases, sign in with a shared company Google account if you have one, not a personal one. When the person who connected it leaves and their account is closed, the connection stops with it.

Step 3: Get told when a lead arrives

A sheet nobody opens is where leads go to wait. Turn on an email for every new lead, so the sheet becomes the record and the email becomes the reminder.

• In Google Forms, open the Responses tab, click the three dots and choose Get email notifications for new responses.
• Most form builders have an email notification setting next to the Google Sheets one. Turn it on as well, so you hear about a lead even if the sheet connection fails.
• In Zapier or Make, add a second step that sends an email, or turn on their alerts for failed runs.

The second point matters most. Your form builder usually keeps its own list of every message sent, separately from the sheet. That list and the email are your backup if the sheet ever misses a lead.

Step 4: Turn on spam protection

Look in your form’s settings for spam protection, CAPTCHA or reCAPTCHA and turn it on. Many builders also have an invisible option that checks visitors in the background, which is less annoying for real customers than ticking boxes or picking pictures.

Google Forms has no CAPTCHA setting, but you can make the email question accept only email addresses. Click the question, open the three dots in its corner, choose Response validation, then Text and Email. That stops bots that fill every field with random text.

Step 5: Test it the way it will fail

One test lead proves very little. Before you put the form live, send these five. Together they take about ten minutes.

If the phone or formula test fails, set that column to Plain text as in step 1 and send the test again. If it still fails, the problem is in the connection, and the form builder’s or service’s support can tell you whether it can be changed. Delete the test rows when you are done.

Check once a week that nothing is missing

A broken connection does not announce itself. The simplest check takes a minute: once a week, compare the number of messages your form builder received with the number of new rows in the sheet. Most builders show the count on the form’s overview or submissions page. If the numbers differ, the missing leads are in the builder’s list and you can copy them over.

In Zapier or Make, open the history of runs and look for any marked as failed or stopped. If a service turns a connection off after repeated errors, this is where you will see it.

Working in the sheet without breaking it

The sheet is now where leads arrive and where your team works on them. A few habits keep those two jobs from getting in each other’s way:

• Use filter views (Data, Filter views) to sort or hide rows. A filter view changes only what you see, not the sheet your colleagues see.
• Add your own columns, such as Called on or Owner, to the right of the columns the form fills. Do not insert columns in the middle.
• Do not rename the tab or the column names once the connection is set up. Many connections look for them by name and stop when they change.
• Keep notes on the same row as the lead. Notes typed on a separate tab that copies the list end up next to the wrong lead when new rows arrive and the list moves down.
• To spot a customer who wrote twice, sort a filter view by the Email column; repeats end up next to each other.

A leads sheet holds personal data. Share it only with the people who reply to leads, keep a copy of the consent text your form showed, and delete leads you no longer need. Under the GDPR, a sheet is no different from a CRM.

When a sheet is no longer enough

This setup works well for one form and a few dozen leads a week. It starts to strain when leads come from several places at once: the website, a booking platform, Facebook lead ads and emails from a marketplace. Each sends the same details in a different shape, and someone ends up copying them over by hand, which is where names and phone numbers get mixed up again.

At that point it helps to have one small program that receives every source, puts each lead into the same columns, checks for repeats across all of them and writes the result to your sheet or your CRM. The rules are the ones in this guide, applied to every source the same way.