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

Step 1: Check you have the Google Sheets AI function

1. Open any spreadsheet in Google Sheets on your computer.
2. In an empty cell, type =AI( and see if the function shows up. You can also go to Insert > Function > AI.
3. If you see "AI function not available," Google lists two reasons: your plan doesn't include it, or your admin or language settings block it.

If you're the admin, check your plan in the Google Admin console. If a teammate can't see it, check that Gemini is turned on for their account.

Paste this test into cell B2 with any word in A2:

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

Select the cell and click Generate and Insert. If it says OK, you're set.

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

The AI function only sees what you point it at. Google says it can't read your whole spreadsheet or other Drive files. So put everything it needs in one row.

Most cleaning software can export inspections or work orders to a CSV file. Check the export or reports menu in Swept or Janitorial Manager, then open the file in Sheets. Keep these columns, in this order:

A: Date
B: Site name
C: Area (e.g. 2F men's restroom)
D: Note or complaint (the raw text)
E: Who logged it (supervisor, client, crew)
F: AI category        <- formula 1
G: AI urgency         <- formula 2
H: AI repeat check    <- formula 3
I: AI crew task       <- formula 4
J: AI client reply    <- formula 5

Leave out contact details and alarm codes.

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

Put each formula in row 2, then drag it down. Each gives a short, fixed answer you can filter on.

Formula 1: Sort the note into one category (column F)

=AI("Classify this cleaning site note into exactly one of these categories: Restroom supplies, Trash, Floors, Dusting, Missed area, Damage, Access/keys, Praise, Other. Reply with the category only.", D2)

Formula 2: Rate how urgent it is (column G)

=AI("Rate the urgency of this note from a facility client for a commercial cleaning company. Reply with exactly one word: High (health, safety, damage or a client complaint), Medium (a missed task), or Low (minor or praise).", C2:E2)

Formula 3: Flag a repeat at the same site (column H)

Sort the sheet by site, then by date. This formula reads every row from the top down to the current one, so it can spot an earlier note about the same problem.

=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)

Formula 4: Turn the note into a crew task (column I)

=AI("Rewrite this cleaning site note as one clear task for the night crew. Start with a verb, name the area, and keep it under 15 words. Do not add anything that is not in the note.", C2:D2)

Formula 5: Draft a short reply to the client (column J)

=AI("Write a 2-sentence reply from a commercial cleaning company to the facility manager about this note. Thank them, say what will be done and when we will re-check. Use a calm, plain tone. Do not promise refunds or credits.", B2:D2)

Step 4: Generate in batches and check before you act

Select the cells with formulas and click Generate and Insert. Google says only the first 350 selected cells generate at once. On a bigger sheet, wait for it to finish, then select the next batch.

You can't undo an AI function, but you can regenerate it with Refresh and Insert. Do the same if you edit a note after generating.

Then filter column G to "High" and column H to "Repeat." That short list is your Monday morning. Read each one yourself before a crew task goes out or a reply gets sent.

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

Five formulas across 200 rows is 1,000 generations. That's a fifth of the 5,000 a month Google lists for Business Standard and Plus. To stretch it, run formulas 1 and 2 on every row, but run 3, 4 and 5 only on rows marked High or Medium.

Monthly limits reset on the first of the month at midnight Pacific time.

• Monday 8:00, export: pull last week's inspections and work orders into the job sheet.
• Monday 8:05, tag: run formulas 1 and 2 on every new row.
• Monday 8:15, focus: filter to High and Repeat, then run formulas 3 to 5 on those rows only.
• Monday 8:30, act: check each crew task and reply, then send them from your usual tools.

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

This is a made-up walkthrough, not a real client. Picture a company that cleans 15 office buildings. Supervisors log notes in their cleaning software, and client emails get pasted into the same sheet.

On Monday the owner exports last week's notes and runs formulas 1 and 2. A few rows come back High. Formula 3 flags some as repeats at one building, all restroom supplies on one floor. That's a stocking problem, not a crew problem, so the owner changes that floor's supply run. Formula 5 drafts the replies, and the owner edits them before sending.

At a glance

• Problem: Site notes and complaints pile up, and repeats get missed until a client escalates.
• What was set up: Five AI formulas in the job sheet, plus a High/Repeat filter.
• Tools it lives in: Google Sheets with Gemini, the cleaning software's export, email.
• What the owner still does: Reads every High and Repeat row, approves each task and reply, decides on credits.
• Payoff: Not measured here. To measure it, time your Monday sort for two weeks before and after, and count repeat complaints per site per month.

Mistakes to avoid

• Letting the AI decide refunds or credits. It only drafts. Money and contract decisions stay with you.
• Open-ended prompts. "What do you think of this note?" gives a paragraph you can't filter. Always give a fixed list of answers.
• Nesting the formula. Google says embedded AI functions aren't supported, so =IF(AI(...)) won't work. Put the AI in its own column, then filter or use IF on that column.
• Feeding it sensitive data. Leave alarm codes, keys and personal details out of the range.
• Trusting the first run. Spot-check 10 rows the first week and tighten the prompt if labels drift.

If it still doesn't work

If the formula won't appear at all, it's almost always the plan. Business Starter doesn't include it. Ask your admin, or check Google's plan list. If you open Sheets through Box, Dropbox or Egnyte, Google says the AI function won't generate there, so open the file directly in Google Drive.

If it gives odd answers, the prompt is too loose or the range is wrong. Point it at the right columns and list the exact answers you want. If you've hit your limit, wait for the reset, or tag only High rows until then.

Doing this inside the tools you already use

The formulas above get you a sorted sheet every Monday. Some owners would rather not run the export, the batches and the filtering by hand each week. That's the kind of job Deeplathe builds: a small custom setup that pulls new notes from your cleaning software into the sheet, tags them, flags repeats per site, and drafts the crew task and client reply. You still read and approve every one before it goes anywhere, and nothing is sent without you. It lives in the Sheets, email and software you already use. If you want to see what that would look like for your job sheet, here's how we work: deeplathe.com/solutions.

FAQ

Is the AI function in Google Sheets free?

Not on every plan. Google lists it for 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. On Business Standard and Plus, the limit is 5,000 AI function generations per month per user. There's no extra charge per formula inside that limit, but you need one of those plans.

Why is the AI function not working in Google Sheets?

Google gives a few reasons. Your plan may not include it, your admin may have turned Gemini off, or your language setting may not be supported. It also won't generate if you open Sheets through Box, Dropbox or Egnyte. If it worked before and stopped, you may have hit your generation limit for the month.

Can I use =AI and =Gemini interchangeably?

Yes. Google's help page says you can use either =AI() or =Gemini(), and they work the same way. Pick one so your team's sheets match. If you built a custom function with either name, Google says it now maps to the built-in one.

How many cells can the AI function fill at once?

Google says only the first 350 selected cells generate in one go. For a bigger sheet, wait for the batch to finish, then select the next set. Each filled cell counts toward your monthly limit.

Keep these

• One formula, one job, one fixed list of answers.
• Point it at one row, never the whole sheet.
• AI tags and drafts; you approve tasks, replies and credits.
• Spend generations on High and Repeat rows first.

Sources

• Use the AI function in Google Sheets (Google Docs Editors Help)
• About AI usage limits (Google Workspace Admin Help)
• The AI function in Google Sheets is now enhanced with Google Search results (Google Workspace Updates)

Related reading

• Part of: The process that only lives in your head
• How to use ChatGPT to analyze a chart for your business
• How to use NotebookLM for business documents
• Can ChatGPT make a spreadsheet you can actually use?
• Commercial cleaning checklist template
• How to respond to a cleaning complaint the same morning