Build a content-moderation filter

Northwind runs a public submission form — readers send in automation ideas, guest-post pitches and questions. Most of it is genuine, but a steady trickle is spam, abuse or simply off-topic, and right now a team member skims every row to weed those out before anyone acts on the queue. It is dull work, and the backlog grows whenever they are busy.

This script does the first pass for them. It walks the submissions sheet, sends each unmoderated entry to Claude, and writes back either ok or a short flag: <reason>. The team still has the final say — but they only need to look closely at the rows the filter flagged, not all of them.

What you’ll need

• A Google Sheet of submissions, with a header row and one row per submission.
• A text column holding the submission body, and a moderationStatus column for the verdict (leave it blank for new rows).
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The spreadsheet ID, copied from the sheet’s URL.

The script

// The spreadsheet that holds incoming submissions.
const SUBMISSIONS_SHEET_ID = '1abcSubmissionsId';

// Model used for the moderation check. Haiku is fast and cheap, which
// suits a per-row classification job.
const MODERATION_MODEL = 'claude-haiku-4-5-20251001';

/**
 * Walks the submissions sheet and writes a moderation verdict
 * ("ok" or "flag: <reason>") for every row that has not been checked yet.
 */
function moderateSubmissions() {
  const sheet = SpreadsheetApp.openById(SUBMISSIONS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split off the header row.
  const [header, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No submissions to moderate — nothing to do.');
    return;
  }

  // 2. Build a name -> column-index lookup so column order can change.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));

  // 3. Check each row that does not already have a verdict.
  let checked = 0;
  rows.forEach((row, i) => {
    if (row[col.moderationStatus]) return; // already moderated — skip

    // Ask Claude for a strict one-line answer.
    const prompt =
      'Is this Northwind submission safe and on-topic ' +
      '(Apps Script, automation)? Return only "ok" or "flag: <reason>".\n\n' +
      row[col.text];

    const verdict = callClaude(prompt);

    // Write the verdict back to the moderationStatus cell (+2 covers the
    // header row and 1-based row numbers; +1 makes the column 1-based).
    sheet.getRange(i + 2, col.moderationStatus + 1).setValue(verdict);
    checked++;
  });

  Logger.log('Moderated ' + checked + ' new submission(s).');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it is
 * never pasted into the code.
 */
function callClaude(prompt, model = MODERATION_MODEL, maxTokens = 400) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. moderateSubmissions opens the submissions spreadsheet and reads the whole data range in one call.
2. If there are no data rows it logs a message and stops — no wasted API calls.
3. It builds a col lookup from the header so the script does not depend on a fixed column order.
4. For each row it first checks moderationStatus. If a verdict is already present the row is skipped, which makes the script safe to re-run and means it never pays to re-check the same submission.
5. For each new row it builds a tight prompt that pins Claude to one of two answers — ok, or flag: followed by a short reason — and sends it.
6. It writes the verdict straight into the moderationStatus cell for that row.
7. callClaude is a small wrapper around the Anthropic Messages API; the key is read from Script Properties, never hard-coded.

Example run

Say the submissions sheet contains these new rows with a blank moderationStatus:

text	moderationStatus
How do I send a Slack message from Apps Script?
BUY CHEAP FOLLOWERS visit my-spam-site.example
Best recipe for sourdough bread?

After a run the column fills in:

text	moderationStatus
How do I send a Slack message from Apps Script?	ok
BUY CHEAP FOLLOWERS visit my-spam-site.example	flag: spam / promotional link
Best recipe for sourdough bread?	flag: off-topic, not about automation

The team now sorts by moderationStatus, ignores the ok rows, and reviews only the two flags.

Run it

Moderation needs to happen soon after a submission lands, so run this on a trigger rather than by hand:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger, choose moderateSubmissions, a Time-driven Hour timer, Every hour.
3. Save and approve the authorisation prompt.

To moderate a row the instant it arrives from a linked Form, attach moderateSubmissions to an On form submit trigger instead — it will simply process whichever rows are still blank.

Watch out for

• The filter is an assistant, not a gatekeeper. Treat flag as “a human should look”, not “auto-reject” — Claude can misjudge tone or context.
• Re-running is cheap and safe because rows with a verdict are skipped, but if you want to re-moderate a row, clear its moderationStatus cell first.
• A long submissions sheet means many API calls in one run. Apps Script caps execution at six minutes; if you have hundreds of new rows, process them in batches or raise the trigger frequency.
• The verdict is free text. If you later want to filter on it programmatically, rely on the ok / flag: prefix rather than parsing the reason.
• Each call costs a fraction of a penny but it adds up — only unmoderated rows are sent, so keep the sheet tidy and the bill stays small.