How to Submit HTML form DIRECTLY to Google Sheets

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://discourse.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

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.

4 Likes

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

<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

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

I think I have this almost working. How do you know what to name the input fields correctly?
Also, I get redirected to google page with a success message after submitting. Any advice would be greatly appreciated. Thanks

1 Like

Hi John,
Did you find a solution ?
I tried everything including the solutions mentioned here :

but no luck. What about you ?

@dont-pop

Anyway to get this to work with internet explorer?

Great work. I have a problem with multi selector form input. If a user selects many options, It submits only the first selected value to the sheet. Any ideas. Thanks in advance

.gs

function doGet() {
return HtmlService.createTemplateFromFile('Form.html')
}

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

function doPost (e) {
var lock = LockService.getScriptLock()
lock.tryLock(10000)

try {
var doc = SpreadsheetApp.getActive();
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()
}
}

.html

<!DOCTYPE html>
<html>
<body>

<style>
</style>

<form name="submit-to-google-sheet" id="form" action="script URL" method="POST">

<select name="Options" id="Options" aria-required="true" required="" size="4" multiple="multiple">
<option value="option1">option1</option>
<option value="option2">option2</option>
<option value="option3">option3</option>
<option value="option4">option4</option>
</select>

<input type="submit" value="Submit"></form>

</body>
</html>

Hi,
It works for me except I get this screen after submitting. Any idea how I could use the success message I set with webflow? I tried redirect URL i didn’t work.

@dont-pop Got it working! Thank you for posting this. Also wanted to integrate a “mailto:” script after the data is submitted (i.e a client clicked on the submit button and you would receive an email notification). I’m working on trying to integrate that right now, any ideas?

EDIT: I figured it out! Just add this to your Code.gs:

function sendNotification() {
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
    var range = sheet.getActiveRange().getA1Notation();
    var recipients = "YourEmail@YourAddress.com";
    var message = '';
        function createSpreadsheetChangeTrigger() {
        var ss = SpreadsheetApp.getActive();
        ScriptApp.newTrigger('onFormSubmit')
            .forSpreadsheet(ss)
            .onFromSubmit()
            .create();
        }  
    var subject = 'Put your subject line text here';
    var body = 'Put your email body text here ' + ss.getUrl();
    MailApp.sendEmail(recipients, subject, body);
};

Make sure to add your email address for “var recipient”, this will send an email notification to that address once the function is run. Customize the “var subject” and “var body”.

Once that’s done, add “sendNotification()” within “finally”, like so:

 finally {
     lock.releaseLock()
     sendNotification() //<--- **ADD THIS HERE**
 }

The final step afterwards is to publish your web app (so you can access it from your html). This step took me longer than I’d like to admit, it’s very simple, but super important. Go to Publish>Deploy as web app… and make sure to select “New” for the project version. Click “Update” and copy the web app URL and replace the one in your html. That’s it!

Hey, i ran these codes and i am having a small issue. The google spreadsheet data shows “undefined” instead of the timestamp and email. Can anyone help ?

PS: I am new to this.

Everything works until this point. Got my form to submit the info to my Google sheet, but as soon as I try to do this for one of the other forms on the page, the lower one will not send anything.

I added another HTML embed to the second form, and I expect that there’s something wrong there. Even tried setting up a completely separate spreadsheet and script, but it’ll still only execute the first one on the page.

Can anyone share an example of their two HTML embeds? Or even a (cloneable) Webflow project?

