Google Sheets AI function: 6 formulas for marketers

Google Sheets has an AI function that runs Gemini on a cell or range and writes the answer back into the sheet. You type =AI("your instruction", B2) and drag it down. For marketing teams, that turns jobs like tagging leads, summarising reviews and drafting ad variants into a spreadsheet task. Below are six formulas we use, and the limits that will trip you up.

How the AI function works

According to Google's help page, the syntax is AI(prompt, [range]). The prompt says what to do. The optional range is the data it should work on, and =Gemini() works as an alias. You need an eligible Google Workspace or Google AI plan, and your admin can switch the feature off.

Four rules matter in practice:

• It only sees the range you give it. Google says plainly: "The AI function doesn't have access to your entire spreadsheet or other files in your Google Drive." If the answer needs column C, include column C.
• It generates 350 cells per run. Select the next batch once the first finishes. For a 2,000-row lead sheet, that's six runs.
• Output is text only, and it doesn't recalculate on its own. Change the source data and you have to click "Refresh and insert" yourself.
• It can't sit inside other functions. =IF(A2="", "", AI(...)) won't work. Put the AI output in its own column, then run normal formulas on that column.

There are also daily generation limits. If you hit the long-term limit, Google says to wait 24 hours, so don't plan a 10,000-row job the night before a client review.

Six formulas for marketing teams

The examples assume a lead export from Meta or Google Ads, a review dump, or a keyword list. Change the column letters to match your sheet.

1. Classify lead intent

``` =AI("Classify this enquiry as Hot, Warm or Junk. Hot = mentions budget, timeline or a site visit. Junk = gibberish, wrong number or job seeker. Reply with one word.", C2) ```

This works well on the free-text "message" field from lead forms. Pair it with the form fixes in our guide to Meta lead ads junk leads. Then check the Junk rate by campaign before you cut budget.

2. Pull the city from messy addresses

``` =AI("From this text, return only the city name in English. If none, return Unknown.", D2) ```

This is useful when leads type "Whitefield, Blr" or "near Hinjewadi Pune". Spot-check 20 rows, because Gemini will occasionally guess.

3. Summarise a review in one line, with a theme

``` =AI("Summarise this review in under 12 words, then add | and one theme from: Delivery, Price, Quality, Staff, Other.", B2) ```

Split on | with SPLIT() in the next column, then pivot by theme. For a clinic or restaurant chain with 800 Google reviews, you get the complaint mix per outlet in about 15 minutes.

4. Draft responsive search ad headlines within the limit

``` =AI("Write one Google Ads headline under 30 characters for this product. No exclamation marks. Include the price in rupees if given.", A2:B2) ```

Add =LEN(C2) next to it and filter for anything over 30. The model doesn't count characters reliably, so this check is required.

5. Group keywords by intent

``` =AI("Label this search term as Informational, Commercial, Transactional or Navigational. One word.", A2) ```

Run it on a Search Console or search-terms export. It's a quick first pass before you build ad groups or a content plan.

6. Flag claims that need a compliance check

``` =AI("Does this ad copy make a health, financial-return or 'guaranteed' claim? Answer Yes or No, then the phrase.", B2) ```

This is useful for healthcare, real estate and fintech brands, where a "guaranteed returns" or "100% cure" line can get an ad rejected or create legal risk. It flags rows for a human. It does not replace legal review.

Where it falls short

One more caution: outputs vary from run to run. If a client report depends on the labels, freeze them with Copy → Paste values only once you've reviewed them. Otherwise a refresh can quietly change last month's numbers. That's the same discipline we recommend for Meta Ads metrics that actually matter: decide your definitions once, then keep them fixed.

What to do this week

1. Check access. Type =AI("test") in a blank sheet. If it errors, ask your Workspace admin whether Gemini features are on for your account.
2. Run formula 1 on last month's leads. Compare the Junk share by campaign with what your sales team reports.
3. Run formula 3 on your Google reviews export and share the theme pivot with operations.
4. Build a 30-character check column before you use any AI-written headlines.
5. Write a prompt sheet. Keep your tested prompts in one tab so the team reuses them instead of rewriting them.

FAQ

Is the AI function in Google Sheets free?

It needs an eligible Google Workspace or Google AI plan, according to Google's help page. Availability also depends on your admin settings, so check your account rather than assuming.

Why does my AI formula stop after 350 rows?

Google generates only the first 350 selected AI cells per run. Select the next block and run it again once the first batch completes.

Can the AI function read other tabs or files?

No. It works only on the prompt and the range you pass it. Include every column it needs in that range.