Build an AI survey-response analyzer

When a Northwind survey closes, the responses are not the hard part — making sense of them is. A few hundred open-text answers carry real signal, but nobody has a spare afternoon to read every one and spot the patterns by hand. So the analysis slips, and by the time it happens the feedback is stale.

This script hands the slow part to Claude. It reads the open-text column, asks for the responses grouped into a handful of named themes — each with an estimated count and two real quotes — and writes the result to a Themes tab you can skim in two minutes. It is the engine behind a quarterly feedback loop, but it works just as well for a one-off survey.

What you’ll need

• A Google Sheet of survey responses, with the open-text answers in column B and a header in row 1. Form-linked sheets put each question in its own column, so point the script at whichever column holds the open-text question.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• Nothing else — the script creates the Themes tab itself.

The script

// The spreadsheet that holds your survey responses.
const SURVEY_SHEET_ID = '1abcSurveyId';

// How many responses to send to Claude in one pass. Keeps the prompt
// within a sensible token budget — see "Watch out for".
const SAMPLE_SIZE = 200;

/**
 * Reads the open-text survey answers, asks Claude to cluster them into
 * themes, and writes the result to a "Themes" tab.
 */
function analyseResponses() {
  const ss = SpreadsheetApp.openById(SURVEY_SHEET_ID);

  // 1. Read column B (the open-text answers) and drop blank cells.
  const responses = ss.getSheets()[0]
    .getRange('B2:B')
    .getValues()
    .flat()
    .filter(Boolean);

  if (!responses.length) {
    Logger.log('No responses to analyse — nothing to do.');
    return;
  }

  // 2. Take a sample and format it as a bulleted list for the prompt.
  const sample = responses.slice(0, SAMPLE_SIZE).join('\n- ');

  // 3. Ask Claude for strict JSON. A fixed schema is the difference
  //    between a parseable result and a parsing nightmare.
  const prompt =
    'Cluster these Northwind survey responses into 5-8 themes. ' +
    'Return ONLY a JSON array — no prose, no markdown — in this shape: ' +
    '[{"theme": string, "count": number, "examples": [string, string]}]. ' +
    'Use "count" for your estimate of how many responses fit the theme, ' +
    'and "examples" for two short verbatim quotes.\n\n- ' + sample;

  // 4. Sonnet does the reasoning; strip any code fence, then parse.
  const reply = callClaude(prompt, 'claude-sonnet-4-6', 2000);
  const clusters = JSON.parse(stripFences(reply));

  // 5. Rebuild the Themes tab from scratch on every run.
  const sheet = ss.getSheetByName('Themes') || ss.insertSheet('Themes');
  sheet.clear();
  sheet.getRange(1, 1, 1, 3)
    .setValues([['Theme', 'Est. count', 'Example quotes']]);

  const rows = clusters.map((c) => [c.theme, c.count, c.examples.join('  /  ')]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 3).setValues(rows);
  }
  Logger.log('Wrote ' + rows.length + ' themes to the Themes tab.');
}

/**
 * Claude occasionally wraps JSON in a ```json code fence. Strip it so
 * JSON.parse never chokes on the markdown.
 */
function stripFences(text) {
  return text.replace(/```(?:json)?/g, '').trim();
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model = 'claude-haiku-4-5-20251001', 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. analyseResponses opens the survey spreadsheet and reads column B, the open-text answers, flattening the range into a plain list and dropping blanks.
2. If there are no responses, it logs a message and stops — no wasted API call.
3. It takes the first 200 responses (SAMPLE_SIZE) and joins them into a bulleted list, the format Claude clusters most reliably.
4. It builds a prompt that pins the output to a strict JSON schema — an array of objects, each with a theme, a count, and two examples.
5. It calls Claude Sonnet, which is worth the extra cost here because clustering needs reasoning over the whole sample. stripFences removes any code fence, then JSON.parse turns the reply into real objects.
6. It rebuilds the Themes tab from scratch and writes one row per theme, so the tab always reflects the latest run.

Example run

Say column B of the responses sheet contains a few hundred answers like these:

• “Onboarding took ages — I never knew where to start.”
• “Great support team, sorted my issue in minutes.”
• “A bit pricey for what you actually get.”
• “Setup was confusing, but support walked me through it.”

After a run, the Themes tab holds a clustered summary:

Theme	Est. count	Example quotes
Onboarding is confusing	58	”Onboarding took ages…” / “Setup was confusing…”
Support is responsive	47	”Great support team…” / “…support walked me through it.”
Pricing feels high	31	”A bit pricey for what you actually get.” / ”…”
Reporting needs work	22	”…” / ”…”

That is the table you take into a meeting — four to eight themes with real quotes underneath, instead of 200 raw rows nobody will read.

Run it

This is a once-per-survey job, not a daily one, so run it by hand when a survey closes:

1. In the Apps Script editor, select analyseResponses and click Run.
2. Approve the authorisation prompt the first time.
3. Open the Themes tab to read the result.

To let non-editors trigger it, add a custom menu so it appears in the spreadsheet itself:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Survey tools')
    .addItem('Analyse responses', 'analyseResponses')
    .addToUi();
}

Watch out for

• Counts are estimates. Claude clusters the sample and estimates how many responses fit each theme — it does not tally every row. That is fine for spotting priorities; it is not an exact frequency table.
• The sample is capped at SAMPLE_SIZE (200). For a larger survey, raise the cap and max_tokens together, or cluster in batches and merge the themes in a second pass.
• Strict JSON keeps this reliable. stripFences handles the common case where Claude wraps the array in a code fence. If JSON.parse still throws, log the raw reply and tighten the prompt rather than reaching for regex.
• This discovers themes from scratch. If you already know your categories and just want every row labelled, use Classify customer feedback by theme instead.