Summarize pros and cons from reviews

Northwind’s product team reads reviews when a launch is fresh, then quietly stops a few weeks in. By the time the next planning cycle comes round, there are hundreds of comments per product and nobody has the appetite to read them end-to-end. The signal is in there — what people genuinely love, what they genuinely hate — but it never makes it onto a slide.

This script does the reading for you. It pulls every review for one product out of a sheet, hands the lot to Claude, and asks for two short lists back: a pros list and a cons list, in strict JSON. The output is the table you’d write by hand if you had a free afternoon — except this one took fifteen seconds and costs less than a sandwich.

What you’ll need

• A Google Sheet of reviews with three columns: date, product name, and the review text (the script reads columns A, B and C with a header row).
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• The product name you want summarised — passed in as the function argument.

The script

// The spreadsheet that holds your reviews.
const REVIEWS_SHEET_ID = '1abcReviewsId';

// Column indexes in the reviews sheet (zero-based after the header row).
const PRODUCT_COL = 1;
const REVIEW_COL = 2;

// How many reviews to include in one pass. Trims very long histories so the
// prompt stays inside a sensible token budget — see "Watch out for".
const MAX_REVIEWS = 200;

/**
 * Pulls every review for a given Northwind product and asks Claude for a
 * pros/cons summary as strict JSON.
 *
 * @param {string} product - The exact product name as it appears in column B.
 * @returns {{pros: string[], cons: string[]}} The parsed summary.
 */
function summariseProsCons(product) {
  if (!product) throw new Error('summariseProsCons needs a product name.');

  // 1. Read the sheet, drop the header, keep only rows for this product.
  const rows = SpreadsheetApp.openById(REVIEWS_SHEET_ID).getSheets()[0]
    .getDataRange()
    .getValues()
    .slice(1)
    .filter((r) => r[PRODUCT_COL] === product)
    .map((r) => r[REVIEW_COL])
    .filter(Boolean);

  if (!rows.length) {
    Logger.log('No reviews found for "' + product + '".');
    return { pros: [], cons: [] };
  }

  // 2. Format the sample as a bulleted list — the shape Claude reads best.
  const reviews = rows.slice(0, MAX_REVIEWS).join('\n- ');

  // 3. Pin the output to a fixed JSON schema. No prose, no markdown.
  const prompt =
    'Summarise these Northwind reviews for "' + product + '". ' +
    'Return ONLY a JSON object — no prose, no markdown — in this shape: ' +
    '{"pros": [string], "cons": [string]}. ' +
    'Give 3-5 entries per list, each a short noun phrase grounded in the ' +
    'reviews (not invented).\n\n- ' + reviews;

  // 4. Sonnet handles the synthesis; strip any code fence before parsing.
  const reply = callClaude(prompt, 'claude-sonnet-4-6', 600);
  return JSON.parse(stripFences(reply));
}

/**
 * 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. summariseProsCons takes a product name and opens the reviews spreadsheet, reading the full data range as a 2D array.
2. It drops the header row, filters down to rows whose product column matches, and pulls the review text out of column C. Blank rows are removed so they don’t pad the prompt with empty bullets.
3. If no reviews match, it logs a message and returns empty lists — no wasted API call.
4. The first MAX_REVIEWS (200) entries are joined into a bulleted list, the format Claude summarises most reliably.
5. The prompt pins the output to a strict JSON schema: an object with pros and cons arrays. Asking for short noun phrases keeps the result skimmable and stops Claude writing paragraphs.
6. stripFences removes any code fence Claude might wrap the JSON in, then JSON.parse turns the reply into a real object the caller can use.

Example run

Say the reviews sheet has rows like these for Northwind Tote:

Date	Product	Review
2025-10-12	Northwind Tote	Lovely fabric, holds my laptop fine, strap dug into my shoulder after an hour.
2025-10-14	Northwind Tote	Great colour and stitching — but the inner pocket is way too small.
2025-10-16	Northwind Tote	Sturdy and looks smart. Wish it had a zip on top.

Calling summariseProsCons('Northwind Tote') returns:

{
  "pros": ["Quality fabric and stitching", "Smart appearance", "Holds a laptop comfortably"],
  "cons": ["Strap is uncomfortable for long carries", "Inner pocket is too small", "No top zip"]
}

That is the slide you needed — three things to keep, three things to fix — distilled from the whole review backlog in one call.

Run it

This is an on-demand job, not a scheduled one. Run it when you need a summary:

1. In the Apps Script editor, open a function that calls summariseProsCons with the product name you want, then click Run.
2. Approve the authorisation prompt the first time.
3. Read the returned object in the logs, or wire it into a sheet, doc, or Slack message of your own.

For a per-product roll-up across the catalogue, loop over the unique product names in column B and write the result to a Summaries tab — one row per product, with the pros and cons joined into two cells.

Watch out for

• The summary is grounded in the reviews you send. If you only send 20 rows, expect 20 rows’ worth of insight — not a brand-wide truth.
• MAX_REVIEWS is capped at 200 to keep the prompt cheap. For products with more reviews, raise the cap and max_tokens together, or summarise in batches and ask Claude to merge the lists in a second pass.
• Strict JSON keeps this reliable. stripFences handles the common code-fence case; if JSON.parse still throws, log the raw reply and tighten the prompt rather than reaching for regex.
• This script summarises one product at a time. If you want themes across the whole catalogue instead, use Classify customer feedback by theme.