Simplify Data Collection with a Custom Google Sheets Form

Looking for an efficient way to collect and organize information? Today, I'm sharing an enhanced, professional-grade Google Sheets Data Entry Form built with Apps Script, now with advanced features like duplicate ID checking, conditional fields, and email confirmations!

What Is This Data Entry Form?

This custom form is a powerful data entry solution integrated directly with Google Sheets. Built using Google Apps Script, it offers a user-friendly interface for collecting data, storing it neatly in your spreadsheet, and now includes advanced features like preventing duplicate entries, dynamic field visibility, and automated email confirmations.

Why Use This Instead of Regular Google Forms?

Complete Customization

Full control over the form’s appearance, functionality, and behavior, tailored to your needs.

Seamless Integration

Access the form directly from your spreadsheet’s menu, keeping everything in one place.

Automatic Organization

Data is instantly added with serial numbers, timestamps, and validation for accuracy.

Advanced Features

Includes duplicate ID checking, conditional fields, and email confirmations for a robust experience.

Key Features

Duplicate ID Prevention: Checks if a Registration ID already exists, preventing duplicate entries and ensuring data integrity.
Conditional Fields: Address, City, and State fields appear only when "Other" is selected for Gender, streamlining the form for specific use cases.
Email Confirmations: Sends an automated confirmation email to the user upon successful submission, including their Registration ID and serial number.
User-Friendly Validation: Required fields and input types (e.g., email, date) ensure clean, consistent data.

How To Use The Form

Open your Google Sheet.
Click the Data Entry menu at the top.
Select Open Data Entry Form.
Enter a unique Registration ID (you’ll be alerted if it’s already used).
Fill in the required fields (marked with *).
Select "Other" in the Gender dropdown to reveal Address, City, and State fields if needed.
Click Submit to save your data and receive a confirmation email.
The information appears instantly in the "Data" sheet.
Click Clear to enter another record.

Where Does The Submitted Data Go?

Data is stored in a sheet called "Data" within your Google Spreadsheet.
If the "Data" sheet doesn’t exist, it’s created automatically on first submission.
Each submission adds a new row with columns for Serial Number, Registration ID, First Name, Last Name, Date of Birth, Gender, Address, City, State, Email ID, and Timestamp.
Duplicate Registration IDs are blocked to maintain unique records.
A timestamp is automatically added to track submission time.
Only authorized users (those you’ve shared the spreadsheet with) can view the data.
Data stays in your Google account, ready for sorting, filtering, or analysis.

This direct integration with your spreadsheet, combined with advanced validation and email notifications, makes this form far more powerful than standard Google Forms.

Perfect For Many Use Cases

This enhanced form is ideal for:

Customer or membership directories with unique IDs.
Event registrations with confirmation emails.
Inventory tracking with conditional data entry.
Project management with secure data collection.
Contact information collection with automated follow-ups.
Survey data with customized field visibility.

How To Implement It Yourself

Adding this enhanced form to your Google Sheet is easy:

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 the JavaScript code below (Block 1).
Click the "+" icon next to "Files" and select "HTML".
Name the file DataEntryForm and paste the HTML code below (Block 2).
Save the project (Ctrl+S or ⌘+S).
Authorize permissions when prompted (for spreadsheet access and Gmail for email confirmations).
Return to your Google Sheet and refresh the page.
You’ll see a new Data Entry menu item.

/**
 * 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');
}

/**
 * Checks if a Registration ID already exists in the Data sheet
 */
function checkDuplicateRegId(regId) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');
  var data = sheet.getDataRange().getValues();
  
  for (var i = 1; i < data.length; i++) {
    if (data[i][1] === regId) {
      return true;
    }
  }
  return false;
}

/**
 * Adds data from the form to the spreadsheet and sends a confirmation email
 */
