How to do it
Get the data into a native Google Sheet
Export last week's numbers from whatever you run: jobs closed, revenue, average ticket, callbacks, unsold estimates. If it comes out as .xlsx, open it in Drive and convert it to Google Sheets format. The feature works best with native Sheets files, and an unconverted Excel file is the most common reason people think it is broken.
Give every column a plain-English header
Not 'REV_NET_TTL'. Write 'Revenue after discounts'. Gemini reads the headers to understand the sheet, and a clear header is worth more than a paragraph of prompt explaining what a cryptic one means. Ten minutes cleaning headers once pays for itself every week after.
Click Ask Gemini and describe the report, not the formula
The button sits at the top right of the sheet and opens the side panel. Ask for the outcome: 'Build a weekly summary showing total revenue, job count and average ticket by technician, with a bar chart of revenue by tech.' It can create tables and charts, generate pivot tables, apply conditional formatting, and sort and filter.
Answer the clarifying questions honestly
It will ask which columns you mean or how you want something grouped. Answer specifically. This is where a report goes right or wrong, and a vague answer here produces a chart you have to redo.
Insert the output into the sheet before you close anything
The panel conversation is not saved. You lose the conversation history when you reload the browser, close and reopen the spreadsheet, or go offline. Anything you want to keep has to be inserted into the spreadsheet itself. Learn this once rather than the hard way.
Use =AI() for the per-row text column
The syntax is =AI("prompt", [optional_data_range]). It does text generation, summarization and categorization, so you can drop a formula down a column to tag every unsold estimate as price, timing or scope. Example: =AI("Categorize this lost-job reason as price, timing, or scope.", C2).
Build it once as a template and reuse it
Once the layout is right, save that sheet as your weekly template. Next Monday you paste in fresh data and the structure holds. Rebuilding the report from a prompt every week is slower than pasting into a sheet that already works, and it drifts.
Where this goes wrong
The parts most guides skip, and the ones that actually cost you money:
- It is not free, and the plan requirements are specific. This needs an eligible Google Workspace or Google AI plan. The =AI() function shipped to Business Standard and Plus, Enterprise Standard and Plus, Gemini Education and Education Premium, and Google AI Pro and Ultra subscribers. A plain gmail.com account on the free tier is not automatically included, though personal accounts can get access through Workspace Experiments.
- Google says do not put confidential information in the prompts. Google's own guidance says to avoid including personal, confidential or sensitive information, because that data is used and stored for product improvement and may be reviewed by humans. Customer names, addresses and payment details do not belong in a Gemini prompt. Aggregate numbers are fine.
- Check the math before anyone sees it. Google's own warning is that these features may suggest inaccurate information and should not be relied on as professional advice. Spot-check the totals against your source system every week for the first month. If revenue is off by 3%, you want to find that yourself, not in a partner meeting.
- A weekly report nobody acts on is still a waste of 25 minutes. Automating the production of a report does not make the report useful. If nothing changes based on what it says, the honest move is to stop making it rather than to make it faster.
Free tools for service businesses
Quotes, invoice follow-ups and review requests. No signup.
Common questions
Can Gemini pull the data itself, or do I have to paste it in?
You paste or export it in. Gemini in Sheets works on the sheet in front of it and can summarize files from Drive and Gmail, but it does not log into your field service software and pull last week's jobs. The export is still a manual step unless you build a separate integration.
Does this work on Excel files?
Not well. Google states the feature works best with native Google Sheets files and that Excel files should be converted to Sheets format first. Converting takes about five seconds in Drive, so do that rather than fighting it.
What if the AI function returns errors down the column?
Check that the range you passed actually contains the text you meant, and that your prompt asks for one short output rather than a paragraph. Per-cell prompts should be one instruction: categorize this, summarize this in eight words. Long prompts in a formula tend to produce inconsistent columns.
Is this better than building the report in Looker Studio?
For a five-person shop, yes, because it is faster to set up and lives where your data already is. Once you need the same dashboard refreshed automatically from a live source every day, a proper reporting tool is the right answer and a prompt is not.
Sources
- Build or edit entire spreadsheets with Gemini in Sheets — Google Docs Editors HelpGemini can create new sheets and complete end-to-end tasks on existing ones, requires an eligible Google Workspace or Google AI plan, works best with native Google Sheets files, and Google advises against including personal, confidential or sensitive information.
- Collaborate with Gemini in Google Sheets — Google Docs Editors HelpThe Ask Gemini button at the top right opens the side panel; Gemini can build tables, charts, pivot tables, conditional formatting and filters; conversation history is lost on browser reload, closing the spreadsheet, or going offline.
- Generate data with Gemini in Google Sheets — Google Workspace UpdatesThe =AI("prompt", [optional_data_range]) function performs text generation, summarization and categorization, and the list of Workspace editions it shipped to.
Related guides
- How to use ChatGPT for job notes
- How to use QuickBooks for expenses and receipts
- How to use ChatGPT for social posts
- How to use Claude for quotes and estimates
Want this built for you instead?
Tell us the job you keep doing by hand. If we can automate it, we will quote it. If we cannot, we will tell you that too. No calls, everything in writing.