Tutorial: Add Forms to Static Sites with Google Sheets

Originally posted on Bootstrapper's Tales

I decided to add a guides section to the blog. The plan is simple - every time I spend too much time figuring something out, I'll write a guide about it.

This guide is about how to create an HTML form that stores responses directly to a Google Sheet. It's how I set up the email sign-up form you see at the bottom of the page 👇👇👇

It's easy, requires no extra services, and takes around 10 minutes. The perfect solution to collect emails on a small static website.

Step 1: Prepare the Google Sheet

• Go to Google Sheets and create a blank sheet

• Name it however you'd like. In the first row insert timestamp and email as headers
  

• On the top bar, click on Tools > Script Editor

• Name your script

• Paste the following code into Code.gs (Override any code there)
  

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()

function initialSetup () {
var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', activeSpreadsheet.getId())
}

function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)

try {
    var doc = SpreadsheetApp.openById(scriptProp.getProperty('key'))
    var sheet = doc.getSheetByName(sheetName)

    var headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
    var nextRow = sheet.getLastRow() + 1

    var newRow = headers.map(function(header) {
    return header === 'timestamp' ? new Date() : e.parameter[header]
    })

    sheet.getRange(nextRow, 1, 1, newRow.length).setValues([newRow])

    return ContentService
    .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
    .setMimeType(ContentService.MimeType.JSON)
}

catch (e) {
    return ContentService
    .createTextOutput(JSON.stringify({ 'result': 'error', 'error': e }))
    .setMimeType(ContentService.MimeType.JSON)
}

finally {
    lock.releaseLock()
    }
}

• Save the script

Step 2: Setup and publish the project

• On the top, choose initialSetup function and click run
• Authorize the script with your Google Account
• On the left, click "Triggers" (clock icon) and then Add Trigger.
• Choose the following settings and click save

• On the top, click Deploy > new deployment
• Select type (cog-wheel icon) > Web app
• Insert description, choose "Execute as Me", set access to "Anyone" and finally hit deploy
• Take note of the web app URL that is provided on screen

Step 3: add the form to your website

• Add the code below to your website. Replace YOUR_WEB_APP_URL with your web app URL

<form style="text-align:center;" name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <button id="email-submit" type="submit">Send</button>
</form>
<script>
  const scriptURL = YOUR_WEB_APP_URL
  const form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    submit_button = document.querySelector("button#email-submit");
    submit_button.innerText = "Sending...";
    fetch(scriptURL, { method: 'POST', body: new FormData(form)})
      .then(function(response){
                console.log('Success!', response);
                submit_button.innerText = "Done!";
                })
      .catch(function(error) {
                console.error('Error!', error.message);
                submit_button.innerText = "Error!";
                })
  })
</script> 

Extras

1. Add a Google Form fallback

I'm not 100% sure what the capacity of this method is. Can it handle 1000 form submits (I wish 😅) within a minute?

So, just to make sure, I implemented a fallback. In case of failure, my website opens a new tab with a Google Form to submit an email.

• Create a Google From and set up the relevant questions (i.e. email)
• Add the following snippet when your post request fails. Replace GOOGLE_FORM_LINK with the link to your form

window.open(GOOGLE_FORM_LINK);

• So all in all your html code should look like this

<form style="text-align:center;" name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <button id="email-submit" type="submit">Send</button>
</form>
<script>
  const scriptURL = YOUR_WEB_APP_URL
  const form = document.forms['submit-to-google-sheet']

  form.addEventListener('submit', e => {
    e.preventDefault()
    submit_button = document.querySelector("button#email-submit");
    submit_button.innerText = "Sending...";
    fetch(scriptURL, { method: 'POST', body: new FormData(form)})
      .then(function(response){
                console.log('Success!', response);
                submit_button.innerText = "Done!";
                })
      .catch(function(error) {
                console.error('Error!', error.message);
                submit_button.innerText = "Error!";
                window.open(GOOGLE_FORM_LINK);
                })
  })
</script> 

2. Collect more fields

To capture more fields in your static site form, simply:

1. Add headers to your Google Sheet
2. Add <input> to your html form with a name attribute that exactly matches your header.

For example, let's add first names. Your Google Sheet:

Your HTML static form:

<form style="text-align:center;" name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <input name="first_name" type="text" placeholder="Your Name" required>
  <button id="email-submit" type="submit">Send</button>
</form>

I hope you enjoyed my guide! For more, follow me on Twitter or visit my blog

adapted from jamiewilson