Simplify Data Collection with a Custom Google Sheets Form

Looking for an efficient way to collect and organize information? Today I'm sharing a powerful tool with all my readers - a custom Google Sheets Data Entry Form built with Apps Script!

What Is This Data Entry Form?

This is a professional-grade data entry solution that works directly with Google Sheets. Unlike typical form solutions, this custom form uses Google Apps Script to create a clean, user-friendly interface while storing everything neatly in your spreadsheet - giving you the best of both worlds!

Why Use This Instead of Regular Google Forms?

Complete Customization

Full control over the form's appearance and functionality - designed exactly how you want it.

Seamless Integration

Access the form directly from your spreadsheet - no switching between different Google products.

Automatic Organization

Data is instantly added to your sheet with automatic serial numbering and timestamps.

Enhanced Validation

Create custom validation rules specific to your data collection needs.

How To Use The Form

Open your Google Sheet
Click the Data Entry menu at the top
Select Open Data Entry Form
Fill in the required information (marked fields must be completed)
Click Submit to save your data
The information appears instantly in the "Data" sheet
Click Clear to enter another record

Where Does The Submitted Data Go?

The data is automatically stored in a sheet called "Data" within your Google Spreadsheet
If the "Data" sheet doesn't exist yet, it will be automatically created the first time data is submitted
Each submission creates a new row in the spreadsheet
The columns match all the form fields (Serial Number, Registration ID, First Name, Last Name, etc.)
A timestamp is automatically added to each entry
Only you (and anyone you've shared the spreadsheet with) can view this data
The data stays in your Google account, making it easy to manage, sort, filter, and analyze

This direct integration with your spreadsheet is one of the biggest advantages over regular Google Forms - your data is immediately available in a format ready for analysis without needing to switch between different tools.

Perfect For Many Use Cases

This form works wonderfully for:

Customer or membership directories
Event registrations
Inventory tracking
Project management
Contact information collection
Survey data

How To Implement It Yourself

Adding this to your own Google Sheet is straightforward:

Create a new Google Sheet or open an existing one
Go to Extensions → Apps Script
Delete any code in the default Code.gs file and paste in the JavaScript code provided below(Block1)

/**
 * Creates a custom menu when the spreadsheet opens
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Data Entry')
    .addItem('Open Data Entry Form', 'showForm')
    .addToUi();
}

/**
 * Shows the data entry form
 */
function showForm() {
  var html = HtmlService.createHtmlOutputFromFile('DataEntryForm')
    .setWidth(600)
    .setHeight(600)
    .setTitle('Data Entry Form');
  
  SpreadsheetApp.getUi().showModalDialog(html, 'Data Entry Form');
}

/**
 * Adds data from the form to the spreadsheet
 */
function addRecord(formData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');
  
  // Check if headers exist, if not add them
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      'Serial Number', 
      'Registration ID', 
      'First Name', 
      'Last Name', 
      'Date of Birth', 
      'Gender', 
      'Address', 
      'City', 
      'State', 
      'Email ID', 
      'Timestamp'
    ]);
  }
  
  // Get next serial number
  var serialNumber = sheet.getLastRow();
  
  // Add the data
  sheet.appendRow([
    serialNumber,
    formData.regId,
    formData.firstName,
    formData.lastName,
    formData.dob,
    formData.gender,
    formData.address,
    formData.city,
    formData.state,
    formData.email,
    new Date()
  ]);
  
  return 'Record added successfully!';
}

/**
 * Gets the next serial number
 */
