This content originally appeared on DEV Community and was authored by Bootstrapper's Tales
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
timestampandemailas 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
initialSetupfunction 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_URLwith 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_LINKwith 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:
- Add headers to your Google Sheet
- Add
<input>to your html form with anameattribute 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
This content originally appeared on DEV Community and was authored by Bootstrapper's Tales
Bootstrapper's Tales | Sciencx (2021-05-02T17:34:50+00:00) Tutorial: Add Forms to Static Sites with Google Sheets. Retrieved from https://www.scien.cx/2021/05/02/tutorial-add-forms-to-static-sites-with-google-sheets/
Please log in to upload a file.
There are no updates yet.
Click the Upload button above to add an update.