Post form submissions to a Google spreadsheet with Zapier
The Aerobatic form-submit plugin allows you to collect form submissions from your static website. One of the targets you can specify is a webhook which enables all sorts of interesting integration possibilities. Combined with Zapier, it's simple to send your form submissions to a wide range of online services including: Mailchimp, Twilio, Hubspot, Salesforce, and many more.
In this blog post we'll explore how to append each submission of a simple online contact form to a Google spreadsheet. We've chosen a spreadsheet since it's a familiar way to manage data, but the steps for integrating Aerobatic with other services via Zapier will work in a similar manner.
First create the form
There's nothing fancy here, just code up a standard HTML form the way you normally would. Just be sure to set the
method to "POST" and the
target attribute to the path that the plugin is mounted at, in our case
<form target="/contact-us" method="post"> <label for="name">Name</label> <input type="text" required name="name"> <label for="email">Email</label> <input type="email" required name="email"> <label for="message"> <textarea name="message"></textarea> <button type="submit">Submit</button> </form>
Your form will need a Google reCAPTCHA which is required by the form-submit plugin in order to protect against spam submissions from bots. But fear not, unlike 1st generation CAPTCHA implementations, with their loathed cryptic text imagery, the most recent incarnation from Google is far less invasive. In fact, with the new invisible option, most users won't actually know there is a CAPTCHA present. For brevity, we'll leave out the reCAPTCHA implementation details here, but it is covered in the form-submit plugin docs.
Configure the plugin
Plugins are configured in the
aerobatic.yml manifest. Here we want to mount the plugin at the
/contact-us path and set it to only be enabled for requests using the
post method. We are using an environment variable to store the actual Zapier webhook URL (we'll get the value shortly). Note that the
path property matches the
target attribute of the
<form> element in the HTML.
plugins: - name: form-submit path: /contact-us method: post options: formName: contact-us redirectUrl: /thank-you recaptchaSecret: $RECAPTCHA_SECRET_KEY targets: - name: webhook url: $ZAP_GSHEET_WEBHOOK_URL ---
Create Zapier integration
Now let's configure the integration in Zapier. First you need a Zapier account — there's a free plan that allows up to 5 "zaps". While the steps below may initially seem daunting, the Zapier UI is quite slick and intuitive.
- Login and click "Make a Zap!"
- For the Trigger App, scroll down to the built-in apps section and select "Webhooks"
- Select the "Catch Hook" radio option, then click "Continue"
- On the "Pick a child key", leave the box blank and click "Continue"
- On the next screen Zapier will provide you the webhook URL. Copy it to your clipboard and run the following command at the root of your Aerobatic website:
aero env -n ZAP_GSHEET_WEBHOOK_URL -v <PASTE_HERE>.
- In order to complete the testing of the webhook, we need to send Zapier an actual form submission from the website. If you haven't yet, go ahead and deploy your site with the
- Now submit the form with some test data. Back in the original Zapier browser tab, the test should indicate a success.
- On the next screen, we need to set the "Action App" where form submissions will be sent. In this case that will be "Google Sheets".
- On the next screen, leave the default action as "Create Spreadsheet Row".
- Next connect your Google account to Zapier.
- In the same Google account, create a new Google spreadsheet and save it with a name of your choosing. Add the column headers in the first row which shouldcorrespond to the
nameattributes of your HTML input elements. The names don't have to match exactly since Zapier will let us establish mappings. For this example the column headers will be: name, email, and message. You should also create columns for the additional metadata fields submitted at, ip address, and location.
- Back in Zapier, use the dropdowns to select the new Google spreadsheet and the worksheet within the spreadsheet to append to. Once selected you will have the opportunity to bind the column headers to the appropriate field received in the test form submission (see Figure #1 below).
- Proceed to the final verification screen and everything should be wired up. The last step is to name your Zap and turn it to the "ON" state.
See it in action
Go ahead and submit another test form and you should see it get added to the spreadsheet. Since Google docs auto-update when changes are made by other users (or API calls in this case), you can even leave the sheet up and see new submissions appear in near real-time!
Here's a screencast showing the form-submit-demo (which uses the same exact setup as we just walked through) in action:
That's it — now each time a user submits the contact form on your Aerobatic hosted website it will show up as a new row in the spreadsheet within seconds. No wrangling with APIs and no server code or infrastructure to maintain. The process for setting up a Zapier webhook integration for other services is very similar to this one.
Now there's one less reason to hold onto that PHP or other active server backend. Static sites FTW!