Streaming live at 10am (PST)

How to Submit HTML form DIRECTLY to Google Sheets


#1

Send form data and submissions to a New Row in your Google Sheets Spreadsheet Document using Apps Script. Private. Does NOT use “Google Forms”. No exporting code. It’s free and securely encrypted too.

This is an addendum to my previous post. Search “hidden fields in a form using javascript”

https://forum.webflow.com/t/how-to-use-hidden-fields-to-include-your-current-url-referring-page-url-and-form-names-using-one-line-of-javascript/71176

Post your questions and comments below to help provide a community knowledge base for everyone. It’s helpful to know when you’re reading a long post like this if it worked for any of your peers. This worked for me.

Cut and paste the snippet into an HTML Embed and now you have a form that adds a new row to a google spreadsheet automatically and includes additional information about the page, user, and form using the html input type=“hidden” element and some beginner’s javascript.

It’s done in 5 steps (step 5 includes beverages)

  1. Create a form in Webflow
  2. Create Spreadsheet in Google Sheets
  3. In your sheet, go to tools->Script Editor and cut and paste the next snippet in there.
  1. Cut and paste the HTML snippet below into an HTML EMBED and place it inside your form Block before the submit button.
  2. Enjoy Freedom and the ability to start automating emails, templates, file creation, calendar appointments, automatic google maps directions and anything else you can find the time to learn to code once it’s up and running.

Here’s a demo of it working
https://form-to-google-sheets.surge.sh

An this is from my webflow site.

So.

Complete step one.

Step Two:
Refer to step one in the following link


There is every resource here to make this super simple and easy (as easy as this king of thing gets)

He’ll cover step 3 as well.
I can’t explain the steps better than this document here. THIS IS EASIST GUIDE TO START INTEGRATING I’ve found. Take some time and be willing to get errors. That’s a good thing. Error messages point you in the right direction.

It’s not “the easiest thing I’ve ever done”. Who cares. I’ll help if I can. Check out the links above. If you do ask for help, I need to know what you’ve already tried and see a screenshot first. HAHA after that then I’ll match your efforts :slight_smile: I’m still learning this side of development so I feel like a hero if I can solve a problem for someone else.

Here is the example Mr Wilson provides regarding Multiple forms to one sheet, and the format I followed, and broke into two separate HTML EMBEDs for use with webflow…

Once you have the Google Apps Script URL authorized with your google Sheet, you can now complete steps 4 and 5.

Step 4
Paste the GoogleScriptURL where indicated below, then copy/past the whole thing in to an HTML Embed into your form element.

I’ve condensed the code to be more “portable” and now (if you’re not going to use the google script just skip the script between “Google Script Starts Here” and “End the Call to Google Scripts.”

Coders, please forgive me, I’m probably mis-naming things in my descriptions by calling a “method” a “function” or using “call” instead of “post”… but the code below works LOL. Once you have the Google Apps Script URL authorized with your google Sheet, you can now complete steps 4 and 5.

important
don’t forget to include column headers of formSrc1 and formIda and formUrl1 if you want them to show up in your spreadsheet. they are the attribute names of the hidden fields below.

<input type="hidden" id="hiddenKey11" name="formSrc1" data-name="formSrc1" value="footerArea">
<input type="hidden" id="hiddenKey12" name="formIdA" data-name="formIdA" value="">
<input type="hidden" id="hiddenKey13" name="formUrl1" data-name="formUrl1" value="">
<input type="hidden" id="hiddenKey14" name="formRef1" data-name="formRef1" value="">

<script>

/*<--------This first constant is used by the hidden field function and the form submit*/
const formID02 = 'wf-form-ContactFormFooter'

/*<---------Google Script part starts here*/
const deskForm2 = document.forms[formID02]


/*<---------Cut and paste the URL that pops up in the window when you "deploy web app"*/
var gScriptURL = 'https://script.google.com/macros/abcdefghij0123456789/exec'

/*<--------(when the) "form ID" . is submitted, ping the URL, encrypt, and send it*/
deskForm2.addEventListener('submit', e => {
    e.preventDefault()
    fetch(gScriptURL, { method: 'POST', body: new FormData(deskForm2)})
      .then(response => console.log('Success!', response))
      .catch(error => console.error('Error!', error.message))
  })
/*<---------End the call to Google Script*/

/*<---------"look for html elements given this ID and assign it's value to this variable*/
{
document.getElementById('hiddenKey12').value = formID02;
document.getElementById('hiddenKey13').value = location.pathname;
document.getElementById('hiddenKey14').value = document.referrer;

/*<--------note: if you link directly to the page url with your Form from the browser, there will be no data for the document.referrer or the hidden input you named hiddenKey14.. in this case... formRef1.  Otherwise you'll see the full URL of the last page they were on.-------*/
}
</script

I got rid of class references to the hidden fields at the time being because if they ever come up I’ll cross that bridge when I come to it. If you don’t know what I’m referring to, It doesn’t have any affect of this project, just an improvement by omission from my last version.

Here is exactly my google apps script:

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

     function setup () {

var doc = SpreadsheetApp.getActiveSpreadsheet()
scriptProp.setProperty('key', doc.getId())

}

 function doPost (e) {

var lock = LockService.getScriptLock()
  lock.waitLock(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()
}
}

I hope all this helps…

Post your questions and comments below to help provide a community knowledge base for everyone. It’s helpful to know when you’re reading a long post like this if it worked for any of your peers. This worked for me.


#2

FYI above code does not work in IE. You should use jQuery since Webflow already includes this by default.


#3
<script defer src="https://cdn.polyfill.io/v2/polyfill.min.js"></script>

Thanks for the heads up !

Well the code work with the above Polyfill???

This was mentioned in the github process. The above is the coda included in my head tag site wide with the defer attribute to help load times although the script is so small I don’t think it’s needed.

The nice thing that form submissions still get recorded in web flow with her with out this extra whole process including the hidden and fields. I’ll see if I can come back with a J query conversion


#4
<script defer src="https://cdn.polyfill.io/v2/polyfill.min.js"></script>

Also, why load an external resource when you can inline the code directly?

<script>(function(undefined) {}).call('object' === typeof window && window || 'object' === typeof self && self || 'object' === typeof global && global || {});</script>