jamiewilson / form-to-google-sheets

Store HTML form submissions in Google Sheets.
Apache License 2.0
4.53k stars 800 forks source link
fetch-api google-sheets html-form javascript promise-polyfills

Submit a Form to Google Sheets | Demo

How to create an HTML form that stores the submitted form data in Google Sheets using plain 'ol JavaScript (ES6), Google Apps Script, Fetch and FormData.

1. Create a new Google Sheet

A B C ...
1 timestamp email

To learn how to add additional input fields, checkout section 7 below.

2. Create a Google Apps Script

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

function intialSetup () {
  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()
  }
}

If you want to better understand what this script is doing, checkout the form-script-commented.js file in the repo for a detailed explanation.

3. Run the setup function

4. Add a new project trigger

5. Publish the project as a web app

IMPORTANT! If you have a custom domain with Gmail, you might need to click OK, refresh the page, and then go to Publish > Deploy as web app… again to get the proper web app URL. It should look something like https://script.google.com/a/yourdomain.com/macros/s/XXXX….

6. Input your web app URL

Open the file named index.html. On line 12 replace <SCRIPT URL> with your script url:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <button type="submit">Send</button>
</form>

<script>
  const scriptURL = '<SCRIPT URL>'
  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))
  })
</script>

As you can see, this script uses the the Fetch API, a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses FormData to pass in our data as URL paramters.

Because Fetch and FormData aren't fully supported, you'll likely want to include their respective polyfills. See section #8.

Fun fact! The <html>, <head>, and body tags are actually among a handful of optional tags, but since the rules around how the browser parses a page are kinda complicated, you'd probably not want to omit them on real websites.

7. Adding additional form data

To capture additional data, you'll just need to create new columns with titles matching exactly the name values from your form inputs. For example, if you want to add first and last name inputs, you'd give them name values like so:

<form name="submit-to-google-sheet">
  <input name="email" type="email" placeholder="Email" required>
  <input name="firstName" type="text" placeholder="First Name">
  <input name="lastName" type="text" placeholder="Last Name">
  <button type="submit">Send</button>
</form>

Then create new headers with the exact, case-sensitive name values:

A B C D ...
1 timestamp email firstName lastName

8. Related Polyfills

Some of this stuff is not yet fully supported by browsers or doesn't work on older ones. Here are some polyfill options to use for better support.

Since the FormData polyfill is published as a Node package and needs to be compiled for browsers to work with, a good option for including these is using Browserify's CDN called wzrd.in. This service compiles, minifies and serves the latest version of these scripts for us.

You'll want to make sure these load before the main script handling the form submission. e.g.:

<script src="https://wzrd.in/standalone/formdata-polyfill"></script>
<script src="https://wzrd.in/standalone/promise-polyfill@latest"></script>
<script src="https://wzrd.in/standalone/whatwg-fetch@latest"></script>

<script>
  const scriptURL = '<SCRIPT URL>'
  const form = document.forms['submit-to-google-sheet']
  ...
</script>

Have feedback/requests/issues?

Please create a new issue. PRs are definitely welcome, but please run your ideas by me before putting in a lot of work. Thanks!

Related/Inspirational Articles

Documentation