Build a sentiment-scoring function without AI

Northwind surveys its clients every quarter, and the free-text answers are where the honest feedback lives. Reading every one for tone is slow, and calling an AI model for a quick gut-check feels like overkill — it means an API key, a quota, and a network round trip for each cell.

For a fast first pass, a lexicon does the job. This custom function scores a piece of text by counting positive and negative words from a small built-in list. It runs entirely inside the sheet — no key, no quota, no internet — so you can fill a whole column of survey answers with =SENTIMENT(...) and sort the spreadsheet by tone in seconds.

What you’ll need

• A Google Sheet with free-text answers to score — Northwind keeps survey responses with the open-text column in column A.
• Nothing else. A custom function lives in the bound script and needs no API key, no enabled services, and no setup beyond pasting the code.

The script

// Words that count as positive sentiment.
const POS = [
  'great', 'love', 'excellent', 'happy', 'amazing', 'perfect',
  'helpful', 'smooth', 'quick', 'fast', 'clear', 'recommend',
];

// Words that count as negative sentiment.
const NEG = [
  'bad', 'hate', 'slow', 'broken', 'confusing', 'disappointing',
  'poor', 'awful', 'rude', 'unclear', 'frustrating', 'late',
];

/**
 * Scores a piece of text by sentiment. Each positive word adds 1,
 * each negative word subtracts 1. The result is a single number:
 * above zero is positive, below zero is negative, zero is neutral.
 *
 * @param {string} text The text to score.
 * @return {number} The net sentiment score.
 * @customfunction
 */
function SENTIMENT(text) {
  // An empty cell is neutral — score it zero.
  if (!text) return 0;

  // Lower-case and split on any non-word character to get plain words.
  const words = String(text).toLowerCase().split(/\W+/);

  // Walk every word, adding for positives and subtracting for negatives.
  let score = 0;
  for (const w of words) {
    if (POS.includes(w)) score += 1;
    if (NEG.includes(w)) score -= 1;
  }
  return score;
}

How it works

1. SENTIMENT takes one cell of text. The @customfunction tag in the JSDoc block is what makes it callable from a spreadsheet formula.
2. If the cell is empty, it returns 0 straight away — a blank answer is neutral.
3. It lower-cases the text and splits it on \W+, any run of non-word characters, which breaks the sentence into a clean array of words and strips punctuation.
4. It walks every word. Each word found in the POS list adds 1 to the score; each word in the NEG list subtracts 1.
5. It returns the net total. A positive number leans positive, a negative number leans negative, and zero is neutral or mixed.

Use it

Paste the formula next to a column of answers and fill it down:

=SENTIMENT(A2)

Example run

Drop the function beside a column of survey answers and the tone falls out as a number you can sort on:

Answer (A)	=SENTIMENT(A)
“The team was helpful and the rollout was smooth.”	2
”Setup was confusing and support felt slow.”	-2
”It works.”	0
”Great product, but the onboarding was frustrating.”	0

Note the last row: one positive word and one negative word cancel out to zero, which is exactly what a mixed answer should score.

Watch out for

• It counts words, it does not read meaning. “Not great” scores +1 because the lexicon never sees the “not”. Negation, sarcasm, and context are all invisible to it.
• The lexicon is tiny and English-only. Answers that avoid these exact words score 0 even when the tone is obvious. Extend POS and NEG with the vocabulary your clients actually use.
• Only whole words match. “loved” and “loving” will not match “love” — add the word forms you care about, or switch the check to a stem-based comparison.
• Custom functions are recalculated by the sheet and cannot call services that need authorisation, but a pure-lexicon function like this has no such limit.
• For nuance — negation, sarcasm, mixed sentiment — a lexicon will not get you there. Use Score sentiment in open-text feedback, which sends each answer to Claude.