AI Formulas in Google Sheets That Replace Hours of Copy-Paste
AI formulas in Google Sheets put the model's answer straight into your sheet, so 300 product descriptions stop being 5 hours of copy-paste. Full reference for =AITEXT, =SERP, =PAGEDATA and the rest, 7 workflows and prompt tips.
Phil Nguyen · 19 min read · Updated

AI formulas in Google Sheets send a prompt to a language model and put the answer straight into your sheet, next to your data, the way =SUM puts a total there. That turns a column you would otherwise fill by copy-paste into one formula you drag down, and gives you back the hours the copy-paste took.
You run an online store, and you've got the product list open in one tab and ChatGPT in another. You copy a product name, paste it into the chat, ask for a description, wait, copy the response, paste it back. At about a minute a row, your 300 products are 5 hours of that, and thirty rows in you're already thinking there has to be a better way.
There is. You type a formula into a cell, just like =SUM or =VLOOKUP, and the AI puts the answer right where you need it. One formula, one cell, one result. Drag it down a column and every row fills in on its own while you work on something else.
This guide covers every SheetMagic formula. You'll learn how to write prompts that give you clean, consistent output. And you'll get real workflows you can paste into your sheet and use right away.
Key Takeaways
- AI formulas work like any other spreadsheet formula. Type them in a cell, get a result. No coding. No extra tools. No API setup on the free tier.
- SheetMagic gives you formulas for AI generation, web scraping, and page data extraction.
=AITEXT()for text,=SERP()for search results,=PAGEDATA()for website data,=AIIMAGE()for images, and more. - Cell references make formulas dynamic. Point your prompt at other cells to process entire columns without rewriting anything.
- Six AI providers are supported. OpenAI (GPT-5.5, GPT-5, GPT-4o), Claude (Opus 5, Sonnet 5, Haiku 4.5), Gemini (3.8 Flash, 3.5 Flash, 3.1 Pro), Mistral, OpenRouter (any model it lists), and Perplexity. Switch between them under Extensions > SheetMagic > Open AI Settings without changing your formulas.
- You can try all of this on the free tier. You get 20,000 AI tokens and 100 scraping credits (a one-time allowance) to test everything. No credit card needed.
What are AI formulas?
An AI formula is a spreadsheet function that sends a prompt to a large language model and returns the response in your cell. If you've used =GOOGLEFINANCE() to pull stock prices or =IMAGE() to load a picture, you already get the idea. The formula reaches out to an external source, grabs data, and shows it in your sheet.
The difference is that AI formulas talk to language models like GPT-5, Claude, or Gemini. So they can generate text, summarize content, translate languages, classify data, pull out information, create images, make audio, and handle all sorts of tasks that normally take a lot of manual work.
Every SheetMagic formula, explained
=AITEXT(), your go-to formula
Formula
=AITEXT("Your prompt here")Send any text prompt to the selected AI model and get the response directly in the cell.
Formula
=AITEXT("Your prompt here", A2)Include a cell reference as context. The AI sees both your instruction and the data in A2.
Note
Where the answer lands. AI formulas run in the background, so the cell shows Processing... first. With Keep Formula on (the default), your formula then stays in its cell as plain text and the answer appears in the cell to its right, so keep that column empty. With Keep Formula off, the answer replaces the formula. Either way the finished answer stays put and is not billed again on recalculation. The examples below show each answer in the formula's own column, which is what you get with Keep Formula off.
=AITEXT() is the formula you'll use most of the time. The first part is your prompt, telling the AI what to do. The optional second part is a cell reference that gives it context: a product name, a paragraph to summarize, raw data to analyze.
Think of it like having a writing helper in every cell. You describe what you want, point it at some data, and it delivers.
Advanced pattern, multi-cell context:
Formula
=AITEXT("Write a personalized email subject line for "&A2&" who works at "&B2&" in the "&C2&" industry.")Concatenates data from multiple cells into the prompt using the & operator.
You can also turn on real-time web search by passing TRUE as the third argument: =AITEXT("your prompt", A2, TRUE). This lets the AI search the internet before writing its response. Web search works with OpenAI, Claude, and Perplexity models.
=AILIST() and =AILISTH(), structured list output
Formula
=AILIST("List 8 blog post ideas about email marketing for SaaS companies")Returns 8 results vertically, one per cell, spreading downward from the formula cell.
Formula
=AILISTH("List the 5 largest countries by population")Returns results horizontally across columns in a single row.
Use =AILIST() when you want each item in its own cell for filtering, sorting, or further work. Use =AILISTH() when you want results laid out in a row.
=AIIMAGE(), generate images
Formula
=AIIMAGE("A flat-design icon of a rocket ship, blue and white color scheme, transparent background")Generates an image using GPT Image or a Gemini image model, depending on your selected provider. Returns a link to the image.
Combine it with =AITEXT() for automated visual content, in two columns:
Formula
=AITEXT("Describe a product photo concept for: "&A2&". Be specific about background, lighting, and composition. Keep it under 50 words.")Put this in B2. AI writes the image prompt.
Formula
=AIIMAGE(B2)Put this in C2 once B2 has its text. AIIMAGE generates the visual from that prompt.
Keep the steps in separate cells. An AI formula shows a Processing placeholder first, so =AIIMAGE(AITEXT(...)) in one cell would send the placeholder to the image model.
=GPTV(), analyze images
Formula
=GPTV(A2, "What product is shown in this image? Return: product name, color, and estimated category.")A2 contains an image URL. The AI analyzes the image and returns structured text.
This is super helpful for e-commerce (sorting product photos), real estate (describing property images), and content moderation.
=AISPEECH(), text to audio
Formula
=AISPEECH(A2, "nova")Converts the text in A2 to spoken audio using the Nova voice. Returns a playable audio link.
Voice options, supported languages, and batch narration workflows are covered in text-to-speech and AI audio in Google Sheets.
With the default OpenAI audio provider there are 13 voices, and you can switch the audio provider to Gemini or Mistral in AI Settings. Use this to create voiceovers for presentations, audio versions of written content, or pronunciation guides.
=AIVIDEO(), text to video
Formula
=AIVIDEO("A 10-second product demo showing a coffee mug being filled with steaming coffee, cinematic lighting")Generates a short video from a text description.
=SERP(), live search results
Formula
=SERP("best project management software 2026")Returns up to 10 Google results with titles, snippets, and URLs, in result order.
This gives you structured search data right in your cells. It's great for SEO research, competitive analysis, and content planning. For detailed SEO workflows using =SERP(), read the SEO guide.
=BULKSERP(), horizontal SERP layout
Formula
=BULKSERP(A2)Same data as =SERP() but laid out horizontally. One row per keyword, results across columns.
=VISIT(), full page content
Formula
=VISIT("https://example.com")Retrieves up to 50,000 characters of text content from the URL. Supports JavaScript-rendered pages.
Use this when you need the full text of a page for summarization, analysis, or extraction. Pair it with =AITEXT() to process the content. If you just need quick metadata (title, description, H1), use =PAGEDATA() instead.
=PAGEDATA(), bulk page metadata
Formula
=PAGEDATA(A2)Returns structured metadata for the URL in A2: URL, Title, Meta Description, H1, and Status.
=PAGEDATA() takes one or more URLs and gives you a table of metadata for each page. It's the fastest way to pull title tags, meta descriptions, and H1 headings in bulk.
Formula
=PAGEDATA(A2, A3, A4, A5)Pass multiple URLs to get metadata for all of them at once. Returns one row per URL with Title, Description, H1, and Status columns.
Tip
For targeted data extraction (like pulling specific fields from a page), use =VISIT(A2) to get the page content, then =AITEXT("Extract the company name, founding year, and city. Format as: Name | Year | City", B2) to process it. Use =SPLIT() to break pipe-separated results into columns.
=GETSELECTOR(), CSS selector scraping
Formula
=GETSELECTOR(A2, "h1.product-title")Extracts content matching a CSS selector from the page at A2. For developers who know exactly what element they need.
Page metadata formulas
These formulas pull SEO data from any URL:
| Formula | Returns |
|---|---|
=GETMETATITLE(A2) | The title tag |
=GETMETADESCRIPTION(A2) | The meta description |
=GETH1(A2) | The H1 heading |
=GETH2(A2) | All H2 headings |
=GETHEADINGS(A2) | All headings (H1-H6) |
=GETP(A2) | Paragraph text content |
=GETIMG(A2) | Image URLs from the page |
=DOMAINCHECK(A2) | Whether the domain is available |
For complete scraping workflows, see the web scraping guide.
7 Real Examples You Can Use Today
Every example below uses real SheetMagic syntax you can paste right into your sheet. Each one replaces a manual process.
1. Generate product descriptions from a name and specs
The scenario: Column A has product names, column B has specs. You need a description for each one.
Formula
=AITEXT("Write a 2-sentence product description for an e-commerce listing. Be specific and highlight the main benefit.", A2&", "&B2)Concatenates the product name and specs with a separator. The AI receives both as context.
What each part does:
The first argument is your prompt. It sets the output format (2 sentences), context (e-commerce), and focus (main benefit).
The second argument joins A2 and B2 with &", "& as a separator. If A2 is "Titanium Travel Mug" and B2 is "16oz, double-walled, keeps drinks hot 8 hours," the AI gets both pieces together as context.
Expected output in C2:
The Titanium Travel Mug keeps your coffee at the perfect temperature for up to 8 hours, thanks to its double-walled insulation built into a rugged 16oz frame. This is the last travel mug you'll need to buy, at home or on the trail.
Tip
2. Extract company information from URLs
The scenario: Column A has company URLs. You need their description, headquarters, and founding year without visiting each site.
First, scrape the page content. Then use AI to pull out what you need:
Formula
=VISIT(A2)Pulls the full page content from the URL in A2. Put this in column B.
Formula
=AITEXT("Extract the company description, headquarters city, and founding year. Format as: Description | HQ | Founded", B2)Processes the page content from B2 and returns pipe-separated structured data in column C.
Expected output in C2:
Stripe is a financial infrastructure platform for businesses, providing payment processing, revenue management, and financial tools for internet companies. | San Francisco, CA | 2010
Then use =SPLIT(C2, "|") in columns D, E, and F to break the result into three separate cells.
3. Write SEO meta descriptions at scale
The scenario: Column A has page titles, column B has target keywords. You need a meta description for each.
Formula
=AITEXT("Write a meta description under 155 characters. Include the keyword naturally. End with a clear benefit or call to action. Keyword: "&B2, A2)The keyword from B2 is injected into the prompt. The page title in A2 is passed as context.
Expected output (for a page titled "Free Invoice Template for Small Businesses" with keyword "free invoice template"):
Download a free invoice template designed for small businesses. Professional, customizable, and ready to send in minutes.
For a complete SEO workflow including competitor analysis and rank tracking, see 5 ways to use AI for SEO.
4. Translate content across 50 rows
The scenario: Column A has English text. You need Spanish translations for a localized landing page.
Formula
=AITEXT("Translate to Spanish. Return only the translation, no commentary or notes.", A2)The constraint 'no commentary or notes' prevents the AI from adding explanatory text around the translation.
Expected output (if A2 is "This app saved me hours every week. Highly recommend it."):
Esta aplicacion me ahorro horas cada semana. La recomiendo mucho.
Tip
5. Classify and analyze customer reviews
The scenario: Column A has customer reviews. You need sentiment, topic, and a summary for each.
Formula
=AITEXT("Analyze this review. Return exactly three fields separated by pipes: Sentiment (Positive/Neutral/Negative) | Main Topic | One-sentence summary. Return nothing else.", A2)Structured pipe-separated output for easy splitting with =SPLIT().
Expected output:
Positive | Customer Support | The reviewer praised the fast response time and helpful guidance from the support team.
Use =SPLIT(B2, "|") to separate into columns. Then use =COUNTIF(C:C, "*Positive*") to tally sentiment across your whole dataset.
6. Research competitors using SERP and PAGEDATA together
The scenario: You want to know who ranks for your target keywords and what their pages look like.
Pull SERP data for your keyword
=SERP("best accounting software for freelancers") in B2. This returns up to 10 results with titles and URLs.
Extract competitor page titles and descriptions
Once you have URLs from the SERP data, put them in column C and run:
=GETMETATITLE(C2) and =GETMETADESCRIPTION(C2) for quick metadata.
Or put =VISIT(C2) in column D (it runs in the background and shows a loading placeholder first), then use =AITEXT("What is this page about? Summarize the main value proposition in one sentence.", D2) for a deeper summary.
Identify gaps with AI
Combine competitor summaries into a single analysis:
=AITEXT("Based on these competitor pages, identify 3 topics none of them cover well that would be valuable to the target audience: "&D2&" | "&D3&" | "&D4&" | "&D5, "")
7. Extract structured data from messy text
The scenario: You have messy, unstructured text and need it broken into clean fields.
Formula
=AITEXT("Extract all people mentioned with their job title and company. Format each person as: Name | Title | Company. One person per line.", A2)Returns pipe-separated structured data from unstructured text. Use =SPLIT() afterward to break it into columns.
This works great for processing meeting notes, press releases, or any text with structured info buried in paragraphs. Use =SPLIT(B2, "|") in the next columns to separate the fields into clean cells.
What can you do with the results these formulas return?
The answers land in ordinary cells, so the next step is a normal spreadsheet step. A few that pay off right away:
- Ship the copy. The description column from example 1 is ready for a CSV import into your store. 300 descriptions at a minute each by hand is 5 hours; checking generated ones is a review pass, not a writing session.
- Answer unhappy customers first. Split the review analysis from example 5 into columns,
FILTERfor "Negative", and you have a reply list sorted before anyone reads all 500 reviews. A same-day reply to an unhappy buyer is cheaper than losing them. - Qualify a lead list before anyone calls it. The pipe-separated company data from example 2 splits into an HQ column. Filter out companies outside your territory and your reps stop spending calls on accounts they can't sell to.
- Turn competitor gaps into a content plan. The three topics from example 6 are three briefs you can hand to a writer the same day, instead of after an afternoon of reading competitor pages.
How to Install SheetMagic
Getting started takes about two minutes.
Open Google Sheets
Go to sheets.google.com and open any spreadsheet, or create a new one.
Open the Add-ons menu
Click Extensions in the top menu bar, then Add-ons, then Get add-ons. This opens the Google Workspace Marketplace.
Search for SheetMagic
In the Marketplace search bar, type SheetMagic and press Enter.
Install the add-on
Click on SheetMagic in the search results, then click the Install button. Google will ask you to pick your Google account and approve permissions. SheetMagic needs access to your spreadsheet data (to read cell contents and write results) and external network access (to talk to AI providers).
Grant permissions
Review the permissions and click Allow. These are standard permissions for any add-on that works with your spreadsheet data.
Start using AI formulas
Go back to your spreadsheet. Type =AITEXT("Say hello") into any cell and press Enter. When a greeting shows up (in the cell to the right with Keep Formula on, the default), you're good to go.
No API keys needed on the free tier. No developer console. You install it, and the AI functions are ready in every spreadsheet on your account.
For full details on plans, token limits, and scraping credits, visit the pricing page.
Tips for Writing Good AI Prompts in Spreadsheet Cells
The quality of your output depends on the quality of your prompt. Here are the patterns that work best.
Be specific about the output format
Vague prompts give you vague results. Compare:
| Weak prompt | Strong prompt |
|---|---|
"Describe this product" | "Write a 2-sentence product description for an e-commerce listing. Highlight the primary benefit." |
"Summarize this" | "Summarize in 2-3 sentences. Focus on the main finding and one actionable recommendation." |
"Translate this" | "Translate to French. Return only the translation, no commentary." |
The strong versions tell the AI exactly what you want: length, format, context, and focus. That gives you predictable, usable output.
Use cell references for dynamic prompts
Typing data directly into your prompt goes against the whole point of using a spreadsheet. Always reference cells:
Formula
=AITEXT("Classify the sentiment of this review as Positive, Neutral, or Negative. Return only the label.", A2)A2 can contain any review text. Drag the formula down and every row is analyzed independently.
Join multiple cells together to give richer context:
Formula
=AITEXT("Write a personalized email opener for "&A2&" at "&B2&" in the "&C2&" industry. Reference their recent product launch.", "")Pulls data from three cells to generate a tailored outreach message.
Add limits to keep output short
Without limits, AI models tend to write too much. In a spreadsheet, you want clean, structured data. Add instructions like:
- "Return only the answer, no explanation."
- "Respond in 15 words or fewer."
- "Format as a comma-separated list."
- "Do not include any introductory text."
These small additions make your results way more consistent, especially across hundreds of rows.
Test with one cell before dragging
Caveat
Look at the output. If the format, length, or content is off, adjust your prompt. Once you're happy with it, drag it down the column.
Pick the right model for the task
Different models are better at different things. You switch models under Extensions > SheetMagic > Open AI Settings. Your formulas stay the same.
- GPT-5.5 / GPT-5 / GPT-4o are strong at creative writing, complex instructions, and image generation.
- Claude (Opus 5, Sonnet 5) is great at analytical tasks, following formatting rules, and long-form content.
- Gemini 3.1 Pro and 3.5 Flash are fast and handle large context windows and multilingual tasks well.
- Mistral is cost-efficient and strong with European languages.
- Perplexity is best for research tasks where you need fact-based, cited answers.
Try different providers for your use case. Visit the features page for the full list of models.
Understanding tokens and credits
SheetMagic uses two types of usage. AI tokens vs scraping credits covers both currencies in full, including how each one resets:
AI tokens power all AI formulas (=AITEXT(), =AILIST(), =AIIMAGE(), etc.). Tokens are how AI models measure input and output length. Longer prompts and longer responses use more tokens. A typical product description might use 200-400 tokens.
Scraping credits power the web scraping formulas (=VISIT(), =PAGEDATA(), etc.). Each scraping call uses credits, and the exact cost depends on the operation. Search with =SERP() and =BULKSERP() returns Google results and costs 3 scraping credits per 10 results asked for, with no AI tokens. =DOMAINCHECK() is free: no credits, no tokens.
These two pools are tracked separately in your plan. You can see your remaining balance under Extensions > SheetMagic in the sidebar, or in the SheetMagic dashboard.
| Plan | AI tokens/month | Scraping credits/month | Price |
|---|---|---|---|
| Free | 20,000 (one-time) | 100 (one-time) | Free |
| Solo | 3,250,000 | 10,000 | $20/mo |
| Growth | 12,000,000 | 45,000 | $80/mo |
| Scale | 30,000,000 | 115,000 | $200/mo |
See full details on the pricing page.
What Will You Build First?
You now know every SheetMagic formula. You've got seven ready-to-use examples. And you know how to write prompts that give you clean output. The question is: what will you automate first?
Product descriptions for your catalog. Meta descriptions for your website. Translations for a new market. Lead research from company websites. Survey analysis from free-text responses. Competitive intel from search results. Every one of these used to take hours of manual work. Now each one is a formula.
You can try all of this on the free tier. No credit card needed. You get 20,000 AI tokens and 100 scraping credits (a one-time allowance), enough to test the examples in this guide and start building your own workflows.
Install SheetMagic free from the Google Workspace Marketplace and type your first =AITEXT() formula in the next two minutes.
For more guides: Getting started with SheetMagic | SEO workflows | Web scraping guide | March 2026 product update
Have questions? Visit the FAQ.
Text and scraping formulas run on the free allowance. Install from the Google Workspace Marketplace, no card, no expiry.
Install free for Google SheetsAbout the author
Phil NguyenPhil builds SheetMagic, the Google Sheets add-on that brings AI and web scraping into the spreadsheet, used by 6,300+ people. He writes these guides from the formulas he runs on his own sheets.
