Build a lead tracker from Google Forms and Sheets

Google Forms already sends every response to a spreadsheet. On its own, though, that sheet is just a log: nothing tells you which inquiries are complete, which ones you’ve answered, or who handles what. This guide turns that log into a small lead tracker, using only Forms, Sheets and a short Apps Script.

What you’ll build

The raw Form Responses 1 tab stays untouched. The tracker lives in a separate Leads tab, so you can sort, filter and edit it without breaking the form.

1. Collect the right fields

Keep the form short. Four questions are enough for most freelancers and small agencies:

1. Your name — short answer, required
2. Email address — short answer, required, with Response validation → Text → Email
3. Company or website — optional
4. What do you need help with? — paragraph, required

Then Responses → Link to Sheets → Create a new spreadsheet.

2. Check every response again in the sheet

“Required” in Google Forms only means something was typed. hi passes. So does an email typed into a pasted row that never went through the form. A second check in the sheet catches these:

var issues = [];
if (!lead.name) issues.push('Missing name');
if (!lead.email) issues.push('Missing email');
else if (!/^[^\s@]+@[^\s@]+\.[^\s@]{2,}$/.test(lead.email)) issues.push('Email looks invalid');
if (lead.need.length < 10) issues.push('Message is very short');
lead.status = issues.length ? 'Needs review' : 'New';

The email pattern is a format check: sam@okafor-media fails, [email protected] passes. No free check can tell whether an inbox really exists, so don’t promise more than that.

3. Give every lead a next step, an owner and a due date

A lead without an owner is a lead nobody answers. The script writes one of two next steps:

• complete lead → Reply to <name> about: <first 80 characters of their message>
• incomplete lead → Check this request and decide whether to reply

The due date is the next business day, skipping weekends:

function addBusinessDays_(date, days) {
  var d = new Date(date.getTime()), added = 0;
  while (added < days) {
    d.setDate(d.getDate() + 1);
    if (d.getDay() !== 0 && d.getDay() !== 6) added++;
  }
  return d;
}

Nothing is sent. The script only writes rows. You still reply from your own inbox and tick Done.

4. Run it on every new response

An installable trigger runs the script each time the form is submitted:

ScriptApp.newTrigger('onFormSubmit')
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();

Two details matter here:

• Use a lock. If two people submit at the same second, LockService.getDocumentLock() makes the second run wait instead of writing over the first.
• Limit the permissions. Add @OnlyCurrentDoc to the script’s header comment. Google then asks for access to this one spreadsheet instead of all your spreadsheets.

5. Test it before real leads arrive

Make 6–8 fake inquiries that cover the cases you care about: two good ones, a missing email, a malformed email, a missing name, a one-word message, and a second request from the same person. Write down what should happen before you run the script, then compare. If you re-run it and rows double up, fix that next — see how to stop duplicate Google Form entries.

Limits to know

• Apps Script has daily quotas. One run of a script like this takes well under a second, so small teams rarely notice — check Google’s current quota page.
• This is not a CRM. There’s no email history, pipeline or reporting. For a freelancer with a few inquiries a week, that’s often fine; with a team and dozens a day, you’ll outgrow it.
• You’re collecting personal data. Say why in the form description, and delete leads you no longer need.