How to submit HTML form to Google Sheet - Nano Blogs Online

Nano Blogs Online

Nano Blogs Online provides free help in Web Technologies, Affiliate Marketing, Social Media Marketing, Digital Marketing, and reviews different products from the online world.

How to submit HTML form to Google Sheet

How to submit HTML form to Google Sheet

Share This


How to submit HTML form to Google Sheet

In six simple steps, you can submit HTML form to google sheets.

 1. Create a new Google Sheet

  • First, go to Google Sheets and withStart a new spreadsheet the templateBlank.
  • Rename it. With any name you want.
  • Put the following headers into the first row:
ABC...
1timestampfirstName


2. Create a Google Apps Script

  • Click on Tools > Script Editor… which should open a new tab.
  • Rename it Submit Form to Google SheetsSometimes the name takes time, wait for that until you are 100% satisfied
  • When you are done with Tools->Script Editor and renaming, then, delete the function myFunction() {} block within the Code.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()  
  }  

 }  


Now you created a Sheet -> This sheet is already saved with some one of your choice -> and from Tools>Scripts Editor the script is past. Done now you can move the next step.

3. Run the setup function

  • Next, go to Run > setup to run this function.
  • In the Authorization Required dialog, click on Review 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

4. Add a new project trigger

  • 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 and On form submit
  • Then click Save

5. Publish the project as a web app

  • Click on Publish > Deploy as web app….
  • Set Project Version to New and put initial version in the input field below.
  • Leave Execute the app as: set to Me(your@address.com).
  • For Who has access to the app: select Anyone, 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 to Publish > Deploy as web app… again to get the proper web app URL. It should look something like https://script.google.com/a/yourdomain.com/macros/s/XXXX….

6. Input your web app URL

Open the file named.index.html Online 12 replace <SCRIPT URL> with your script URL:
NOTE: In the above code you can see that <SCRIPT URL> you will replace it with your URL.


 <form name="contact">  
  <input name="email" type="email" placeholder="Email" required>  
  <button type="submit">Send</button>  
 </form>  
 <script>  
  const scriptURL = '<SCRIPT URL>'  
  const form = document.forms['contact']  
  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>  


7. Adding additional form data

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:
ABCD...
1timestampemailfirstNamelastName

8. Submit HTML form to Google Sheet

18 comments:

  1. I run this from localhost, and it the sheet i get undefined variables, did i do anything wrong?

    ReplyDelete
  2. okay i needed to replace name attribute with the exact name in the sheet.

    ReplyDelete
  3. See, the Google marketing listing process always occurs in function of a search request for specific words. Imagine that you are into furniture retail sales.https://edkentmedia.com/ppc-management-toronto/

    ReplyDelete
  4. thanks man, its working fine for me

    ReplyDelete
  5. Dig this. Using drive api, it can accept images too. Got it working with files.

    ReplyDelete
  6. how to submit multiple values like from checkbox

    ReplyDelete
  7. Is there a Youtube tutorial for this ? Thanks

    ReplyDelete
  8. Thank You..so much.. i am happy..for this code

    ReplyDelete
  9. this code again not working.....what am i do now?

    ReplyDelete
  10. This is amazing! Thanks so much

    ReplyDelete
  11. What about TWO forms on the same page? This script doesn't support it. What changes need to be made and how can I have two identical forms with different form names post to the same sheet? All the input fields are identical. One form is in the footer area and the other form is a modal accessible through button clicks throughout the page for convenience. and THANK YOU everything else works!!

    You forgot to mention you have to specify the form name in the html java script

    AND

    I had to add a doGet() function to avoid a bunch of errors.

    Thanks

    ReplyDelete
    Replies
    1. shawn@dontpop.com is my email thanks

      Delete
  12. While adding new project trigger, under "Choose which function to run", there are only 'doGet', 'handleResponse' and 'setup' options.
    There isn't a 'doPost' option at all.
    What is to be done? Proceed with 'doGet'?
    Please revert ASAP.

    ReplyDelete

  13. Uncaught TypeError: Cannot read property 'addEventListener' of undefined

    I think I've done everything fine. But when I press submit I get this error and it opens again the form on a new tab with the url from my website plus the text written on the inputs.

    It seems there si something wrong with submit. Am I right?

    Thank you anyway.

    ReplyDelete
    Replies
    1. Well thi issue is solved. Had another name on the tag form :)

      Anyway now it seems I've having problem with CORS:

      ailed to load https://script.google.com/macros/s/.../exec: No 'Access-Control-Allow-Origin' header is present on the requested resource. Origin 'https://MYDOMAIN.com' is therefore not allowed access. If an opaque response serves your needs, set the request's mode to 'no-cors' to fetch the resource with CORS disabled.

      and de Failed fetch:

      fetch.then.catch.error @
      Promise.catch (async)
      form.addEventListener.e @

      I've searched and found in another post about the same thing. send post data into google spreadsheet. And this is what I found about CORS:

      CORS Issues
      If you’re seeing an error like the following: “No ‘Access-Control-Allow-Origin’ header is present on the requested resource,” double-check you’re making a GET request and not a POST request.

      I've set like this code with POST and doPost.

      So I don't know what can be wrong....

      Delete