Classify customer feedback by theme

Northwind collects feedback everywhere — post-purchase emails, the support inbox, the website widget — and it all ends up in a single sheet that nobody sorts. Without tags you can’t count, and without counts you can’t tell whether pricing is louder than support this month, or whether quality complaints have quietly doubled.

This script tags every row for you. Given a fixed list of themes — pricing, quality, support, timing, other — it asks Claude to pick the best label for each comment and writes the result back into the sheet. It only touches rows that don’t already have a tag, so you can run it every morning without re-paying for work it already did.

What you’ll need

• A Google Sheet with at least two columns, comment and theme. The script reads the header to find them, so the order doesn’t matter.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The fixed list of themes you want to classify into. The default — pricing, quality, support, timing, other — covers most consumer feedback; change it if your business needs different buckets.

The script

// The spreadsheet that holds the feedback rows.
const FEEDBACK_SHEET_ID = '1abcFeedbackId';

// The closed list of themes. "other" is the safety valve so Claude never
// has to invent a label.
const THEMES = ['pricing', 'quality', 'support', 'timing', 'other'];

/**
 * Walk the feedback sheet, classify every untagged row, and write the
 * theme back to its `theme` column.
 */
function classifyFeedback() {
  const sheet = SpreadsheetApp.openById(FEEDBACK_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) return;

  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));
  if (col.comment === undefined || col.theme === undefined) {
    throw new Error('Sheet must have "comment" and "theme" header columns.');
  }

  let tagged = 0;
  rows.forEach((r, i) => {
    // Skip rows that are already classified or that have no comment to read.
    if (r[col.theme] || !r[col.comment]) return;

    const label = claudeClassify(r[col.comment]);
    values[i + 1][col.theme] = label; // +1 to skip the header row.
    tagged++;
  });

  // Single write back — much faster than one setValue() per row on big sheets.
  sheet.getDataRange().setValues(values);
  Logger.log('Tagged ' + tagged + ' new row(s).');
}

/**
 * Ask Claude to map one comment onto exactly one of the THEMES labels.
 * Falls back to "other" if the reply isn't one of the known labels.
 *
 * @param {string} comment - The feedback text to classify.
 * @returns {string} One of THEMES.
 */
function claudeClassify(comment) {
  const prompt =
    'Classify this Northwind feedback into one of: ' + THEMES.join(', ') + '.\n' +
    'Return ONLY the single label — no punctuation, no quotes, no extra ' +
    'text.\n\n' + comment;

  const raw = callClaude(prompt).toLowerCase().trim();

  // Defensive: never let an unexpected label leak into the sheet.
  return THEMES.includes(raw) ? raw : 'other';
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code. Haiku is plenty for short-label tasks.
 */
function callClaude(prompt) {
  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: 'claude-haiku-4-5-20251001',
      max_tokens: 50,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. classifyFeedback reads the whole sheet into memory in one call. That’s much faster than reading row-by-row, and it lets us write the result back in a single shot at the end.
2. It builds a column index from the header so the script doesn’t break if you reorder columns later. Missing comment or theme headers throw immediately — a clear error beats a silent miss.
3. For each row it skips two cases: rows already classified (so re-runs are cheap) and rows with no comment text (no point paying for a blank prompt).
4. claudeClassify builds a prompt listing the exact themes and asks for a single label back. Haiku is the right model here — classification into five buckets doesn’t need Sonnet’s reasoning depth.
5. The reply is lowercased and checked against the known themes. If Claude ever returns something unexpected (“billing”, say, or a sentence), the helper falls back to other so a stray label never lands in your sheet.
6. After the loop, the script writes the full grid back in one call. That matters once you’re past a few hundred rows.

Example run

Suppose the feedback sheet looks like this before a run:

comment	theme
Way too expensive for what you get.
The pack arrived torn at the seam.
Support replied within an hour, brilliant.
Shipping took three weeks.	quality
Loved the colour, fit was great.

After classifyFeedback runs, the theme column fills in for new rows and leaves the existing quality tag alone (even though it’s arguably wrong — the script doesn’t overwrite manual edits):

comment	theme
Way too expensive for what you get.	pricing
The pack arrived torn at the seam.	quality
Support replied within an hour, brilliant.	support
Shipping took three weeks.	quality
Loved the colour, fit was great.	other

Now a COUNTIF on column B gives you a real weekly breakdown.

Trigger it

For a steady stream of feedback, run this on a schedule:

1. In the Apps Script editor, open Triggers (clock icon on the left).
2. Add a time-driven trigger for classifyFeedback — once an hour or once a day, depending on volume.
3. The script will only touch new rows on each run, so the trigger is safe to leave on indefinitely.

For a one-off batch, run it by hand from the editor and watch the log.

Watch out for

• Themes are a closed set. Anything ambiguous goes into other. If other starts growing, that’s a signal you need a new bucket — not that Claude is wrong.
• One row, one API call. For sheets of tens of thousands of rows, switch to a batched prompt (send 20 comments, get 20 labels back as JSON) to cut cost and latency.
• Manual edits are preserved. The script skips rows where theme is already filled, so analysts can correct mislabels without the next run undoing their work.
• If you want themes you haven’t pre-defined — clustering rather than classification — use Build an AI survey-response analyzer instead.