Quantify tone and sentiment in reviews

“The reviews are mostly positive” is a feeling, not a number. To track whether Northwind’s customers are getting happier or grumpier over time, the feeling has to become data — a score you can average, chart, and compare quarter to quarter.

This script reads the Reviews tab and, for each review that has not been scored yet, asks Claude for two things at once: a numeric sentiment score from -1 to +1, and a single tone tag from a fixed list. The score gives you something to average; the tone gives you something to filter by. Both come back as one small JSON object, parsed and written into their own columns. Already-scored rows are skipped, so a run only pays for new reviews.

What you’ll need

• A Google Sheet with one review per row. Row 1 holds headers including review, plus (initially empty) sentiment and tone columns.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• Nothing else — the script writes straight back into the existing tab.

The script

// The spreadsheet that holds the Reviews tab.
const REVIEWS_SHEET_ID = '1abcReviewsId';

// The tone tags Claude is allowed to pick from — keeps the column filterable.
const TONES = ['enthusiastic', 'neutral', 'frustrated', 'angry', 'confused'];

/**
 * Reads the Reviews sheet and fills the "sentiment" and "tone" columns
 * for any row that has a review but no sentiment yet.
 */
function scoreReviews() {
  const sheet = SpreadsheetApp.openById(REVIEWS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet and split the header off the data rows.
  const [h, ...rows] = sheet.getDataRange().getValues();

  // 2. Map header names to column indexes so columns are addressed by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // 3. Bail out if there is nothing to score.
  if (!rows.length) {
    Logger.log('No reviews to score — nothing to do.');
    return;
  }

  // 4. Walk each row; skip rows already scored or with no review text.
  rows.forEach((r, i) => {
    if (r[col.sentiment] || !r[col.review]) return;

    // 5. Ask for both a numeric score and a tag in one strict-JSON reply.
    const prompt = `Score this review on sentiment (-1 to +1) and pick one ` +
      `tone from: ${TONES.join(', ')}.\n` +
      `Return JSON {"sentiment": number, "tone": string}\n\n${r[col.review]}`;

    const out = JSON.parse(callClaude(prompt));

    // 6. Write the score and the tag into their own columns.
    sheet.getRange(i + 2, col.sentiment + 1).setValue(out.sentiment);
    sheet.getRange(i + 2, col.tone + 1).setValue(out.tone);
  });

  Logger.log('Finished scoring reviews.');
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 *
 * @param {string} prompt The full prompt to send.
 * @return {string} Claude's reply, trimmed.
 */
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: 80,
      messages: [{ role: 'user', content: prompt }],
    }),
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. scoreReviews opens the Reviews spreadsheet and reads every row, splitting the header off the data with a destructuring assignment.
2. It builds a col map of header name to column index, so review, sentiment, and tone can be addressed by name.
3. If there are no data rows, it logs a message and stops — no wasted API calls.
4. It walks each row and skips two cases: the row is already scored, or there is no review text to score.
5. For each remaining row it builds a prompt that asks for both signals in one call — a sentiment number and a tone tag drawn from the fixed TONES list — and pins the reply to a strict JSON shape.
6. It parses the JSON and writes the sentiment number and the tone tag into their own columns, ready to average and filter.

Example run

Say the Reviews sheet has three new unscored rows:

review	sentiment	tone
Absolutely love it — saved us hours every week.
It works, but the setup docs were hard to follow.
Charged twice and no one has replied to my emails.

After scoreReviews, both columns are filled:

review	sentiment	tone
Absolutely love it — saved us hours every week.	0.9	enthusiastic
It works, but the setup docs were hard to follow.	0.1	confused
Charged twice and no one has replied to my emails.	-0.8	angry

Average the sentiment column for a single happiness number; filter by tone to pull every angry review for follow-up.

Trigger it

New reviews come in continuously, so run this on a schedule:

1. In the Apps Script editor open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose scoreReviews, a Time-driven source, and a Day timer — an overnight run keeps the scores current each morning.

To run it on demand instead, add a custom menu:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Review tools')
    .addItem('Score reviews', 'scoreReviews')
    .addToUi();
}

Watch out for

• A bad JSON reply throws. JSON.parse will fail if Claude wraps the object in a code fence or adds a stray word. If you hit this, strip code fences before parsing and tighten the prompt rather than reaching for regex.
• Sentiment is a judgement, not a measurement. The -1 to +1 score is consistent enough to trend over time, but treat individual values as approximate — what matters is the average moving up or down.
• Sarcasm is hard. “Great, another outage” reads positive on the surface. Spot-check a sample of low-confidence rows now and then.
• Keep TONES short and distinct. Overlapping tags (“annoyed” vs “frustrated”) make the column noisy. Five clear tags filter better than ten fuzzy ones.
• Re-running never re-scores. Rows with a sentiment value are skipped, so to re-score a review clear its sentiment cell first.
• Long sheets can time out. Apps Script caps a run at six minutes — process thousands of reviews in batches across scheduled runs.