Sources Contact Advanced Search Tutorials

An Interest In:

Web News this Week

Search Archive

Some of Our Sources

View All Sources

Help Webnuz

Referal links:

May 2, 2021 05:34 pm GMT

Tutorial: Add Forms to Static Sites with Google Sheets

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 timestamp and email as 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 initialSetup function 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_URL with 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_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:

Add headers to your Google Sheet
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:

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

Original Link: https://dev.to/bs_tales/tutorial-add-forms-to-static-sites-with-google-sheets-1c3a

Share this article:

View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To