Hello everyone! Today, we're going to explore how to send data from HTML form to google sheets..
In this image, we observe a form that I developed using HTML, CSS, and JavaScript. This form is versatile, suitable for integration into any static website. What's exciting is that I've implemented a mechanism to transfer user-submitted data directly to my personal Google Sheets file. This feature enhances the functionality of the form, allowing seamless data collection and management.
Creating a Google Sheets file requires a Gmail account, ensuring that the Chrome profile you're using is logged in with a single Gmail ID
It's crucial to ensure that the titles of the columns in your Google Sheets file correspond accurately to the fields in your HTML form. In my case, for instance, the columns are labeled as 'Name', 'Phone', and 'Email'. This alignment is essential for maintaining clarity and coherence between the form submissions and the data recorded in the Google Sheets document.
Afterwards, navigate to the 'Extensions' tab in the top section of your browser window, then select 'App Script' from the dropdown menu.
Next, clear any existing code and replace it with the following script:
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()
}
}
After clicking on the 'Save Icon' to preserve the changes made to the script, proceed to 'Run' the code. This action executes the script, activating the functionality to transfer submitted form data to the designated Google Sheets file. Ensure that the script runs smoothly without errors before proceeding further with form submissions
After saving the script, click on 'Review Permissions' This step is crucial as it ensures that the script has the necessary permissions to access and modify the Google Sheets file. Grant the required permissions for the script to function correctly.
After selecting Review Permissions, proceed to click on 'Advanced'. This will allow you to review and configure advanced settings related to the permissions granted to the script.
After clicking on Advanced, locate and select the option labeled 'Go to Data Submissions.'
Now click on 'Allow'. This action grants the necessary permissions for the script to access and manage your Google Sheets file as specified.
Now navigate to the top right corner and click on 'Deploy'. Then, select 'New Deployment' from the dropdown menu.
After selecting New Deployment, locate the 'Gear Icon' and click on it. From the options provided, select 'Web App'.
In the Configuration section, provide a name for your deployment, such as 'Data_Submission' Then, navigate to the 'Who has Access' section. Here, select the option for 'Anyone'. This configuration allows your web application to be accessed by anyone who has the link, facilitating the submission of form data to your Google Sheets document
Now click on Deploy Button
From this section, you'll find the 'Web App URL'. Copy this URL as it will serve as the endpoint for your HTML form submissions
To integrate the Web App URL into your HTML form, you need to find the JavaScript code responsible for handling the form submission. Once you locate this code, you'll replace the placeholder URL with the Web App URL you copied.
const scriptURL = 'https://script.google.com/macros/s/AKfycbxkgWotCMyvIKoT9aGtPCndZdHtHrq8i972t9CKAVjO6xC7T0I/exec'
Done
You've completed all the steps, your HTML form submissions will be directed to your Google Sheets file via the deployed script. Users filling out the form will have their submissions automatically recorded in the corresponding columns (such as Name, Phone, and Email) of your Google Sheets document.
