Build an AI keyword-clustering tool

Northwind tracks a few hundred search terms in a spreadsheet, but a flat list is no use for planning content. What the marketing team actually needs to know is which terms belong together — which ones are really the same topic asked five different ways — so each cluster can become one page instead of five thin ones competing with each other.

This script hands the grouping to Claude. It reads the keyword column, asks for the terms sorted into named topic clusters, and writes each cluster to its own row in an output sheet. What comes back is a content plan: a handful of themes, each with the search terms it should target.

What you’ll need

• A Google Sheet of tracked keywords, one term per row in column A of the first tab, with a header in row 1.
• A second Google Sheet (or another tab) to receive the clusters. The script clears it on every run, so use a sheet you are happy to overwrite.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.

The script

// The spreadsheet that holds the flat list of tracked keywords.
const KEYWORDS_SHEET_ID = '1abcKeywordsId';

// The spreadsheet that receives the clustered output.
const CLUSTERS_SHEET_ID = '1abcKeywordClustersId';

/**
 * Reads the tracked keywords, asks Claude to group them into topic
 * clusters, and writes one row per cluster to the output sheet.
 */
function clusterKeywords() {
  // 1. Read column A of the keyword sheet and drop blank cells.
  const keywords = SpreadsheetApp.openById(KEYWORDS_SHEET_ID)
    .getSheets()[0]
    .getRange('A2:A')
    .getValues()
    .flat()
    .filter(Boolean);

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

  // 2. Ask Claude for strict JSON. A fixed schema keeps the reply
  //    safe to parse — see "Watch out for".
  const prompt =
    'Cluster these Northwind search keywords into topic groups. ' +
    'Return ONLY a JSON array — no prose, no markdown — in this shape: ' +
    '[{"cluster": string, "keywords": [string]}]. ' +
    'Give each cluster a short descriptive name and put every keyword ' +
    'into exactly one cluster.\n\n' + keywords.join('\n');

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

  // 4. Rebuild the output sheet from scratch on every run.
  const sheet = SpreadsheetApp.openById(CLUSTERS_SHEET_ID).getSheets()[0];
  sheet.clear();
  sheet.getRange(1, 1, 1, 2)
    .setValues([['Cluster', 'Keywords']]);

  // 5. Write one row per cluster, keywords joined into a single cell.
  const rows = clusters.map((c) => [c.cluster, c.keywords.join(', ')]);
  if (rows.length) {
    sheet.getRange(2, 1, rows.length, 2).setValues(rows);
  }
  Logger.log('Wrote ' + rows.length + ' clusters.');
}

/**
 * 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. clusterKeywords opens the keyword spreadsheet and reads column A, flattening the range into a plain list and dropping blank cells.
2. If there are no keywords, it logs a message and stops — no wasted API call.
3. It builds a prompt that pins the output to a strict JSON schema: an array of objects, each with a cluster name and a list of keywords. The prompt also tells Claude to put every term into exactly one cluster.
4. It calls Claude Sonnet, which is worth the cost here because good grouping needs reasoning over the whole list. stripFences removes any code fence, then JSON.parse turns the reply into objects.
5. It rebuilds the output sheet from scratch and writes one row per cluster, joining each cluster’s keywords into a single comma-separated cell.

Example run

Say column A of the keyword sheet holds terms like these:

• “google apps script tutorial”
• “apps script send email”
• “automate google sheets”
• “apps script gmail trigger”
• “google sheets formulas”

After a run, the output sheet holds a clustered plan:

Cluster	Keywords
Learning Apps Script	google apps script tutorial, apps script for beginners
Email automation	apps script send email, apps script gmail trigger
Sheets automation	automate google sheets, google sheets formulas

That is three content briefs instead of a list of forty loose terms — each cluster becomes one focused page rather than several thin ones.

Run it

This is a planning job you run when the keyword list changes, not on a schedule:

1. In the Apps Script editor, select clusterKeywords and click Run.
2. Approve the authorisation prompt the first time.
3. Open the output sheet to read the clusters.

Watch out for

• Clustering is a judgement call, not a fact. Claude groups by meaning, so borderline terms can land in either of two reasonable clusters. Treat the output as a starting point a human reviews.
• The output sheet is wiped on every run. If you have annotated it — added priorities or notes — those are lost. Keep notes in a separate column or tab.
• A very long keyword list can exceed the token budget. If JSON.parse throws, raise max_tokens, or split the list and cluster in batches.
• Strict JSON keeps this reliable. stripFences handles the common case where Claude wraps the array in a code fence. If parsing still fails, log the raw reply and tighten the prompt rather than reaching for regex.