Sorting Forms free-text answers with the Sheets AI function: decide the shelves before you write the formula

Somewhere past the hundredth response, my finger gets heavy on the scroll wheel. Free-text answers from a request form look different one by one, and before I notice, the question has quietly changed from "which one should I reply to first?" to "what was I looking for in the first place?"

As an indie developer who reads app feedback week after week, I know that feeling well. The weight isn't the number of rows. It's that the sorting rule lives only in my head.

So I let the Sheets AI function help with the sorting. But there is one thing to settle before any formula gets typed: the shelves.

Fix three shelves and an "other" first

If you simply ask an AI to "categorize these nicely," the wording drifts from row to row. "Request," "feature wish," and "suggestion" end up as three different labels for the same thing, and you can't count them later.

So I fix the labels by hand beforehand. Here they are, four in all.

"Other" isn't an escape hatch. It's a place for the AI to put answers it would otherwise force into one of the three. The shelf names are the exact strings you'll later count with COUNTIF or a pivot table, so once they're set, I don't rename them midway.

Bring the form responses into a sheet

In the Responses tab of Google Forms, choose the link-to-Sheets option and the answers start piling up in a spreadsheet. I'll assume column C holds the free text and column D will hold the result. Put "category" in D1 and "my call" in E1; column E is for comparing against the AI later.

A formula in column D does not follow new response rows by itself. If you assume it will, the newest answers stay unsorted. I plan on copying the formula down by hand.

Start with one row

Put this in D2.

=AI("Classify the following free-text answer as exactly one word from: request, bug, thanks, other. Do not explain.", C2)

Three things are pinned down here.

• The shelf names appear in the prompt exactly as I want them in the cell
• "Exactly one word" and "do not explain" keep the cell down to the label alone
• The text to read is passed as the second argument, the range C2

Google's help page strongly recommends giving the function its data through that range argument. The AI function can't read the whole sheet or other files in Drive, so anything you want it to see has to be in the range.

Select D2 and press Generate and insert. The cell gets one label, and the change history records you as the editor of that cell. You can also get there from Insert, then Function, then AI.

Try twenty rows, then compare with your own reading

I don't run all rows at once. I select D2 through D21 and generate only those. When you select several cells, only the first 350 are generated, so twenty is nowhere near that limit.

If the button won't respond or nothing generates, the cause may be where the file is stored or which plan you're on. I wrote that triage in a separate article, When the Sheets AI Function Won't Generate, Suspect the File's Location Before the Limit.

Once the twenty rows are filled, read them yourself and type your own call into E2 through E21. It's the tedious part, and the most important one. Then put this in F2 to flag disagreements.

=IF(D2=E2, "", "check")

And count them with this.

=COUNTIF(F2:F21, "check")

If even a few of twenty disagree, I rewrite the definition before running the rest. That's a rule of thumb, not a rule, but a mismatch you see in twenty rows comes back ten times larger in two hundred.

When I read the mismatched rows, the cause is more often a fuzzy boundary between shelves than a misreading by the AI. Take "I'm not getting notifications." Is that a bug or a request? If a person hesitates, the AI will too.

So I add the boundary in one sentence.

=AI("Classify the following free-text answer as exactly one word from: request, bug, thanks, other. If the answer says something cannot be done or does not work, it is a bug. If it says how things should be, it is a request. Do not explain.", C2)

You could keep the definition in a separate cell and join it into the prompt with concatenation. But the help page notes that when data in a concatenated range changes, you won't be prompted to refresh. After editing the definition cell, you decide when to regenerate.

Freeze the result as values

AI function output can be regenerated. The flip side is that opening the same sheet later and regenerating may not give you the same answers. And according to the help page, the function can't be undone or redone.

So once the sorting has settled, I turn the column I'll tally from into plain values. Copy and paste-values works, but I like doing a selected range in one step, so I keep a small Apps Script function around.

/**
 * Replace formulas in the selected range with their current values,
 * so AI function output is frozen before it goes into any tally.
 */
function freezeSelectedRange() {
  const range = SpreadsheetApp.getActiveRange();
  range.setValues(range.getValues());
}

getValues() returns what you currently see, not the formulas, and writing it back with setValues() replaces each formula with its value. There is no undo, so make a copy of the sheet before running it.

When the function isn't available at all

The help page lists reasons the "AI function isn't available" message can appear: you aren't in the Workspace Experiments program or your Workspace plan doesn't include the function, or an admin or language setting is in the way.

The only thing you can fix yourself is the language setting. For the rest, checking your plan is as far as I'd go before asking an administrator.

Tonight, just twenty rows

Sorting accuracy depends less on how the formula is phrased than on how clearly the shelf names and their boundaries were decided up front. Write the formula last and decide the shelves first. That order is one I try to keep no matter how many forms I end up reading.

Tonight, pick twenty rows from your own responses, write down four shelf names, and put one formula in D2. Reading the rows that disagree becomes your next definition.