Detect and flag spam or duplicate submissions

A public Northwind contact form is a magnet for two kinds of noise: outright spam (casino links, SEO pitches, the usual) and the same earnest person filling it in three times because they didn’t see the confirmation page. Either way, the response sheet gets cluttered, the team wastes a triage cycle, and a real enquiry gets missed.

The fix is to do the triage at submission time. This script runs the moment a new row lands, scans the answers against a list of spam patterns, counts how many times the same email has appeared before, and tints anything suspicious in pale red. Nothing is deleted — junk just becomes obvious at a glance, and the inbox-style review takes seconds rather than minutes.

What you’ll need

• The form’s response sheet — the script runs on the active sheet, so install the trigger from the spreadsheet that the form writes to.
• An Email column in the responses (this is the default when a form collects email addresses). The duplicate check matches on that column.
• Edit the SPAM_PATTERNS list to suit your own junk — the defaults catch the most common drive-by submissions.

The script

// Words and phrases that almost always mean spam in a Northwind enquiry.
// Add your own — keep them as case-insensitive regexes.
const SPAM_PATTERNS = [/casino|crypto|forex/i, /seo services/i];

// Pale red. Used as a row background so flagged rows stand out
// without hiding the data.
const FLAG_COLOUR = '#fde2e1';

// Column header used for the duplicate check. Match exactly — including
// the capital E that Forms uses by default.
const EMAIL_HEADER = 'Email';

/**
 * Installable form-submit trigger. Flags the new row if it matches a
 * spam pattern or repeats an email we have already seen.
 *
 * @param {GoogleAppsScript.Events.SheetsOnFormSubmit} e The submit event.
 */
function onFormSubmit(e) {
  // Guard against being run manually from the editor — there is no
  // event object then, and nothing useful to do.
  if (!e || !e.range || !e.namedValues) {
    Logger.log('No event payload — run this from a form-submit trigger.');
    return;
  }

  const sheet = SpreadsheetApp.getActive().getActiveSheet();
  const row = e.range.getRow();

  // 1. Flatten every answer into one string so the regexes can scan
  //    the whole submission in one pass.
  const values = Object.values(e.namedValues).flat().join(' ');
  const isSpam = SPAM_PATTERNS.some((re) => re.test(values));

  // 2. Pull the submitter's email (lower-cased) for the duplicate check.
  const email = e.namedValues[EMAIL_HEADER]?.[0]?.toLowerCase();

  // 3. Count how many times that email already appears in the sheet.
  //    > 1 means this is at least the second submission from them.
  const all = sheet.getDataRange().getValues();
  const headerCol = all[0].indexOf(EMAIL_HEADER);
  const dupes = headerCol >= 0 && email
    ? all.slice(1).filter((r) => r[headerCol]?.toString().toLowerCase() === email).length
    : 0;

  // 4. Tint the whole row if either check trips. Nothing is deleted —
  //    the team still sees the row, just marked.
  if (isSpam || dupes > 1) {
    sheet.getRange(row, 1, 1, sheet.getLastColumn()).setBackground(FLAG_COLOUR);
    Logger.log('Flagged row ' + row + ' — spam:' + isSpam + ' dupes:' + dupes);
  }
}

How it works

1. The trigger fires on every form submission and receives the event object, which carries the answers in e.namedValues and the new row in e.range.
2. It guards against being run by hand from the editor — without the event payload there is nothing to check.
3. It joins every answer into one long string and tests it against the regexes in SPAM_PATTERNS. A single match flips isSpam to true.
4. It reads the submitter’s email from the Email column (lower-cased so Alex@… and alex@… count as the same person).
5. It scans every existing row for the same email. If the count is more than one, this is a repeat submission.
6. If either check trips, the entire row is tinted with FLAG_COLOUR so it reads as “needs a closer look” at a glance.

Example run

Say the sheet already contains one row from [email protected]. Two new submissions arrive:

Timestamp	Email	Message
09:14	[email protected]	Best casino bonuses — visit our site!
09:16	[email protected]	Forgot to mention I need it by Friday.

After the trigger runs:

• The 09:14 row is tinted pale red — casino matched SPAM_PATTERNS.
• The 09:16 row is tinted pale red — Alex’s email now appears twice.
• A genuine first-time submission would land with no tint at all.

The team can sort or filter by background colour and clear the noise in one sweep, or quickly merge a duplicate with the original.

Trigger it

This needs the installable form-submit trigger, not the simple one — only installable triggers can change cell formatting:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add trigger, choose onFormSubmit, event source From spreadsheet, event type On form submit.
3. Approve the authorisation prompt. Submit a test response to confirm the row is tinted as expected.

Watch out for

• SPAM_PATTERNS is a denylist, so it will only catch what you teach it. Review flagged-vs-missed rows weekly for the first month and add new patterns as the spam evolves.
• The duplicate check is exact-match on the email column. A submitter using [email protected] and [email protected] will not be caught — strip the +tag portion before comparing if that matters to you.
• Tinting is reversible, deletion is not. Keep this script as a flagger; if you want to auto-delete, do it from a separate manual review step once you trust the rules.
• The Email header must match exactly. If you renamed the column, update EMAIL_HEADER to match — otherwise the duplicate check silently returns 0.
• For richer triage (sentiment, intent, category) consider classifying with an AI step instead — pattern lists scale poorly past a few dozen rules.