Step-by-step: Building a public form handler with Google Sheets

Alright folks, I've seen this pattern come up a dozen times in migration projects: a simple internal tool or a public-facing contact form needs a backend, but spinning up a database and an API layer is overkill. Often, the goal is just to capture structured data somewhere accessible, like a spreadsheet.

I recently guided a client through setting up a Google Sheets backend for a public form, moving away from a fragile, self-hosted PHP script. The core principle is using Google Apps Script as your "serverless" endpoint. Here's a step-by-step breakdown, including the major pitfalls I've encountered doing this at scale.

### Step 1: The Google Sheets Foundation
First, create your Sheet. The first row is your header/column names. This is your schema. Lock it down.
* **Pitfall:** Letting users or even collaborators edit the header row. Protect that range.

### Step 2: The Apps Script Backend
Open the Extensions menu -> Apps Script. This is where you'll write the `doPost` function that handles form submissions.

```javascript
function doPost(e) {
// e.parameter contains our form data
const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

// Basic validation/logging
console.log("Received submission: ", JSON.stringify(e.parameter));

// Map incoming data to your sheet columns. Be explicit.
const timestamp = new Date();
const name = e.parameter.name || '';
const email = e.parameter.email || '';
const message = e.parameter.message || '';

// Append as a new row
sheet.appendRow([timestamp, name, email, message]);

// Return a JSON response
const output = JSON.stringify({
result: "success",
message: "Submission recorded."
});

return ContentService
.createTextOutput(output)
.setMimeType(ContentService.MimeType.JSON);
}
```

### Step 3: Deploy as a Web App
This is the most critical step where things go wrong.
1. Click "Deploy" -> "New deployment".
2. Choose type "Web app".
3. Set **"Execute as"** to "Me". This controls whose permissions are used to write to the Sheet.
4. Set **"Who has access"** to "Anyone". This allows your public form to call it.
5. Deploy. **Copy the generated URL.** This is your endpoint.

**Major Pitfall:** Every time you update the script, you must create a **new deployment** and get a new URL. The old URL will run the old code. Versioning is manual.

### Step 4: The Frontend Form
Your HTML/JS form should POST JSON to that web app URL.

```html

Submit

document.getElementById('contactForm').addEventListener('submit', async (e) => {
e.preventDefault();
const formData = new FormData(e.target);
const data = Object.fromEntries(formData);

const response = await fetch('YOUR_WEB_APP_URL', {
method: 'POST',
headers: { 'Content-Type': 'application/json' },
body: JSON.stringify(data)
});
const result = await response.json();
alert(result.message);
});

```

### Critical Considerations & Scaling Limits
* **Quotas:** Apps Script has daily trigger quotas (e.g., 1,000 POST requests per day per user for free accounts). For a truly public form, this can be hit quickly.
* **Security:** This endpoint has no built-in authentication. You're open to spam. Implement a hidden honeypot field or, for more security, use a Google Cloud Project with an API Key (more complex).
* **Rate Limiting:** None. A burst of traffic can fail.
* **Data Validation:** The script shows minimal validation. You must add more (email format, length checks) or you'll pollute your sheet.

This is a classic "lift and shift" of a simple backend to a managed platform. It's perfect for low-volume, internal, or prototype forms. For anything expecting significant traffic, you're looking at a re-architecture to a proper serverless function (like AWS Lambda or Google Cloud Functions) with a real database, which I can outline in another thread.

Has anyone else tried this? Where did you run into walls?

-- migrator