Skip to content
Home » Blog » Get Form Data In Google Sheets | Web Development Projects

Get Form Data In Google Sheets | Web Development Projects4 min read

Get Form Details Web Dev

Hey Web Devs, In this Post We Will Be Creating a Project Which Drives User’s Data to Google Sheets By Using a Google Sheets API. And We First Need a Basic Form Structure You can Use this Form Or You can Copy the Following Code.

Read Also:

QR Code Generator In Python

Front-End Development Complete Roadmap

To-Do List HTML,CSS & JavaScript

Form

HTML CODE:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Contact Form</title>
    <link rel="stylesheet" href="style.css">
</head>
<body>
    <div class="container">
    <form method="POST" name="google-sheet">
        <h3>GET IN TOUCH</h3>
    <input type="text" id="name" name="Name" placeholder="Enter your Name:"required>
    <input type="email" id="name" name="Email" placeholder="Enter your Email:"required>
    <input type="text" id="phone" name="Mobile" placeholder="Enter your Phone No:"required>
    <button type="submit">Send</button>
   </form>
</div>

     <script>
      const scriptURL = 'https://script.google.com/macros/s/AKfycbw8RLTt0prHFUua5r_RhLRKmDKBbjD9q25Xxl4O3Go-B3j9T1kgmAVPciiyr9Mg3ovXQw/exec'
      const form = document.forms['google-sheet']
    
      form.addEventListener('submit', e => {
        e.preventDefault()
        fetch(scriptURL, { method: 'POST', body: new FormData(form)})
          .then(response => alert("Thanks! for Using our Form"))
          .catch(error => console.error('Error!', error.message))
      })
    </script>

</body>
</html>

CSS CODE:

*{
  margin: 0;
  padding: 0;
  font-family: sans-serif;
  box-sizing: border-box;
}
.container{
width: 100%;
height: 100vh;
background-color: #001660;
display: flex;
align-items: center;
justify-content: center;
}
form{
  background: #fff;
  display: flex;
  flex-direction: column;
  padding: 2vw 4vw;
  width: 90%;
  max-width: 600px;
  border-radius: 10px;
}
form h3{
color: #555;
font-weight: 800;
margin-bottom: 20px;
}
form input,form textarea{
  border: 0;
  margin: 10px 0;
  padding: 20px;
  outline: none;
  background: #f5f5f5;
  font-size: 16px;
}
form button{
  padding: 15px;
  color: #ff5361;
  color: #fff;
  font-size: 18px;
  border: 0;
  outline: none;
  width: 150px;
}
form button{
  padding: 15px;
  background: #ff5361;
  color: #fff;
  font-size: 18px;
  border: 0;
  outline: none;
  cursor: pointer;
  width: 150px;
  margin: 20px auto 0;
  border-radius: 30px;
}

Now, After you Have Created a Basic Form Structure, Then We Need to Create a Blank Sheet In Google Sheets & Add Name, Email, & Mobile No Cell to the Sheet.

Add the Following JavaScript Code To Make the Form Working:

JavaScript Code:

<script>
      const scriptURL = 'Enter Google Form Script URL'
      const form = document.forms['google-sheet']
    
      form.addEventListener('submit', e => {
        e.preventDefault()
        fetch(scriptURL, { method: 'POST', body: new FormData(form)})
          .then(response => alert("Thanks! for Using our Form"))
          .catch(error => console.error('Error!', error.message))
      })
    </script>

After That Go to The Extensions>Script Editor & After You are in the Script Editor Delete All the Code & Paste the Following Code in It:

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

Copy the Following Code & Deploy>New Deployment And Click On Settings And Set it To Web App.

image 1

And Deploy the Project, After Deploying Add Some Data to the Form and You Will See that Data Has Been Added to the Sheet.

So This Was it For this Blog See you in the Next One till Then Keep Coding Keep Exploring!

DOWNLOAD SOURCE CODE

Tanmay Sinha

Leave a Reply