Build an AI sentiment trend dashboard

Northwind reads its reviews, but reading them tells you how customers feel today — not whether that is better or worse than last month. A single bad week looks like a crisis; a slow three-month slide looks like nothing at all. The signal is in the trend, and a trend needs a daily data point.

This script lays that data point down every day. It reads the last 24 hours of reviews, asks Claude for a single average sentiment score, and appends one row — date, score, review count — to a trend sheet. Chart that sheet and you have a line that shows which way customer feeling is actually moving.

What you’ll need

• A Google Sheet of incoming reviews, with a date in column A and the review text in column B.
• A second Google Sheet — or another tab — to hold the daily trend series.
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• Nothing else — the script appends to the trend sheet; chart it once by hand.

The script

// The spreadsheet that collects incoming reviews.
const REVIEWS_SHEET_ID = '1abcReviewsId';

// The spreadsheet that holds the daily sentiment series.
const TREND_SHEET_ID = '1abcSentimentTrendId';

// One day in milliseconds — the look-back window for "recent" reviews.
const ONE_DAY_MS = 86400000;

/**
 * Reads the last 24 hours of reviews, asks Claude for an average
 * sentiment score, and appends one row to the trend sheet.
 */
function logDailySentiment() {
  // 1. Work out today's date label and the 24-hour cut-off.
  const today = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');
  const cutoff = new Date(Date.now() - ONE_DAY_MS);

  // 2. Read the reviews sheet, drop the header, and keep recent rows.
  const reviews = SpreadsheetApp.openById(REVIEWS_SHEET_ID).getSheets()[0]
    .getDataRange().getValues().slice(1)
    .filter((r) => r[0] instanceof Date && r[0] >= cutoff)
    .map((r) => r[1])
    .filter(Boolean);

  if (!reviews.length) {
    Logger.log('No reviews in the last 24 hours — nothing to log.');
    return;
  }

  // 3. Ask Claude for a single average sentiment number (-1 to +1).
  const prompt =
    'Return the average sentiment of these Northwind reviews as a single ' +
    'number between -1 (very negative) and +1 (very positive). ' +
    'Reply with only the number.\n\n' +
    reviews.join('\n---\n');
  const avg = parseFloat(callClaude(prompt));

  // 4. Bail out if the reply was not a usable number.
  if (Number.isNaN(avg)) {
    Logger.log('Claude did not return a number — skipping today.');
    return;
  }

  // 5. Append one row: date, score, and how many reviews it covers.
  SpreadsheetApp.openById(TREND_SHEET_ID).getSheets()[0]
    .appendRow([today, avg, reviews.length]);
  Logger.log('Logged sentiment ' + avg + ' across ' + reviews.length + ' reviews.');
}

/**
 * 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 = 20) {
  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. logDailySentiment builds today’s yyyy-MM-dd label and a cut-off 24 hours in the past.
2. It reads the reviews sheet, drops the header row, keeps only rows whose date is on or after the cut-off, and pulls out the review text — dropping blanks.
3. If no reviews landed in the window, it logs a message and stops — no wasted API call and no empty row.
4. It builds a prompt asking for one average sentiment number between -1 and +1, and calls Claude Haiku, which is more than enough for a scoring task this narrow. max_tokens is tiny because the reply is a single number.
5. parseFloat turns the reply into a number; if Claude returned anything that is not a number, the guard logs it and skips the day rather than writing junk.
6. It appends one row to the trend sheet — date, score, review count — building the daily series one day at a time.

Example run

After the script has run for several days, the trend sheet holds a clean series:

Date	Avg sentiment	Reviews
2025-11-28	0.42	17
2025-11-29	0.38	23
2025-11-30	0.11	19
2025-12-01	-0.05	26

Select the Date and Avg sentiment columns and insert a line chart. The dip from 0.42 to -0.05 over four days is the thing no single review would have shown you — that is the whole point of the dashboard.

Trigger it

This only works if it runs every day, so set a time-driven trigger:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for logDailySentiment, Time-driven, Day timer, set for the early hours so it captures the full previous day.
3. Approve the authorisation prompt the first time it runs.

Run it once by hand first to confirm a row appears in the trend sheet, then let the daily trigger build the series.

Watch out for

• The score is an average, so it hides the spread. A day of 0.0 could be calm neutral feedback or a violent mix of love and fury — log the review count alongside it, and consider tracking the range too if that distinction matters.
• The window is a fixed 24 hours from the run time. If the trigger fails one day and runs late the next, you can miss or double-count reviews near the edge — filtering by a stored “last run” timestamp is more robust than a rolling window.
• The script only reads rows where column A is a real Date object. A review whose date arrived as text will be silently skipped — format the date column as a date in the reviews sheet.
• A non-numeric reply is caught by the Number.isNaN guard and the day is skipped, which leaves a gap in the series. If gaps are a problem, retry the call or carry the previous day’s value forward.
• All recent reviews go into one prompt. A day with hundreds of long reviews can exceed the token budget — sample the reviews, or score them in batches and average the batch scores, if volume grows.