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.
- First, go to Google Sheets and
Start a new spreadsheet
with theBlank
template. - Rename it
Email Subscribers
. Or whatever, it doesn't matter. - Put the following headers into the first row:
A | B | C | ... | |
---|---|---|---|---|
1 | timestamp |
To learn how to add additional input fields, checkout section 7 below.
- Click on
Tools > Script Editor…
which should open a new tab. - Rename it
Submit Form to Google Sheets
. Make sure to wait for it to actually save and update the title before editing the script. - Now, delete the
function myFunction() {}
block within theCode.gs
tab. - Paste the following script in it's place and
File > Save
:
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.
- Next, go to
Run > Run Function > initialSetup
to run this function. - In the
Authorization Required
dialog, click onReview Permissions
. - Sign in or pick the Google account associated with this projects.
- You should see a dialog that says
Hi {Your Name}
,Submit Form to Google Sheets wants to
... - Click
Allow
- Click on
Edit > Current project’s triggers
. - In the dialog click
No triggers set up. Click here to add one now.
- In the dropdowns select
doPost
- Set the events fields to
From spreadsheet
andOn form submit
- Then click
Save
- Click on
Publish > Deploy as web app…
. - Set
Project Version
toNew
and putinitial version
in the input field below. - Leave
Execute the app as:
set toMe(your@address.com)
. - For
Who has access to the app:
selectAnyone, even anonymous
. - Click
Deploy
. - In the popup, copy the
Current web app URL
from the dialog. - And click
OK
.
IMPORTANT! If you have a custom domain with Gmail, you might need to click
OK
, refresh the page, and then go toPublish > Deploy as web app…
again to get the proper web app URL. It should look something likehttps://script.google.com/a/yourdomain.com/macros/s/XXXX…
.
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>
, andbody
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.
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 | firstName | lastName |