function addRecord(formData) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Data') || ss.insertSheet('Data');
  
  if (checkDuplicateRegId(formData.regId)) {
    return 'Error: Registration ID "' + formData.regId + '" already exists!';
  }
  
  if (sheet.getLastRow() === 0) {
    sheet.appendRow([
      'Serial Number', 
      'Registration ID', 
      'First Name', 
      'Last Name', 
      'Date of Birth', 
      'Gender', 
      'Address', 
      'City', 
      'State', 
      'Email ID', 
      'Timestamp'
    ]);
  }
  
  var serialNumber = sheet.getLastRow();
  
  sheet.appendRow([
    serialNumber,
    formData.regId,
    formData.firstName,
    formData.lastName,
    formData.dob,
    formData.gender,
    formData.address,
    formData.city,
    formData.state,
    formData.email,
    new Date()
  ]);
  
  try {
    var subject = 'Data Entry Confirmation';
    var message = `
      Dear ${formData.firstName} ${formData.lastName},
      
      Your data has been successfully recorded!
      Registration ID: ${formData.regId}
      Serial Number: ${serialNumber}
      Date of Submission: ${new Date().toLocaleString()}
      
      Thank you!
    `;
    GmailApp.sendEmail(formData.email, subject, message);
    return 'Record added successfully and confirmation email sent!';
  } catch (e) {
    return 'Record added successfully, but failed to send email: ' + e.message;
  }
}

/**
 * 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;
      }
      .hidden {
        display: none;
      }
    </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 hidden" id="addressGroup">
          <label for="address">Address:</label>
          <input type="text" id="address" name="address">
        </div>
        <div class="form-group hidden" id="cityGroup">
          <label for="city">City:</label>
          <input type="text" id="city" name="city">
        </div>
        <div class="form-group hidden" id="stateGroup">
          <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();
      });
      
      // Check for duplicate Registration ID
      document.getElementById('regId').addEventListener('blur', function() {
        var regId = this.value;
        if (regId) {
          google.script.run
            .withSuccessHandler(function(isDuplicate) {
              var messageDiv = document.getElementById('message');
              if (isDuplicate) {
                messageDiv.innerHTML = 'Registration ID "' + regId + '" already exists!';
                messageDiv.style.color = 'red';
                document.getElementById('regId').focus();
              } else {
                messageDiv.innerHTML = '';
              }
            })
            .checkDuplicateRegId(regId);
        }
      });
      
      // Conditional logic for gender selection
      document.getElementById('gender').addEventListener('change', function() {
        var addressGroup = document.getElementById('addressGroup');
        var cityGroup = document.getElementById('cityGroup');
        var stateGroup = document.getElementById('stateGroup');
        
        if (this.value === 'Other') {
          addressGroup.classList.remove('hidden');
          cityGroup.classList.remove('hidden');
          stateGroup.classList.remove('hidden');
        } else {
          addressGroup.classList.add('hidden');
          cityGroup.classList.add('hidden');
          stateGroup.classList.add('hidden');
          document.getElementById('address').value = '';
          document.getElementById('city').value = '';
          document.getElementById('state').value = '';
        }
      });
      
      // 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 = '';
        // Reset conditional fields
        document.getElementById('addressGroup').classList.add('hidden');
        document.getElementById('cityGroup').classList.add('hidden');
        document.getElementById('stateGroup').classList.add('hidden');
      }
    </script>
  </body>
</html>

Ready to streamline your data collection?

Download the code from the blocks above and implement this powerful, enhanced form today!

Customization Options

Once implemented, you can further customize the form by:

Adding or removing form fields to match your data needs.
Changing colors, fonts, or layout to align with your branding.
Adding more validation rules (e.g., email format or age restrictions).
Modifying conditional logic (e.g., show fields based on other criteria).
Enhancing email confirmations with custom messages or HTML formatting.
Integrating with other Google services, like Google Calendar or Drive.

Troubleshooting Tips

Permissions: When you first run the script, Google will prompt you to authorize access to your spreadsheet and Gmail. Follow the prompts to grant permissions.
Duplicate ID Alerts: If you see a "Registration ID already exists" message, choose a unique ID.
Email Issues: Ensure the email address entered is valid. If emails fail, check your Gmail sent folder or script logs.
Conditional Fields: Address, City, and State fields only appear when "Other" is selected for Gender.

Have Questions?

Feel free to comment below if you need help setting up or customizing this form! I’m happy to assist with tailoring it to your specific needs.

Note: This solution requires a Google account and basic spreadsheet familiarity. No advanced coding knowledge is needed to use the form once set up!

Webtoolhelp

Wednesday, August 6, 2025

Data Entry firm preventing Duplicate entry.Sends email also