function getNextSerialNumber() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');
  
  return sheet.getLastRow() === 0 ? 1 : sheet.getLastRow();
}

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <style>
      body {
        font-family: Arial, sans-serif;
        padding: 10px;
        background-color: #f0f0f0;
      }
      .form-container {
        max-width: 500px;
        margin: 0 auto;
        background-color: #ffe9d4;
        padding: 20px;
        border-radius: 8px;
        box-shadow: 0 2px 5px rgba(0,0,0,0.1);
      }
      h2 {
        text-align: center;
        margin-bottom: 20px;
        font-size: 24px;
      }
      .form-group {
        margin-bottom: 15px;
        display: flex;
      }
      .form-group label {
        width: 150px;
        font-weight: bold;
        line-height: 30px;
      }
      .form-group input, .form-group select {
        flex-grow: 1;
        padding: 5px;
        height: 30px;
        border: 1px solid #ccc;
        border-radius: 4px;
      }
      .btn-container {
        text-align: center;
        margin-top: 20px;
      }
      .btn {
        padding: 8px 20px;
        background-color: #4285f4;
        color: white;
        border: none;
        border-radius: 4px;
        cursor: pointer;
      }
      .btn:hover {
        background-color: #3367d6;
      }
      #message {
        text-align: center;
        margin-top: 15px;
        color: green;
        font-weight: bold;
      }
    </style>
  </head>
  <body>
    <div class="form-container">
      <h2>Data Entry Form</h2>
      <form id="dataForm">
        <div class="form-group">
          <label for="serialNumber">Serial Number:</label>
          <input type="text" id="serialNumber" disabled>
        </div>
        <div class="form-group">
          <label for="regId">Registration ID:</label>
          <input type="text" id="regId" name="regId" required>
        </div>
        <div class="form-group">
          <label for="firstName">First Name:</label>
          <input type="text" id="firstName" name="firstName" required>
        </div>
        <div class="form-group">
          <label for="lastName">Last Name:</label>
          <input type="text" id="lastName" name="lastName" required>
        </div>
        <div class="form-group">
          <label for="dob">Date of Birth:</label>
          <input type="date" id="dob" name="dob" required>
        </div>
        <div class="form-group">
          <label for="gender">Gender:</label>
          <select id="gender" name="gender" required>
            <option value="">Select</option>
            <option value="Male">Male</option>
            <option value="Female">Female</option>
            <option value="Other">Other</option>
          </select>
        </div>
        <div class="form-group">
          <label for="address">Address:</label>
          <input type="text" id="address" name="address">
        </div>
        <div class="form-group">
          <label for="city">City:</label>
          <input type="text" id="city" name="city">
        </div>
        <div class="form-group">
          <label for="state">State:</label>
          <input type="text" id="state" name="state">
        </div>
        <div class="form-group">
          <label for="email">Email ID:</label>
          <input type="email" id="email" name="email" required>
        </div>
        <div class="btn-container">
          <button type="button" class="btn" onclick="submitForm()">Submit</button>
          <button type="button" class="btn" onclick="clearForm()">Clear</button>
        </div>
      </form>
      <div id="message"></div>
    </div>
    
    <script>
      // When the page loads, get the next serial number
      document.addEventListener('DOMContentLoaded', function() {
        google.script.run.withSuccessHandler(function(serialNumber) {
          document.getElementById('serialNumber').value = serialNumber;
        }).getNextSerialNumber();
      });
      
      // Submit form data
      function submitForm() {
        const form = document.getElementById('dataForm');
        if (!form.checkValidity()) {
          form.reportValidity();
          return;
        }
        
        const formData = {
          regId: document.getElementById('regId').value,
          firstName: document.getElementById('firstName').value,
          lastName: document.getElementById('lastName').value,
          dob: document.getElementById('dob').value,
          gender: document.getElementById('gender').value,
          address: document.getElementById('address').value,
          city: document.getElementById('city').value,
          state: document.getElementById('state').value,
          email: document.getElementById('email').value
        };
        
        document.getElementById('message').innerHTML = 'Submitting...';
        
        google.script.run
          .withSuccessHandler(function(message) {
            document.getElementById('message').innerHTML = message;
            clearForm();
            google.script.run.withSuccessHandler(function(serialNumber) {
              document.getElementById('serialNumber').value = serialNumber;
            }).getNextSerialNumber();
          })
          .withFailureHandler(function(error) {
            document.getElementById('message').innerHTML = 'Error: ' + error.message;
            document.getElementById('message').style.color = 'red';
          })
          .addRecord(formData);
      }
      
      // Clear the form
      function clearForm() {
        const elements = document.getElementById('dataForm').elements;
        for(let i = 0; i < elements.length; i++) {
          if(elements[i].type !== 'button' && elements[i].id !== 'serialNumber') {
            elements[i].value = '';
          }
        }
        document.getElementById('message').innerHTML = '';
      }
    </script>
  </body>
</html>

Click the "+" icon next to "Files" and select "HTML"
Name the file "DataEntryForm" and paste in the HTML code provided(Block2)
Save the project (Ctrl+S or ⌘+S)
Return to your Google Sheet and refresh the page
You'll see a new "Data Entry" menu item appear

Ready to streamline your data collection?

Download the code from the attachments below and implement this powerful tool today!

Customization Options

Once you've implemented the basic form, you can customize it by:

Adding or removing form fields
Changing the form's colors and styling
Adding additional validation rules
Modifying the layout to match your branding
Adding conditional fields that appear based on other selections

Have Questions?

Feel free to comment below if you have any questions about implementing this on your own site! I'm happy to help you get this up and running for your specific needs.

Note: This solution requires a Google account and works best when you're comfortable with basic spreadsheet operations. No advanced coding knowledge is required to use the form once it's set up!

Webtoolhelp

Friday, April 18, 2025

🟢33DataEntryFormAdded33