Tutorial: Add Forms to Static Sites with Google Sheets
Tags: gatsby, tutorial, html, javascript
Published: 2021-05-02 17:34:50

**Originally posted on [Bootstrapper's Tales](https://bootstrapperstales.com/forms-static-site-googlesheets?utm_source=devto)**

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]("https://www.google.com/sheets") and create a blank sheet
* Name it however you'd like. In the first row insert `timestamp` and `email` as headers
![Sheet Headers](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ro9gz8r8l55bfqic3sd7.png "Sheet Headers")

* On the top bar, click on Tools > Script Editor
* Name your script
* Paste the following code into `Code.gs` (Override any code there)

```js
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
![Run initialSetup](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/ulh3s69tlekdn6bcmpq9.png "Run initialSetup")
* 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

![Google Sheets Form Trigger](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/08h9eixg3linczp0fvlv.png "Google Sheets Form Trigger")

* 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

```html
<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]("https://www.google.com/forms") 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:
![Google Sheet First Name](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hdbu9h9mqjuthvtua43q.png "Google Sheet First Name")

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](https://twitter.com/bs_tales) or visit [my blog](http://bootstrapperstales.com)**
</br>

</br>
*adapted from <a href="https://github.com/jamiewilson/form-to-google-sheets" rel="nofollow" target="_blank">jamiewilson</a>*