Validate Form answers before they hit your master sheet

TL;DR — onFormSubmit writes to a staging tab first; validate required fields, regex, and allow-lists; then approve into master or flag the reject — so bad submissions never poison the sheet your jobs run on.

Linked Forms feel convenient until one typo in “Email” breaks every nightly job that trusts the master tab. The fix isn’t a sterner form description — it’s a staging sheet: onFormSubmit lands there, validators run, and only clean rows graduate into Master.

TL;DR

Form responses → Staging (not Master)
Validate required fields, formats (regex), and allow-lists
Approve → append to Master; Reject → flag + optional email
Jobs and reports read Master only
Soft assist: Co-Pilot can draft validators from plain-English rules

Using the Apps Script editor a lot? NitroGAS drops free themes & snippets right into script.google.com — optional Co-Pilot when you want a boost.

Why “Form → Master” bites

Google Forms already has required questions. That’s not enough when:

Downstream scripts assume a real email / SKU / site code
Operators paste free text into “Other”
One bad row makes UrlFetch or joins fail for everyone

Your master sheet is an API contract. Treat inbound Form rows like untrusted POST bodies.

What not to do

Don’t validate after Master is already polluted (cleanup theater)
Don’t delete rejects silently — keep them on Staging with a reason
Don’t put business rules only in the Form UI; scripts should enforce the same rules
Don’t run overnight jobs against the raw Form response sheet

Pattern: Staging → validate → Master

Form submit
  → onFormSubmit appends to Staging (status = PENDING)
  → validateRow_()
      OK  → copy to Master, status = APPROVED
      BAD → status = REJECTED, reason = …

Sheet layout

Staging headers (example):

Master — same business columns, no status/reason noise (or keep a thin audit column if you prefer).

Helpers

var STAGING = 'Staging';
var MASTER = 'Master';

function onFormSubmit(e) {
  var ss = SpreadsheetApp.getActive();
  var staging = ss.getSheetByName(STAGING);
  var named = (e && e.namedValues) || {};
  var row = {
    timestamp: new Date(),
    email: first_(named['Email']),
    siteCode: first_(named['Site code']),
    qty: first_(named['Quantity']),
    status: 'PENDING',
    reason: '',
    rawJson: JSON.stringify(named)
  };

  var result = validateRow_(row);
  row.status = result.ok ? 'APPROVED' : 'REJECTED';
  row.reason = result.ok ? '' : result.reason;

  staging.appendRow([
    row.timestamp, row.email, row.siteCode, row.qty,
    row.status, row.reason, row.rawJson
  ]);

  if (result.ok) {
    approveIntoMaster_(ss, row);
  } else {
    console.warn('rejected form row:', row.reason, row.email);
    // optional: emailErrorAlert('Form reject: ' + row.reason);
  }
}

function first_(arr) {
  return arr && arr.length ? String(arr[0]).trim() : '';
}

function validateRow_(row) {
  if (!row.email || !/^[^\s@]+@[^\s@]+\.[^\s@]+$/.test(row.email)) {
    return { ok: false, reason: 'invalid email' };
  }
  var allowedSites = { NYC: 1, LAX: 1, ORD: 1 };
  if (!allowedSites[row.siteCode]) {
    return { ok: false, reason: 'siteCode not in allow-list' };
  }
  var qty = Number(row.qty);
  if (!isFinite(qty) || qty < 1 || qty > 999) {
    return { ok: false, reason: 'qty out of range' };
  }
  return { ok: true, reason: '' };
}

function approveIntoMaster_(ss, row) {
  var master = ss.getSheetByName(MASTER);
  master.appendRow([row.timestamp, row.email, row.siteCode, Number(row.qty)]);
}

Install the trigger once (form submit → onFormSubmit). Keep validators pure: input object → { ok, reason } so you can unit-test them with a dummy row.

Manual re-approve

Sometimes Staging is right and the rule was wrong. Add a menu action that re-runs validateRow_ on the active Staging row and, if OK, appends to Master and flips status. Don’t hand-edit Master to “fix” a reject — you’ll lose the audit trail.

function revalidateActiveStagingRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if (sheet.getName() !== STAGING) {
    SpreadsheetApp.getUi().alert('Switch to Staging first');
    return;
  }
  var r = sheet.getActiveRange().getRow();
  if (r < 2) return;
  var values = sheet.getRange(r, 1, 1, 7).getValues()[0];
  var row = {
    timestamp: values[0],
    email: values[1],
    siteCode: values[2],
    qty: values[3]
  };
  var result = validateRow_(row);
  sheet.getRange(r, 5, 1, 2).setValues([[
    result.ok ? 'APPROVED' : 'REJECTED',
    result.ok ? '' : result.reason
  ]]);
  if (result.ok) approveIntoMaster_(SpreadsheetApp.getActive(), row);
}

Failure modes worth naming

Trigger not installed → rows only land in the Form’s default response sheet; Master stays empty
Header drift between Form titles and namedValues keys → empty fields, silent rejects
Allow-list in code only → update Script Properties / a Config tab when sites change
Jobs reading Staging → point every consumer at Master; Staging is quarantine

Soft Co-Pilot note

NitroGAS ships the free editor tools you already use; Co-Pilot can draft validateRow_ rules from a plain-English list (“email required, site in NYC/LAX/ORD, qty 1–999”) — you still own Staging vs Master and the trigger. Pay-when-you-need-it; core stays free.

Closing checklist

Staging tab with status + reason
onFormSubmit never writes Master first
Validators cover required / regex / allow-list
Nightly jobs read Master only
Reject path is visible (sheet or alert)

Happy Coding!