How to Create a Data Entry Form for Google Sheets (Without Google Forms)

Google Forms is the standard way to collect structured input into Google Sheets. But Forms has real limits: you can't conditionally show fields, you can't pre-fill values based on existing sheet data, and the form interface is fixed.

Apps Script lets you build a custom sidebar form that lives inside your spreadsheet. It's more work to set up, but you get full control — custom validation, dropdown values pulled from the sheet, conditional fields, whatever you need.

This tutorial builds a working sidebar form with a text field, a dropdown, and a date field that appends rows to a sheet.

---

How This Works

Apps Script has a showSidebar() function that opens an HTML panel inside Google Sheets. The HTML panel is a regular web form — inputs, a submit button — and when submitted, it calls an Apps Script function that appends the data to the sheet.

---

Step 1: Set Up the Sheet

Open a new Google Sheet. Name the first tab Log. Add headers in Row 1:

| A | B | C | D | |---|---|---|---| | Timestamp | Name | Category | Notes |

---

Step 2: Create the HTML Form

In the Apps Script editor (Extensions > Apps Script), click the + icon next to Files and add a new HTML file. Name it Form.

Paste this into Form.html:

Code

<!DOCTYPE html>
<html>
<head>
  <base target="_top">
  <style>
body { font-family: Arial, sans-serif; font-size: 13px; padding: 16px; color: #333; }
label { display: block; margin-top: 12px; font-weight: bold; font-size: 12px; }
input, select, textarea {
  width: 100%; padding: 6px 8px; margin-top: 4px;
  border: 1px solid #ccc; border-radius: 3px; font-size: 13px;
}
textarea { resize: vertical; height: 60px; }
button {
  margin-top: 16px; width: 100%; padding: 8px;
  background: #1D6B3F; color: white; border: none;
  border-radius: 3px; font-size: 13px; cursor: pointer;
}
button:hover { background: #155730; }
#status { margin-top: 12px; font-size: 12px; color: #1D6B3F; min-height: 16px; }
  </style>
</head>
<body>
  <label for="name">Name</label>
  <input type="text" id="name" placeholder="Enter name" />

  <label for="category">Category</label>
  <select id="category">
<option value="">-- Select --</option>
<option value="Software">Software</option>
<option value="Hardware">Hardware</option>
<option value="Service">Service</option>
<option value="Other">Other</option>
  </select>

  <label for="notes">Notes</label>
  <textarea id="notes" placeholder="Optional notes"></textarea>

  <button onclick="submitForm()">Add Row</button>
  <div id="status"></div>

  <script>
function submitForm() {
  var name     = document.getElementById('name').value.trim();
  var category = document.getElementById('category').value;
  var notes    = document.getElementById('notes').value.trim();

  if (!name) {
    document.getElementById('status').innerText = 'Name is required.';
    document.getElementById('status').style.color = '#c0392b';
    return;
  }

  document.getElementById('status').innerText = 'Saving...';
  document.getElementById('status').style.color = '#888';

  google.script.run
    .withSuccessHandler(function() {
      document.getElementById('name').value     = '';
      document.getElementById('category').value = '';
      document.getElementById('notes').value    = '';
      document.getElementById('status').innerText = '✓ Row added.';
      document.getElementById('status').style.color = '#1D6B3F';
    })
    .withFailureHandler(function(err) {
      document.getElementById('status').innerText = 'Error: ' + err.message;
      document.getElementById('status').style.color = '#c0392b';
    })
    .appendRow(name, category, notes);
}
  </script>
</body>
</html>

---

Step 3: Create the Apps Script Functions

In your Code.gs file, paste:

Code

// Show the sidebar
function showForm() {
  var html = HtmlService.createHtmlOutputFromFile('Form')
.setTitle('Add Entry')
.setWidth(300);
  SpreadsheetApp.getUi().showSidebar(html);
}

// Append a row to the Log sheet (called from the form's submit handler)
function appendRow(name, category, notes) {
  var sheet     = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log');
  var timestamp = new Date();
  sheet.appendRow([timestamp, name, category, notes]);
}

// Add a menu item to open the form
function onOpen() {
  SpreadsheetApp.getUi()
.createMenu('Entry Form')
.addItem('Open form', 'showForm')
.addToUi();
}

Save the script. Reload the spreadsheet — a new Entry Form menu appears in the top menu bar.

Click Entry Form > Open form to open the sidebar.

---

Step 4: Test It

Open the form (Entry Form > Open form)
Fill in a name and select a category
Click Add Row
Check the Log tab — a new row should appear with the timestamp, name, category, and any notes

---

Pulling Dropdown Values From the Sheet

Instead of hardcoding the category options in the HTML, you can pull them from a named range in the sheet. This way the form stays in sync with any list you maintain in the spreadsheet.

In the Apps Script file, add:

Code

function getCategoryOptions() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Log');
  var namedRanges = SpreadsheetApp.getActiveSpreadsheet().getNamedRanges();
  var range = namedRanges.find(r => r.getName() === 'Categories');
  if (!range) return [];
  return range.getRange().getValues().flat().filter(v => v !== '');
}

In the HTML form, replace the hardcoded