SOPs

Google Sheets AI function: 5 paste-ready formulas for a cleaning company's job sheet

How the Google Sheets AI function is scoped, metered and refreshed, and how to build a five-column tagging pipeline on a cleaning job sheet.

Google Sheets AI function: 5 paste-ready formulas for a cleaning company's job sheet

For operators who want the mechanics: how the Google Sheets AI function is scoped, metered and refreshed, and how to build a five-column tagging pipeline on a cleaning job sheet.

A weekly export of inspection notes and client complaints lands in one sheet. The free-text column is the only place repeats and escalations show up, and no filter can read it. That column is what the AI function is for.

The short answer

The Google Sheets AI function, =AI(prompt, [range]) or =Gemini(prompt, [range]), sends a prompt plus the referenced cells to Gemini and writes a text result into the cell. Constrain each formula to one task, an enumerated output set and the smallest range that carries the context, then review outputs before acting. Access depends on plan: Business Starter has none, and Business Standard and Plus are metered at 5,000 generations per month.

Why site notes pile up unread

Free-text notes have no controlled vocabulary. "TP out, 2F men's" and "restroom supplies low" are the same issue with zero shared tokens, so FILTER, QUERY and pivot tables can't group them. Classification needs a model that reads meaning, not strings.

Google documents four jobs for the function: generate text, summarize, categorize (including sentiment) and pull current information from Google Search (Google Docs Editors Help). The model sees only the prompt and the optional range. It has no access to the rest of the spreadsheet or to other Drive files.

Metering is per generated cell, not per prompt. Google's admin page lists 5,000 per month on Business Standard/Plus and Enterprise Standard/Plus, 25,000 with the AI Expanded Access add-on, and no access on Business Starter (Google Workspace Admin Help). Monthly limits reset at 12 AM PT on the first of the month and don't roll over.

Floor vote

When you open the AI function in your Google Sheets today, what happens?

One vote per device. Change it any time.

Loading votes…

Step 1: Check you have the Google Sheets AI function

