Bulk SERP Scraping in Google Sheets
Pull search results for 50+ keywords at once in Google Sheets. Build a keyword research dashboard without expensive SEO tools.
Phil Nguyen · 13 min read
You are paying $99/month to export CSVs
Ahrefs Lite costs $129/month. Semrush's entry SEO plan lists at $117/month. And most of the time, you export the data into a spreadsheet anyway. You run a keyword report, click Export, open the CSV in Google Sheets, and start sorting.
That is a lot of overhead just to see who ranks for your keywords.
What if you could skip the middleman? Type a keyword into a cell, get up to 20 results with titles, URLs, and snippets. Then drag the formula down 50 rows and get the same data for every keyword on your list.
That is exactly what =SERP() and =BULKSERP() do. And they work right now, inside Google Sheets.
Formula
=SERP("best project management software")Returns up to 20 DuckDuckGo search results with titles, snippets, and URLs, in result order. One formula, one keyword, structured data in your cells.
Try that formula with any keyword you are researching. The results show up in your sheet within seconds. No API keys to configure. No Python scripts to debug. Just a formula.
How SERP scraping works in SheetMagic
SheetMagic gives you two formulas for pulling search engine results.
=SERP() for vertical results
Formula
=SERP(A2)Returns search results vertically. Each result gets its own row with title, snippet, and URL.
=SERP() returns results in a vertical layout. Each search result takes up its own row. This is great when you want to dig deep into one keyword. You can see every result, scan the titles, and spot patterns in what ranks.
=BULKSERP() for horizontal comparison
Formula
=BULKSERP(A2)Returns results horizontally. One keyword per row, results spread across columns. Perfect for comparing 50 keywords at a glance.
=BULKSERP() keeps each keyword on a single row. The results spread across columns instead. This is the formula you want for bulk keyword research. Put 50 keywords in column A, put =BULKSERP(A2) in column B, and drag down. Your entire keyword landscape fits on one screen.
Tip
Use =SERP() when you are exploring a single keyword in depth. Use =BULKSERP() when you are comparing many keywords at once. Neither one uses AI tokens. Both run on DuckDuckGo and cost 1 integration credit per search on every plan, including Free.
Build your keyword research dashboard
This is where things get fun. You are going to build a keyword research dashboard that would cost you $1,548/year on Ahrefs Lite. It takes about ten minutes.
Set up your seed keywords
Put your target keywords in column A. Start with 10 to 20 keywords. These can be terms you want to rank for, topics you are writing about, or keywords your competitors target.
For example:
- A2:
best crm for startups - A3:
project management tool comparison - A4:
free invoice template google sheets - A5:
how to track expenses in a spreadsheet
Pull SERP data for every keyword
Formula
=BULKSERP(A2)Put this in B2 and drag down to B21. Each row fills with the top search results for that keyword, spread across columns B to P (five results, three cells each).
You now have the top-ranking pages for every keyword. Titles, URLs, and snippets, all in your spreadsheet.
Extract who ranks in position 1
Formula
=AITEXT("From these search results, extract only the URL of the #1 ranking result. Return just the URL, nothing else.", B2:P2)Pulls the top-ranking URL for each keyword. The context range covers the whole spilled row, since B2 alone holds only the first title. Put in a new column and drag down.
Now you can see exactly who owns the top spot for each keyword. Look for patterns. Does one competitor dominate? Are there smaller sites sneaking into position 1?
Classify search intent
Formula
=AITEXT("Classify this keyword by search intent. Return one label: informational, navigational, transactional, or commercial. No explanation.", A2)Tags each keyword with its search intent. Filter by intent to prioritize your content calendar.
Search intent tells you what to create. Informational keywords need blog posts. Transactional keywords need product pages. Commercial keywords need comparison guides.
Score the opportunity
Formula
=AITEXT("Look at these search results. Are the top results from major authority sites (Wikipedia, Forbes, government sites) or from smaller niche sites? Return: HIGH OPPORTUNITY if smaller sites rank, LOW OPPORTUNITY if dominated by major authorities. Add one sentence explaining why.", B2:P2)Scores whether you can realistically compete for this keyword based on who currently ranks.
This is your difficulty filter. Keywords where small sites rank well are the ones you should target first.
After five steps, you have a keyword research dashboard with SERP data, search intent, and opportunity scores. All of it is formula-driven. Change a keyword in column A and the entire row updates.
Note
You can try all of this on the free tier. No credit card needed. Free comes with 20,000 AI tokens and 100 integration credits, one time, with no expiry, enough to run the AI columns on a few dozen keywords and see how the workflow fits your process. Install SheetMagic free and build your first dashboard today.
Competitive SERP analysis: who ranks for your keywords?
Keyword research tells you what to write. Competitive SERP analysis tells you who you are up against and how to beat them.
Map your competitive landscape
Formula
=SERP("your target keyword")Pull the full SERP for a keyword you want to rank for. Copy the top 10 URLs into a new column for deeper analysis.
Once you have the top-ranking URLs, you can break them apart.
Formula
=GETMETATITLE(A2)Extracts the title tag from each competitor URL. Scan these to see what titles Google rewards for this keyword.
Formula
=GETH2(A2)Extracts all H2 headings from the competitor page. This gives you their full content outline without reading a single paragraph.
Now you know what the top results cover. Compare their outlines side by side. Look for topics that multiple competitors cover (table stakes) and topics only one or two mention (potential differentiators).
Find content gaps your competitors miss
Formula
=AITEXT("Here are the H2 heading outlines from 5 articles ranking for the keyword: best crm for startups. Identify 3 subtopics that none of them cover well. Be specific and actionable.", B2&" ||| "&B3&" ||| "&B4&" ||| "&B5&" ||| "&B6)Feeds competitor outlines into AI analysis. The gaps it finds become your content advantage.
The gaps are your opportunity. Every subtopic your competitors skip is a chance to create something more useful. Google rewards the most complete answer.
Tip
Feed the content gaps into a content brief. Use =AITEXT("Create a blog post outline that covers these underserved subtopics: "&C2) to generate a structure that fills the gaps. For a full content brief workflow, see the SEO guide.
Turn search results into content angles
=SERP() returns the organic listings: titles, snippets, and URLs. That is enough to read what a topic looks like competitively, and enough for AI to turn into an outline.
Note
=SERP() does not return SERP features. Featured snippets, People Also Ask boxes, knowledge panels, and image packs are not in the data, so don't ask AI to extract them from it. For Google-specific result data including rich snippets and sitelinks, use =GSEARCH(), which returns 10 organic Google results per call with a real position number, for 3 integration credits.
Generate the questions your article should answer
Formula
=AITEXT("From these search results and snippets, list 8 questions a searcher on this topic would want answered. Return a numbered list.", B2:P2)Generates question-style subheadings from what the ranking pages actually cover. These are content ideas, not scraped People Also Ask data.
Each question is a subheading you can add to your article. Answer them clearly and you cover the topic more completely than the pages you pulled.
Identify the winning content format
Formula
=AITEXT("Look at the titles and snippets of these top results. What content format dominates? Options: listicle, how-to guide, comparison, review, tutorial, tool/calculator. Return the dominant format and a one-sentence explanation.", B2:P2)Tells you what content format wins for this keyword. Match the format to improve your ranking chances.
If the top results are all listicles, write a listicle. If they are all how-to guides, write a how-to guide. Matching the winning format gives you a structural advantage before content quality even enters the picture.
Bulk SERP scraping: 50 keywords in one go
The real power of spreadsheet-based SERP scraping is scale. You are not running one keyword at a time. You are running 50 or 100 at once, then slicing the data with filters and formulas.
Prepare your keyword list
Paste 50 keywords into column A. These can come from brainstorming, competitor research, or tools like Google Search Console.
Run BULKSERP across all keywords
Formula
=BULKSERP(A2)Put in B2 and drag down to B51. Process in batches of 25 if you prefer. Each keyword is one search, so each row costs 1 integration credit.
Caveat
Process large keyword lists in batches of 25 to 50 rows. This avoids timeouts and makes it easier to spot issues early. Run the first batch, check the results, then continue.
Tag each keyword with intent and opportunity
Use the classification formulas from the dashboard section above. Add columns for search intent and opportunity score. You now have a prioritized keyword list based on real SERP data.
Filter and sort
Use Google Sheets filters to find your best opportunities. Filter for "HIGH OPPORTUNITY" and "commercial" intent. Sort by whatever matters most to your strategy. The keywords that survive your filters are the ones worth writing about first.
Those 50 keywords cost 50 integration credits. =SERP() and =BULKSERP() charge 1 credit per search, no matter how many results a search returns. Google Sheets also applies its own daily limit on outbound requests.
Ahrefs charges $129/month and Semrush $117/month for a capped list of tracked keywords. With SheetMagic, the data lives in your spreadsheet where you already do your analysis.
| Tool | Monthly cost | Keywords/month | Data location |
|---|---|---|---|
| Ahrefs Lite | $129 | 750 tracked | Separate dashboard |
| Semrush SEO | $117 | 500 tracked | Separate dashboard |
| SheetMagic Solo | $20 | No tracked-keyword cap | Your Google Sheet |
Scrape titles and descriptions for SEO audits
SERP scraping is not just about keyword research. You can also audit your own pages by pulling their metadata and comparing it against competitors.
Formula
=GETMETATITLE(A2)Extracts the title tag from your page URL. Check for length, keyword placement, and click-worthiness.
Formula
=GETMETADESCRIPTION(A2)Extracts the meta description. Verify it is under 155 characters and includes your target keyword.
Formula
=GETH1(A2)Extracts the H1 heading. Make sure every page has one and it aligns with the title tag.
Combine these with SERP data to see how your pages stack up against competitors:
Formula
=AITEXT("Compare my page title with the top 3 competitor titles for this keyword. My title: "&B2&". Competitor titles: "&C2&", "&C3&", "&C4&". Is mine more or less compelling? Suggest an improved version under 60 characters.", "")AI-powered title tag comparison. Generates a better title based on what competitors use.
For a complete walkthrough of SEO audits with SheetMagic, see the full SEO guide. For extracting headings and content from competitor pages, check the content extraction guide.
Ethical scraping and rate limits
A quick note on being a good citizen. A few things to keep in mind:
Respect robots.txt. Some sites block automated scraping. If a formula returns empty results, the site may restrict access. Move on to another source.
Cache your results. SheetMagic caches scraping results for 30 minutes. If you tweak your AI processing formulas, the cached data gets reused, so paid scrapes like =VISIT() don't get charged twice.
Use scraping for research, not copying. Pull titles, URLs, and snippets to inform your strategy. Do not scrape full articles and republish them. Use =VISIT() for analysis and summarization, not duplication.
Start small. Run 10 keywords first. Check the results. Then scale up to 50 or 100. This catches problems early.
Caveat
SERP results change constantly. Data you scrape today may not match tomorrow's results. For time-sensitive research, note the date you pulled the data. Create a new sheet tab each week or month if you want to track changes over time.
Your keyword research dashboard template
Here is the full column layout for your dashboard. Set this up once and reuse it for every keyword research project.
| Column | Formula | What it shows |
|---|---|---|
| A | (your keywords) | Seed keyword list |
| B | =BULKSERP(A2) | Top search results |
| C | =AITEXT("Extract the #1 URL...", B2:P2) | Top-ranking domain |
| D | =AITEXT("Classify intent...", A2) | Search intent |
| E | =AITEXT("Score opportunity...", B2:P2) | Difficulty estimate |
| F | =AITEXT("List 8 questions...", B2:P2) | Questions to answer |
| G | =AITEXT("What format dominates...", B2:P2) | Winning content type |
Seven columns. One formula per column. Drag them all down your keyword list. You have a complete keyword research dashboard that updates live.
What to do with your SERP data
Scraping is step one. Here is how the data feeds into your content workflow:
Prioritize keywords. Filter your dashboard for high-opportunity, commercial-intent keywords. These are the ones most likely to drive traffic and conversions.
Build content briefs. Take the SERP data for your top keyword and run it through the content gap analysis. The gaps become your outline. For a full brief-building workflow, see the SEO guide.
Track competitors. Set up a sheet tab for each month. Re-run your SERP formulas to see who moves up, who drops, and what new content enters the top 10.
Feed into link prospecting. The URLs in your SERP data are potential link targets. Run =VISIT() on them and use =AITEXT() to find contact pages, author bios, and link opportunities. The web scraping guide covers this workflow in detail.
The spreadsheet is your command center. The SERP data is the raw material. And the AI formulas are your analysis engine.
Start scraping SERPs today
Open Google Sheets. Type =SERP("your keyword") in any cell. That is it. You are doing keyword research now.
SERP scraping costs 1 integration credit per search on every plan, including the free tier. No credit card needed. Run your keyword list, build a mini dashboard, and see if this replaces the $99/month tool you are thinking about canceling. The free tier includes 100 integration credits, shared between SERP searches and page scraping formulas like =VISIT() and =GETMETATITLE().
Install SheetMagic free from the Google Workspace Marketplace and pull your first SERP data in the next sixty seconds.
More scraping and SEO guides: Complete web scraping guide | Content extraction | SEO workflows | Getting started
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.