By Editorial Team
Introduction
In this tutorial, you’ll learn how to create a simple web form that collects user data and stores it directly in a Google Sheet using Google Apps Script. This is a powerful way to automate data collection and management, especially if you’re working with Google Workspace tools. Whether you’re gathering contact details, feedback, or any other information, this guide will show you step-by-step how to set up a form and link it to your spreadsheet seamlessly.
By the end of this tutorial, you’ll have a fully functional form that not only captures user input but also automatically organizes the data into a Google Sheet for easy access and analysis. Let’s dive in!
Apps Script Code (Code.gs)
This part of the script handles the backend processing, linking the HTML form with the Google Sheet.
function doGet() {
return HtmlService.createHtmlOutputFromFile('index');
}doGet() Function: This function is triggered when someone accesses the web app URL. It serves the index.html file as the user interface.
HtmlService.createHtmlOutputFromFile(‘index’);: This command loads the index.html file and returns it as the web page.
function processForm(form) {
var sheet = SpreadsheetApp.getActiveSheet();
var row = [form.fname, form.lname, form.email, form.mobile, form.address, form.description];
sheet.appendRow(row);
return true;
}processForm(form) Function: This function processes the form data when it’s submitted.
var sheet = SpreadsheetApp.getActiveSheet();: Retrieves the active sheet in the current Google Sheets document where the data will be saved.
var row = […]: Collects all the form data fields (first name, last name, email, mobile number, address, and description) into an array, which represents a single row in the spreadsheet.
sheet.appendRow(row);: Appends this array as a new row in the spreadsheet.
return true;: Confirms successful form submission.
HTML File (Index.html)
This file creates the user interface for the form.
<!DOCTYPE HTML>
<HTML>
<head>
<base target="_top">
</head>
<body>
<div style="text-align:left;width:16%;margin:50px auto 0px; display: block;line-height: 26px; padding: 59px 137px;background-color: #8080801c;">
<form id="myForm">
<h1>Enquiry forms</h1>
<label for="name">First Name:</label>
<input type="text" id="fname" name="fname"><br><br>
<label for="name">Last Name:</label>
<input type="text" id="lname" name="lname"><br><br>
<label for="email">Email:</label>
<input type="email" id="email" name="email"><br><br>
<label for="name">Mobile Number:</label>
<input type="text" id="mobile" name="mobile"><br><br>
<label for="name">Address:</label>
<input type="text" id="address" name="address"><br><br>
<label for="name">Description:</label>
<input type="text" id="description" name="description"><br><be>
<input type="button" value="Submit" onclick="submitForm()">
</form>
</div>
<script>
function submitForm() {
google.script.run.withSuccessHandler(alert('Data submitted successfully.')).processForm(document.getElementById('myForm'));
document.getElementById('myForm').reset();
}
</script>
</body>
</html>HTML Structure: The form has input fields for first name, last name, email, mobile number, address, and a description. Each field is labeled accordingly.
CSS Styling: Inline styles are used to center the form and provide padding and background color for a more appealing look.
<form id=”myForm”>: The form is wrapped in a div tag, and it is given an ID of myForm.
Input Fields: Each input field has an associated label and input element. The input elements collect the user’s data.
Submit Button: The Submit button is not a traditional submit button (<input type=”button”>), as it triggers a custom JavaScript function when clicked.
JavaScript Code:
- submitForm() Function: When the submit button is clicked, this function is called.
- google.script.run.withSuccessHandler(alert(‘Data submitted successfully.’)).processForm(document.getElementById(‘myForm’));: This line sends the form data to the processForm() function in the Apps Script and shows an alert when the submission is successful.
- document.getElementById(‘myForm’).reset();: This resets the form, clearing all the input fields after submission.
Final Notes:
This code demonstrates how to link an HTML form with a Google Sheets spreadsheet using Google Apps Script. It’s a straightforward way to collect and store user input data in a structured manner.
This tutorial is perfect for beginners looking to automate data collection tasks or create web-based forms that feed directly into Google Sheets.
0 Comments