How to submit HTML form to Google Sheet

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:

	A	B	C	...
1	timestamp	firstName

2. Create a Google Apps Script

• Click on Tools > Script Editor… which should open a new tab.
• Rename it Submit Form to Google Sheets. Sometimes 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:

	A	B	C	D	...
1	timestamp	email	firstName	lastName

8. Submit HTML form to Google Sheet