Google Spreadsheet integration with a web form

How to make spreadsheet receive leads directly from a landing page?

One of the best solutions would be to transform a google spreadsheet into a web app and post data to the app directly. To make it happen, we will need to create and format a new spreadsheet, then add a custom script to make it receive the data from the landing page form.

Spreadsheet (microCRM) has to receive a user data and be able to report a sale later on back to Google Analytics and Google Ads/Meta Ads (also called offline conversions or post-conversions).

1. Create a Google Spreadsheet

Create a new spreadsheet with the following columns (case sensitive):

timestamp name email gclid cid

2. Setup the Google Spreadsheet

Check the following github repository called “Submit a Form to Google Sheets” where you can find step by step instructions and a script that needs to be copied.

3. Update the code on the landing page

Here’s the html form:

<form name="submit-to-google-sheet">
<input type="text" name="name">
<input type="email" name="email">
<input type="hidden" name="cid" id="cid">
<input type="hidden" name="gclid" id="gclid">
<input type="submit">
</form>

Here’s the javascript code that sends the data from a form to the spreadsheet:

const scriptURL = 'https://script.google.com/macros/s/YOUR-UNIQUE-ID'
const form = document.forms['submit-to-google-sheet']

form.addEventListener('submit', e => {
e.preventDefault()
fetch(scriptURL, { method: 'POST', body: new FormData(form)})
.then(response => console.log('Success!', response))
.catch(error => console.error('Error!', error.message))
})

YOUR-UNIQUE-ID can be found on "deploy" page.

4. Create a copy of your spreadsheet

In order to have a safe copy of your database, let’s create a new spreadsheet and include the formula for the Measurement Protocol link.

In the new spreadsheet use ImportRange to import columns from the original spreadsheet:

Add 3 additional columns that are needed for the Measurement Protocol link to be generated: Value; Sale Closed?; Offline Conversion

In the “Offline conversion” column include the formula:

=ArrayFormula(IF(G2:G="Yes",HYPERLINK("http://www.google-analytics.com/collect?v=1&tid=UA-XXXXXXXX-1&t=event&ec=Lead&ea=Closed&el=Won&ev="&F2:F&"&gclid="&D2:D&"&cid="&E2:E,"send")))

Where UA-XXXXXXXX-1 is your counter ID.

Example