The AI function in Google Sheets is a button, not a formula

What are the AI functions in Google Sheets, actually?

There is no single AI feature in Sheets, which is why the advice about it contradicts itself so often. There are four layers, they behave differently, and picking the wrong one is how a sheet ends up either slower than doing the job by hand or quietly wrong.

The first layer is the one nobody calls AI: the ordinary function library. QUERY, REGEXEXTRACT, SUMIFS, ARRAYFORMULA. Deterministic, free, auditable, and still the correct answer for most of what people now reach for a model to do.

The second is Smart Fill. Google’s Smart Fill documentation describes it as detecting patterns to help automate data entry, and tells you to click a cell to see the formula used. That instruction is the useful part: where Smart Fill hands you a formula, you have something you can read, keep and check, which puts it much closer to layer one than to a model.

The third is the AI function itself, which runs a Gemini prompt against a cell or a range you point it at. The fourth is Apps Script, where you write a custom function that calls a model API yourself. Four layers, four very different failure modes.

Read the last column downward and the shape of the decision appears. Three of the four break in ways you find out about immediately. Layer three breaks silently, days later, in a file somebody else has already forwarded.

Why does the AI function behave like a button and not a formula?

Because Google built it that way, and documented it. The output is generated once and inserted; when the data it referred to changes, the cell does not follow. Google’s help page for the AI function in Sheets puts the responsibility on you, saying you need to manually determine when to refresh the data.

A second line in the same documentation gives the game away completely: you cannot undo or redo the function, you regenerate the output instead. Undo applies to actions. Regeneration applies to buttons. Nothing about a live formula works like that.

Here is why that matters more than it sounds. You build a sentiment column on Monday against 42 rows of feedback. By Wednesday the sheet has 88 rows with a different story in them, and every AI cell still shows Monday’s reading. The numbers beside it, being real formulas, have all moved. The sheet now disagrees with itself and looks completely normal while doing it.

This is the same species of problem as the silent errors that reach the final report, with one extra twist. A broken lookup at least has the decency to show an error. A stale AI cell shows a fluent, plausible, confident sentence about last week.

Which limits decide whether you can use it at all?

Four of them, and they are worth knowing before you design a sheet around the feature rather than afterwards. All four are published by Google on the AI function help page, which is a page most guides cite for the syntax and then stop reading.

The first is volume. Google states that when you select multiple cells with AI functions, only the first 350 selected cells will be generated. That is a comfortable ceiling for tagging a week of support tickets and a hard wall for a five thousand row export, and it arrives after you have already written the prompt.

The second is composition, and it is the one that surprises people who think in formulas. Embedded AI functions are not supported: you cannot wrap the call inside an IF, which means you cannot make it conditional, cannot fall back when it returns nothing, and cannot chain it into the rest of your sheet the way you would chain anything else.

The third is scope. Google states plainly that the AI function does not have access to your entire spreadsheet or to other files in your Drive. It sees the prompt and the range you hand it, nothing else, so questions about the workbook as a whole are the wrong questions to ask it.

The fourth is capacity. There are short term and long term generation limits, and reaching a long term one temporarily disables the Generate button. Google also states the feature requires an eligible Workspace or Google AI plan, so on plenty of accounts this whole layer simply is not there.

Note the fourth row. Text output that looks like a number is still text, and a category column produced by a model will contain near duplicates the moment the phrasing drifts. That is a cleaning job arriving through the front door, and it wants the same treatment as any other, which is the order laid out in cleaning messy spreadsheet data.

Which layer does a given task actually belong to?

The decision is not about how clever the task is. It is about whether the rule can be written down. If you can state the rule precisely, it belongs in a formula, and a formula will be faster, free and checkable forever. A model is for the jobs where writing the rule down is the hard part.

1. 01
   Can you write the rule as a sentence a colleague could follow?

   Then it is layer one. "Flag anything over 30 days past due" is a rule. It does not need a model, and putting one there costs you determinism for nothing.
2. 02
   Is it the same transformation repeated down a column?

   Splitting names, reformatting codes, tidying a repeated pattern. Try Smart Fill, then open the cell and read what it produced before you accept it.
3. 03
   Does it need judgement about language, and does it need it once?

   Categorising free text, summarising comments, reading intent out of a message. That is the AI function, on a snapshot, on a batch small enough to check.
4. 04
   Does it need to be current, conditional, or bigger than the ceiling?

   Then it is layer four, or it is not a spreadsheet job at all. Anything that has to be right tomorrow morning without a person pressing a button belongs in code.

That last row is the honest one. Every layer above moves work; none of them removes the person who has to stand behind the answer. It is the same conclusion I reached comparing Power Query and Python for automation: choose the lowest layer that survives the job, because the lowest layer is always the cheapest to check.

How do you make an AI column safe to hand to a client?

By making it obvious that it was generated, and when. Five columns do the whole job: the input, the prompt version, the output, the date it was generated, and who checked it. It costs three extra columns and it turns a liability into a piece of work somebody can audit.

The prompt version column matters more than it looks. When you change the wording of a prompt halfway through a project, rows tagged before the change and rows tagged after are not comparable, and six weeks later nobody will remember there was a change. A short label beside each row records it for free.

Then freeze it. Once a batch is generated and spot checked, paste it as values into the column that feeds your report, so the text in the report cannot silently change under a colleague who clicks Generate. The generated cells stay in a working tab; the frozen values are what people read.

1. 01
   Check a stratified sample, not the top ten rows

   Take a few from each category the model produced, including the smallest one. Models are least reliable on the categories they used least, and those rows sit at the bottom where nobody scrolls.
2. 02
   Count the distinct outputs before you trust the column

   A tagging column that was supposed to produce four categories and produced nineteen has failed, whatever the individual rows look like. That one check catches most of it in ten seconds.
3. 03
   Keep the prompt in the file, not in your head

   A notes tab with the exact prompt text, the date, and the range it ran against. It costs a minute, and it is the difference between a rerun and a rebuild.

This is the same verify-first discipline as asking AI for spreadsheet formulas, applied one level up. There the model writes something you can test against a row you calculated by hand. Here it writes the answer itself, so the test has to be built into the sheet.

What if your plan does not include the AI function?

Then you build layer four, and it is a better answer than it sounds. A custom function in Apps Script calls a model API directly, and Google’s external APIs documentation covers the request side. You supply your own key and pay the API directly rather than through a plan upgrade.

It also fixes the staleness problem outright. Google’s custom functions guide states that passing a referenced cell or range directly as an argument is what triggers recalculation. Your own function then behaves the way people already expect a cell to behave, which is exactly what the built-in AI function does not do.

The trade-offs are real and they are also documented. A custom function call must return within 30 seconds or the cell shows an error, which rules out long generations. Custom functions never ask for authorisation, so they can only call services that have no access to personal data. And recalculation you did not intend is now a bill, because every recalculated cell is another API call.

If you have never opened the script editor, that is a smaller step than it looks, and what Apps Script can do inside a Google Sheet covers the ground before this one, including the trigger limits that decide whether a scheduled job belongs there at all. Past that point the sheet stops being the right home for the work, and a proper report pipeline takes over.