Submit a Form to a Google Spreadsheet
Tags: javascript, google
Published: 2020-01-30 12:23:09

Lately, I created an HTML form where the results are sent to a Google spreadsheet. I used it for analytics purposes, but it's useful even just for keeping track of the form's results. After trying all kinds of solutions and even searching beyond the 2nd page on Google, I found something that worked for me and I thought worth sharing.

**Prerequisites:** HTML and basic JavaScript knowledge.

>The credit for this solution goes to [Jamie Wilson](https://github.com/jamiewilson) who created and uploaded it to [Github](https://github.com/jamiewilson/form-to-google-sheets).

### First, Create the Spreadsheet
1. Go to [Google Sheets](https://docs.google.com/spreadsheets) and `Start a new spreadsheet` with the `Blank` template.
Disclaimer: It doesn't really matter how you name the file.
2. On the first row, write **timestamp** on the first column and **email** on the second column.

### Create a Google Apps Script
1. Click on `Tools > Script Editor…` which should open a new tab.
2. Name the script `Submit Form to Google Sheets` or however you want for you to remember what the script does.
3. Delete the `function myFunction() {}` block withing the `Code.gs` tab.
4. Paste the following script in its place and `File > Save`:
```javascript
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, check out the [`form-script-commented.js`](https://github.com/jamiewilson/form-to-google-sheets/blob/master/form-script-commented.js) file in the repo for a detailed explanation.

###Run the setup function
1. Now, go to `Run > Run Function > initialSetup` to run this function.
2. In the `Authorization Required` dialog, click on `Review Permissions`.
3. Sign in or pick the Google account associated with this project.
4. You should see a dialog that says `Hi {Your Name}, Submit Form to Google Sheets wants to...`
5. Then click `Allow`

###Add a new project trigger
1. Click on `Edit > Current project’s triggers`.
2. In the dialog click `No triggers set up. Click here to add one now`.
3. In the dropdowns select `doPost`
4. Set the events fields to `From spreadsheet` and `On form submit`
5. Then click `Save`

###Publish the project as a web app
1. Click on `Publish > Deploy as web app…`.
2. Set `Project Version` to `New` and put `initial version` in the input field below.
3. Leave `Execute the app as:` set to `Me(your@address.com)`.
4. For `Who has access to the app:` select `Anyone, even anonymous`.
5. Click `Deploy`.
6. In the popup, copy the `Current web app URL` from the dialog.
7. And click `OK`.

> **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…`.

###Input your web app URL
Open the file named `index.html`. On line 7 replace `<SCRIPT URL>` with your script url:

```html
<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 [Fetch API](https://developer.mozilla.org/en-US/docs/Web/API/Fetch_API), a fairly new promise-based mechanism for making web requests. It makes a "POST" request to your script URL and uses [FormData](https://developer.mozilla.org/en-US/docs/Web/API/FormData) to pass in our data as URL parameters.

###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:

```html
<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, you can create new headers with the exact, case-sensitive `name` values. Just go to the spreadsheet and add the names to the first row, each *name* in a different column.

###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.
1. [Promise Polyfill](https://github.com/taylorhakes/promise-polyfill)
2. [Fetch Polyfill](https://github.com/github/fetch)
3. [FormData Polyfill](https://github.com/jimmywarting/FormData)

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](https://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.:

```html
<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>
```

And... Yep, this is it.
After trying several different methods to do this "simple" action, that's the only one that worked for me - so I hope it helps. Feel free to share it/save it for future use and feel free to comment in case you have ideas to improve it!