AI Assistant Case Study for Google Sheets

The most common question from spreadsheet users is not how to remove duplicates. It is how to write the formula in the first place, and what to do when the answer needs three steps and none of them fit on one line. This case study follows a fictional membership association of about 1,200 members through three such jobs, all run from Extensions > Flookup Data Wrangler > Sheets AI Assistant in Google Sheets. The organisation is a worked example, but every character limit, batch size and access rule below is the real behaviour documented in the Sheets AI Assistant reference.

The association behind this case study

The association keeps its member list, event attendance logs and sponsor records in one spreadsheet, and one operations coordinator owns all three. The member list holds renewal dates in mixed formats because two people have entered them over five years. The attendance logs arrive from two event platforms with different name spellings. The sponsor list is the worst of the three: the same company appears as Acme Freight Ltd, ACME FREIGHT LIMITED and Acme Freight, because every sponsorship form was typed by hand.

None of this is a recurring automation job. Each need arrives once, gets answered once and returns in a different shape next quarter. That is exactly the space between the Standard Data Cleaning menu and a formula cookbook, which is where the assistant sits.

Where the assistant lives in the sidebar

One sidebar opens with a Mode dropdown at the top, a description field and a submit button. The habit that matters is selecting the data range before the sidebar opens: the assistant reads the active sheet name, the active range and the first three rows of the selection, so its advice names the actual columns instead of describing a generic case. Without a selection the assistant still answers, but the advice stays general.

Asking for a formula in Recommendation Mode

The first job was a renewal report. The coordinator needed the members whose memberships expire within sixty days, working from the renewal date in column D, returning the member name from column B. The description typed into the field was one sentence naming both columns and the outcome, within the 300 character limit Recommendation Mode allows.

Get solutions returns one or more solutions in the results area. Each solution carries a descriptive title, the exact formula to use and a brief explanation of how the formula works, and any formula can be copied to the clipboard with Copy. Nothing has touched the sheet at this point. The coordinator pasted the formula into a scratch column, checked it against a handful of known renewals and only then let it fill the report.

Following up inside the same session

The first answer had one flaw. Blank rows in the renewal column produced misleading zeros, so the follow-up was a single line: make the formula ignore blank rows. The assistant keeps the last few exchanges in memory during an active Recommendation Mode session, so the original problem did not need restating. A second follow-up asked what to do when the renewal date arrives as text rather than a date, and the answer covered that variant in the same thread.

The memory is short by design. It clears when the sidebar closes or after a period of inactivity, and the habit that follows from that is closing the sidebar when switching to a completely different task, so the old conversation does not colour the new one. The same applies when two people share one workstation through the day.

Running the monthly clean in Execution Mode

The second job was the monthly one. The attendance logs from the two event platforms had to be matched against the member list before the newsletter merge. Execution Mode takes the range first, then one sentence describing the workflow: profile the name column for issues, standardise the names, then fuzzy match against the member list at 85 percent threshold.

Run task plans the workflow and executes each step sequentially on the sheet. A progress indicator shows each step as it completes, success steps in green and failures in red with the error message attached. The planner can reach the full set of Standard Data Cleaning tools, from Profile Column through Fuzzy Match to Remove Duplicates, and the distinction that mattered here is the one the reference draws clearly: Find Duplicates only reports groups with a confidence score and never changes data, while Remove Duplicates writes the deduplicated result back.

Cancellation exists and is honest about what it preserves. Stopping a running workflow leaves the completed steps in place rather than rolling them back, which is why the first run of any new workflow in this association started on a copy of the log rather than the log itself.

Cleaning the sponsor list with judgement

The sponsor list defeated rules. Company suffixes arrived in a dozen spellings, casing was inconsistent and some entries carried doubled words from double typing. The third job therefore used the Execute task dropdown to switch from Multi-step workflow to Direct AI clean, which is the Smart Data Cleaning path.

The steps are select the range, set the mode, describe the problem in up to 500 characters, tick the operations and run. The operations cover trim, spacing, casing, punctuation, diacritics, merge variants and fault repair, with an optional list of words or symbols to remove and a match threshold. Naming an operation in the description ticks its checkbox automatically, so the prose and the selections stay in agreement.

Two behaviours make this path safe enough for a sponsor list. Cleaning runs in batches of up to 25 values and the whole range is written only after every batch completes, so a failed batch never leaves half a list rewritten. And values the service cannot clean are left exactly as they were, counted as unmapped in the summary that reports the changed values with before and after examples. In this case the summary showed 96 changed values and 11 unmapped, and those 11 went to the coordinator for a five minute review rather than being guessed at.

What the assistant sends and what it declines

Three constraints define what leaves the spreadsheet, and all three are worth knowing before the first run.

• For the cleaning guide, only column headers and a small sample of rows travel to the AI service, capped at 300 characters per request in Recommendation Mode and 2,000 in Execution Mode, while the full dataset stays in the spreadsheet.
• Smart Data Cleaning sends the values of the selected range in batches of up to 25 so they can be cleaned, which is why the reference advises selecting only ranges the coordinator is comfortable processing.
• The current conversation sits in the Apps Script cache for up to 30 minutes to keep the thread together, and submitted data falls under the AI provider's own policy from that point.
• Requests outside Google Sheets data operations are declined with a brief message, so a general scripting question about emailing members goes unanswered by design.

Workbook context is capped too. The assistant takes up to 15 sheet names and 25 named ranges, reports when it had to use partial context and never sends every name in a large workbook. In this spreadsheet, seven sheets sat comfortably inside the cap.

Plans, keys and limits worth knowing

The assistant runs on the API key of your own Gemini or OpenAI account. What a run costs depends on the tokens it uses and the provider bills directly, while Flookup meters nothing and there are no credits to run low on. If the provider key hits its own limit, only the AI features pause and the cleaning, deduplication and standardisation tools keep working.

The pass check happens before anything runs, so a lapsed pass or an expired trial is met with a purchase message rather than a half-finished step. The trial runs seven days without a credit card and covers the assistant and Smart Data Cleaning, which is enough to prove the workflow on a sample before the member list is touched. The pricing page lists every pass.

Frequently Asked Questions