form.addEventListener(‘submit’, e => {
e.preventDefault()
window.location.href = “https://webflow.com”;
fetch(scriptURL, { method: ‘POST’, body: new FormData(form)})
.then(response => console.log(‘Success!’, response))
.catch(error => console.error(‘Error!’, error.message))

My abilities with Javascript are still minimal, but I’m starting to get better – at least I feel a bit more confident establishing and calling functions. It reminds me a bit of the syntax behind the functions that I used with R back in the day…

Anywho, I definitely found a way to reroute you back to your website! This original article is a bit hard to understand, and I think part of the reason is because Webflow has updated their ‘w-form’ since this article was originally written. If you’re having trouble implementing this, it could be because I believe that two options in “form settings”–both the ‘Redirect URL’ AND ‘Action’ fields–didn’t exist when this main article was written, voiding most of the functionality of this “HTML Embeded Code Editor” portion here… I might be wrong tho…

In the grand scheme of things, I think if you’re going to use Embeded Code at all, I would start fresh, and create ‘old-fashioned HTML forms’, opposed to a ‘w-form’, which is going to access the javascript found further down the page’s HTML. This is a very long road to go down, if you have tried to read its code… (If your curious, the rules dictacting your ‘w-forms’ are found within the 12th massive function inside that gargantuan JavaScript document that Webflow fires at the end of every page!!)

On the other hand, the GOOGLE SHEETS SCRIPT (aka the Code.gs script) works fantastic!
Abandon using any Embeded HTML Block on your site and just pump that “web app” URL directly into the “Action” field in Form Settings, and…BAM!! - you’ll have it working just the way you want it! Unfortunately, you’re gonna end up staring at this random page, simply saying it was a success after submitting the form. Big whoop. You want to do more now! Right?! Not just looking at some pointless words on some transient webpage hosted by god knows who!

SOLUTION! -

  1. To redirect back to your website, I first created a 3rd function on the google sheet’s script like this:
/*Add this 3rd function anywhere; the very bottom is probably the best:*/
function redirect() {
  return HtmlService.createHtmlOutput(
    "<script>window.top.location.href=\"" + REDIRECT_URL + "\";</script>"
  ); 
}
  1. Also create a third, new instance variable (at least that’s what we used to call them back in Java I class!!), before any functions are defined, at the top like this:

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
/*NEXT ADD this line, as whatever URL you wanna go to,
 after clicking that 'submit form' button:*/
  var REDIRECT_URL = "https://www.yourURL.com/whateverpage";
  1. .a. -
    Then change the end of the “try{}” block, inside the doPost(e) function,
    FROM:
/*DELETE this return statement (which simply creates that dumb page you end up on that says some words you don't care about)*/
return ContentService
  .createTextOutput(JSON.stringify({ 'result': 'success', 'row': nextRow }))
  .setMimeType(ContentService.MimeType.JSON)
  1. .b. -
    TO:
/*INSTEAD still use "return" but instead followed by calling your new function!!*/

return redirect()
}

/* Again, this is the END of the try block, 
right before start of the the catch block
in function doPOST()*/

Make sure you save the google script document and fire a new version of the web app creating a new URL.
And that should send you right back to your own webpage… or I guess where ever you really wanna go after pressing that ‘submit’ button! It’ll be whatever you set that top `REDIRECT_URL’
variable as.

On a final note, when you add a URL into the action setting field, the w-form won’t function with its classic Email function dictated in your profile settings. This should be fine; because at this point, you should be a more excited about the dynamic, unrestricted capabilities of applying your own JavaScript, opposed to receiving static emails and Webflow controlled spreadsheets!!

Rock on.

Thanks For Sharing but hey I’ve got a problem is google app script with google sheet does not work with mobile browser. Any idea??

The original solution is cool but there’s actually a much easier way to do this without any Google App Scripts or Javascript. You just need a free Sheet Monkey account here: https://sheetmonkey.io It only takes a few minutes.

Here’s a video on how to do it:

I am using webflow form to submit directly and I am having this issue. any ideas how to prevent the redirect using a webflow form?

Not working. Can you check my code?

var sheetName = 'Sheet1'
var scriptProp = PropertiesService.getScriptProperties()
var REDIRECT_URL = "https://webflow.com/design/smallbusinessavior"
function intialSetup () {
  var activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet()
  scriptProp.setProperty('key', activeSpreadsheet.getId())
}

/*Add this 3rd function anywhere; the very bottom is probably the best:*/
function redirect() {
  return HtmlService.createHtmlOutput(
"<script>window.top.location.href=\"" + REDIRECT_URL + "\";</script>"
  ); 
}

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 redirect()
  }

  catch (e) {
return redirect()
  }

  finally {
lock.releaseLock()
  }
}

Firstly, you have to create a google sheet, then convert it from excel file, if you have your data here ( I hope you know how to convert file, if not - here is the guide https://blog.coupler.io/convert-excel-to-google-sheets/), then in your sheet, go to tools->Script Editor and cut and paste the next snippet in there. And here is it!

function redirect() {
  return HtmlService.createHtmlOutput(
"<script>window.top.location.href=\"" + REDIRECT_URL + "\";</script>"
  ); 
}

is missing @ the end of the script