Eligibility per Google: Business Standard and Plus, Enterprise Standard and Plus, eligible Google AI plans where Google lists them (check your account), and earlier Gemini Business or Enterprise add-on buyers (Google Workspace Updates). Entry points are typing =AI( or Insert > Function > AI. "AI function not available" means the plan lacks it, an admin has Gemini off for that user or org unit, or the language setting isn't supported.

=AI("Reply with the single word OK if you can read this cell.", A2)

Step 2: Lay out the job sheet so each formula reads one row

Because context is limited to the range argument, the schema matters. Put every field a prompt needs in contiguous columns of one row. For non-contiguous inputs, Google shows string concatenation inside the prompt, but those cells don't trigger an out-of-sync prompt when they change.

A Date | B Site | C Area | D Note (raw) | E Logged by
F Category | G Urgency | H Repeat | I Crew task | J Client reply

Export from Swept or Janitorial Manager via their reports or export menu to CSV, then import. Strip alarm codes, key numbers and personal details before they enter any range.

Step 3: Paste the 5 AI formulas into your job sheet

Enumerated outputs keep columns filterable and reduce drift.

F2: =AI("Classify this cleaning site note into exactly one of: Restroom supplies, Trash, Floors, Dusting, Missed area, Damage, Access/keys, Praise, Other. Reply with the category only.", D2)
G2: =AI("Rate urgency for a commercial cleaning company. Reply with exactly one word: High (health, safety, damage or client complaint), Medium (missed task), Low (minor or praise).", C2:E2)
H2: =AI("The last row in this range is a new cleaning site note. Do any earlier rows show the same problem at the same site? Reply with exactly one word: Repeat or New.", B$2:D2)
I2: =AI("Rewrite this note as one night-crew task. Start with a verb, name the area, under 15 words. Add nothing not in the note.", C2:D2)
J2: =AI("Write a 2-sentence reply from a commercial cleaning company to the facility manager. Thank them, say what will be done and when we re-check. Calm, plain tone. No refunds or credits.", B2:D2)

H2 uses an expanding range anchored at row 2, so sort by site then date first. Its input grows with each row, which raises latency on long sheets.

Step 4: Generate in batches and check before you act

Formulas don't run on entry. Select cells and click Generate and Insert. Google caps one generation at the first 350 selected cells. Generated content is attributed to you in version history. There's no undo, only Refresh and Insert. Nesting isn't supported, so =IF(AI(...),...) fails. Branch on the output column instead:

=FILTER(A2:J, G2:G="High", H2:H="Repeat")

Step 5: Spend your monthly limit on the rows that matter

Cost is rows times AI columns. 200 rows across five columns is 1,000 generations, a fifth of a 5,000 monthly cap. Run F and G on every row, and I and J only where G is High or Medium. Google also lists short-term limits. If a long-term limit is hit, it says to wait 24 hours and retry.

Monday sort, 30 minutes

8:00ExportPull last week's inspections and work orders into the job sheet.
8:05TagRun the category and urgency formulas on every new row.
8:15FocusFilter to High and Repeat, then run the task and reply formulas on those rows.
8:30ActCheck each crew task and reply, then send from your usual tools.

Spend AI generations on the rows that matter; you approve everything that goes out.

Monday 8:00, export: CSV from cleaning software into the job sheet.
Monday 8:05, tag: generate F and G for all new rows.
Monday 8:15, focus: FILTER to High and Repeat, generate H to J on those.
Monday 8:30, act: review each task and reply, send from your usual tools.

Example (illustrative): a 20-person office cleaning company

A made-up walkthrough, not a client. The owner imports a week of notes from 15 buildings, generates F and G for every row, then H to J only on High rows. H groups several restroom-supply repeats on one floor, which points to the stocking schedule rather than the crew.

At a glance

Problem: Unstructured notes hide repeat issues per site.
What was set up: Five enumerated AI columns plus a FILTER view.
Tools it lives in: Google Sheets with Gemini, cleaning software CSV export, email.
What the owner still does: Reviews every High and Repeat row, approves tasks and replies, decides credits.
Payoff: Not measured here. Measure minutes spent on the Monday sort and repeat complaints per site per month, before and after.

Mistakes to avoid

Letting the model decide money. Credits and contract decisions stay human.
Free-form outputs. Unbounded answers break FILTER and pivots.
Nesting AI in IF or other functions. Not supported. Use a helper column.
Over-wide ranges. Whole-column ranges add noise and latency.
No spot-check. Sample 10 rows a week and tighten the label list if it drifts.

Field check

Three questions. Honest answers. No score sent anywhere but this page.

01 / 03

Which Google Workspace plan has no access to the AI function?

If it still doesn't work

No function at all usually means plan or admin settings. Files opened through Box, Dropbox or Egnyte can't generate, per Google. Odd output usually means a loose prompt or a range missing a key column. A greyed-out Generate button points to a usage limit.

Doing this inside the tools you already use

The formulas need a person to export, batch and filter each week. Deeplathe builds that loop as a custom setup inside your existing tools: new notes flow from your cleaning software into the sheet, get tagged, repeats are flagged per site, and a crew task and client reply are drafted for each High row. You review and approve every item, and nothing goes out without you. It runs in the Google Sheets and email you already use. See how we work: deeplathe.com/solutions.

FAQ

Is the AI function in Google Sheets free?

It's included, not separately billed, on eligible plans: Business Standard and Plus, Enterprise Standard and Plus, and eligible Google AI plans where Google lists them (check your account). Business Starter has no access. Usage is metered per generated cell, at 5,000 a month on Business Standard and Plus, and 25,000 with AI Expanded Access.

Why is the AI function not working in Google Sheets?

Check plan eligibility, then the admin's Gemini setting for that user, then the language setting. Files opened via third-party storage can't generate. If it worked and stopped, a short-term or monthly limit is the likely cause. Nested formulas like IF(AI()) also fail by design.

Can I use =AI and =Gemini interchangeably?

Yes. Both names run the same built-in function. A custom Apps Script function named AI or Gemini is remapped to the built-in one, so rename any custom function you rely on.

How many cells can the AI function fill at once?

The first 350 selected cells per generation, per Google's help page. Larger ranges need multiple passes, and every generated cell counts toward the monthly limit.

Keep these

One task and one enumerated output set per column.
Smallest range that carries the context.
Branch on output columns, never nest the AI formula.
Humans approve tasks, replies and credits.

Sources

The working rules

Constrain each formula to one task, an enumerated output set and the smallest range that carries the context, then review outputs before acting.

Comments

A short note on the job in this piece. Name optional. Saved on this page, not a profile.

Loading comments…