Score sentiment in open-text feedback

Northwind’s post-event feedback form ends with the dreaded “Anything else?” box, and most weeks the answers pile up faster than anyone reads them. By the time someone exports the sheet to skim it, the event is two months old and the useful signal — was that night a win or a near-miss? — has already faded.

This script gives you a fast, dumb first pass. On every submission it tallies the comment against a small list of positive and negative words and writes the net score into the next free column. It is not sophisticated linguistics — it is a quick triage flag so the inbox-zero brain knows which rows deserve a real read. For nuance, see Quantify tone and sentiment in reviews.

What you’ll need

• A Google Form linked to a Google Sheet, with an open-text question titled exactly Anything else? (case-sensitive).
• Edit access to the sheet — the script writes to the row the submission created.
• A few minutes to tune the word lists for the kind of feedback Northwind actually receives.

The script

// Words that nudge the score upward. Lowercased — the script normalises
// the comment before checking. Add studio-specific vocabulary as you
// notice patterns the defaults miss.
const POS = ['great', 'love', 'excellent', 'helpful', 'quick', 'clear', 'amazing', 'smooth'];

// Words that nudge the score downward. Same rules as above. Tune over
// time — "expensive" shows up in feedback that is not always negative,
// so include it cautiously.
const NEG = ['bad', 'slow', 'confusing', 'poor', 'frustrating', 'rude', 'broken', 'late'];

// The form question whose answer we score. Change once if you rename the
// question — namedValues is keyed by the question title.
const COMMENT_FIELD = 'Anything else?';

/**
 * Runs on every form submission. Tokenises the open-text comment, sums
 * +1 for each positive keyword and -1 for each negative one, and writes
 * the score to the next free column on the same row.
 *
 * @param {GoogleAppsScript.Events.SheetsOnFormSubmit} e Form submit event
 *   from a form linked to a Google Sheet.
 */
function onFormSubmit(e) {
  // 1. The comment is optional — empty answers get a neutral zero.
  const comment = (e.namedValues[COMMENT_FIELD] && e.namedValues[COMMENT_FIELD][0]) || '';

  // 2. Split on non-word characters and lowercase everything. `\W+`
  //    handles punctuation, spaces and dashes in one pass.
  const words = comment.toLowerCase().split(/\W+/).filter(Boolean);

  // 3. One pass through the words, tally as we go. A word in both
  //    lists would cancel itself — we keep the lists disjoint by hand.
  const score = words.reduce((s, w) => {
    if (POS.includes(w)) return s + 1;
    if (NEG.includes(w)) return s - 1;
    return s;
  }, 0);

  // 4. Write the score to the next free column on the submitted row.
  //    `e.range` covers the cells the form just appended.
  const sheet = e.range.getSheet();
  const row = e.range.getRow();
  const col = e.range.getLastColumn() + 1;
  sheet.getRange(row, col).setValue(score);
}

How it works

1. onFormSubmit fires once per submission, with e.range pointing at the row Forms just appended to the sheet.
2. It reads the answer to the Anything else? question, defaulting to an empty string so silent responses score zero rather than throwing.
3. It lowercases the comment and splits it on non-word characters, producing a clean list of tokens. filter(Boolean) drops the empty strings that split leaves on punctuation boundaries.
4. It walks the tokens once, adding 1 for each positive keyword and subtracting 1 for each negative one. The result is a signed integer — positive means broadly upbeat, negative means worth a closer look.
5. It writes the score into the column immediately after the last form column on the same row, so the sentiment lives alongside the response that produced it.

Example run

Say the form receives three submissions in a quiet afternoon:

Anything else?	Score
”The setup was quick and the team was helpful — love it.”	3
”Sound was poor and the start was late.”	-2
”It was fine.”	0

The first response picks up quick, helpful, love for +3. The second catches poor and late for -2. The third contains no scored words and lands at zero. A weekly skim now starts with “show me the rows below zero” — three clicks instead of three hundred read-throughs.

Trigger it

The trigger comes from the form, not the clock:

1. In the Apps Script project bound to the form, open Triggers.
2. Add a trigger for onFormSubmit, source From spreadsheet, type On form submit. (Choosing “From spreadsheet” gives you e.range, which the “From form” trigger does not.)
3. Approve the Sheets scope on the first run, then submit a test response.

Watch out for

• This is a bag-of-words approach. “Not bad” scores -1; “the setup was slow at first, then great” scores zero. For genuine sentiment, hand the comment to a language model instead — see the AI cluster linked above.
• Keyword lists drift. The first month of running this script in production surfaces words you forgot to add — review the lists quarterly.
• The score column is appended each time, so if the sheet already has a manually-added column to the right of the form columns, the script will write into the column after that. Move any extra columns to the left of the form data, or set the column explicitly.
• Sarcasm is invisible to this method. Treat the score as a triage hint, not a judgement — a -3 deserves a read, not an automatic apology email.