Collect leads in a Google Sheet with a free Apps Script webhook (no Zapier)

Zapier and Make are popular tools for automating workflows, but their monthly fees can become expensive when handling just a few dozen leads. A cost-effective alternative is a Google Apps Script web app, which provides a free HTTPS endpoint to directly write submissions into a Google Sheet. Here's how to set it up. Open a Google Sheet, navigate to Extensions > Apps Script, and paste the provided code.

The script defines a SECRET token, which should be replaced with a unique value. When a POST request is made to the script's endpoint, it first checks if the token in the request matches the SECRET. If not, it returns an error. Next, it validates the email address provided in the request, ensuring it follows the standard format. If the email is valid, the script appends a new row to the current sheet, containing the current date, name, email, and two empty columns for future use.

After deploying the script as a web app and copying the resulting URL, any HTML form can be configured to post its data to this endpoint. The form action attribute should be set to the copied URL, with the method set to POST. Include input fields for name and email, and a hidden field for the SECRET token value.

When a form is submitted, the data will be recorded as a new row in the sheet, with columns for timestamp, name, email, and two additional empty columns. While this method provides a simple and free solution for handling leads, it does have limitations. The token is visible in the public URL, so it only serves to deter basic bots and not determined humans. For sensitive data, consider adding additional security measures like a honeypot field.

Anyone with the URL can post data to the script, so it's crucial to use separate scripts for private data and limit access accordingly. Also, note that Google Apps Script has limits on concurrent executions and daily runtime. For most lead volumes, these limitations won't be an issue, but it's still worth testing under heavy traffic conditions.

Once leads are collected in the sheet, they can be automatically processed using additional scripts. The auto-reply script, mentioned in a previous post, can acknowledge the submission within minutes. A follow-up script can then be used to monitor the lead's activity, sending a reminder after 48 hours if there's been no response.

All three components - the webhook, auto-reply, and follow-up scripts - are provided with editable email templates and a straightforward setup guide in the LeadReply Kit. This comprehensive solution allows for a streamlined process of collecting, recording, and managing leads without incurring any costs.