Extract Webpage Headings and Text: Skip 8 Hours of Copying

SheetMagic's page formulas pull the H1, the H2 outline, the full heading structure, the paragraphs or the whole text of any URL into Google Sheets, one formula per column. Point them at 200 URLs and four dragged columns build the content inventory that takes most of a working day by hand.

Say you run content for a company blog with 200 posts and a redesign is coming. Before anyone moves a page, you need every post's H1, H2s, title tag and meta description in one sheet. Opening each post and copying those four things takes about 2.5 minutes a page, which is over 8 hours. This guide shows the formulas, then three workflows that turn the extracted text into a fix list, a content brief and a research summary.

You are copying and pasting from 47 browser tabs. There is a better way.

Content audits, competitive research, link prospecting. They all start the same way. You open a webpage, scan the headings, copy some text into a spreadsheet, open the next tab, and repeat. By tab fifteen your eyes glaze over. By tab forty-seven you have missed things. And your spreadsheet is a mess of inconsistent formatting.

The analysis part is not the problem. Getting the raw text into a spreadsheet is the problem.

SheetMagic gives you formulas that extract headings, paragraphs, and full page content from any URL. The data goes directly into Google Sheets. You write one formula, point it at a URL, and the content lands in your cell. Then you drag the formula down a column of URLs. You can process hundreds of pages in minutes instead of hours.

This guide covers every content extraction formula. It also includes three real workflows you can build today, plus techniques for cleaning and organizing extracted text at scale.

If you are new to SheetMagic, start with the getting started guide to install the add-on and run your first formula. For a broader overview of all scraping tools, see the complete web scraping guide.

---

The content extraction formulas

SheetMagic includes purpose-built formulas for every level of content extraction. From a single H1 tag to the full text of an entire page. Each formula targets a specific piece of content so you pull exactly what you need.

=GETH1() -- The main heading

The H1 is the single most important heading on any page. It tells you (and search engines) what the page is about. Pulling H1s across your own site shows you duplicate topics, missing pages, and keyword overlap.

=GETH2() -- Section headings

H2 headings are the backbone of long-form content. Pulling them gives you an instant outline of any page. You don't even have to read a single paragraph.

=GETHEADINGS() -- Full heading hierarchy

When you need the full picture, =GETHEADINGS() returns every heading from H1 through H6. This is the formula to use for content audits where heading hierarchy and nesting matter.

=GETP() -- Paragraph text

=GETP() extracts the paragraph-level text from a page. This is useful when you need the actual written content for analysis, word counting, or feeding into an AI formula.

=VISIT() -- Full page content

=VISIT() is the most powerful extraction formula. It pulls the full rendered text of a page, including headings, paragraphs, lists, and other text elements. Use this when you need everything. Or when you plan to process the content with =AITEXT() to pull out specific pieces.

=GETSELECTOR() -- Precision extraction

When the other formulas return too much or too little, =GETSELECTOR() lets you target exactly the HTML element you need. You do need to know the CSS selector (right-click, then Inspect in your browser). But the precision is hard to beat.

---

Workflow 1: Content audit, extract every heading from your site

This is the highest-value use case for content extraction. You paste your sitemap URLs into a spreadsheet and extract the heading structure of every page on your site. A few dragged columns give you a complete content inventory that would take a full day to build by hand.

---

Workflow 2: Competitive content research

You want to write a blog post that outranks the current top 10 results. Before writing a single word, you need to understand what the competition covers, what they miss, and how they structure their content.

This entire workflow costs about 21 scraping credits on basic pages (1 search, 10 heading pulls, 10 page scrapes), more if some pages need JavaScript rendering. The alternative is reading ten articles by hand, taking notes, and hoping you catch the patterns. The spreadsheet approach is faster, more organized, and produces a brief you can hand to any writer.

---

Workflow 3: Research compilation

You are writing a report and need to gather information from dozens of sources. Academic papers, industry reports, news articles. They all sit on different websites. And they all need manual copy-paste to get the relevant sections into your working document.

---

Cleaning and formatting extracted text

Raw extracted text is not always clean. You might see extra whitespace, HTML artifacts, or inconsistent formatting. Here are patterns for handling the common issues.

Strip extra whitespace

This is a native Google Sheets formula. No credits needed. Run it on any column of extracted content as a standard cleanup step.

Normalize heading formats

Extract word count from page content

This is great for content audits where you want to compare article lengths across your site or against competitors.

Extract specific sections with AI

When =VISIT() returns the full page but you only need one section:

---

Batch processing: extracting from 100+ URLs

When you are working with large URL lists, a few practices keep things running smoothly.

Process in batches of 50-100 rows. Do not write your formula in row 2 and drag it straight down to row 500. Start with rows 2-51. Check that the results look right. Then continue to the next batch. This catches problems early, like a broken URL pattern, a site that blocks scraping, or a formula that returns unexpected data.

Use PAGEDATA for bulk metadata. When you need the title, meta description and H1 for many URLs, =PAGEDATA() is more efficient than running three separate formulas per URL. Give it the whole range once instead of dragging it, because it returns a header row plus one row per URL:

Take advantage of caching. SheetMagic caches the page formulas (=GETH1(), =GETH2(), =GETHEADINGS(), =GETP(), =PAGEDATA(), =GETSELECTOR() and the meta formulas) per user for 6 hours. Reopening the sheet or a recalculation with the same URL inside that window reads the cache and costs nothing. And because your =AITEXT() columns point at the cells that hold the extracted text, you can rewrite the AI instructions as often as you like without scraping again.

Handle errors gracefully. Some URLs will fail. Pages behind login walls, sites that block automated access, or URLs that have moved. SheetMagic writes a readable message into the cell for those, not a spreadsheet error, so filter the column for words like "error" or "unexpected" to find them. =IFERROR() still helps for spreadsheet-level errors such as a timeout:

For more on handling large-scale formula runs, see the batch processing guide.

---

What can you do with the extracted headings and text?

A column of H1s is only useful once it changes a decision. Four ways the rows pay back the setup:

Fix the pages that compete with each other. Sort column B (H1s) and look for near-duplicates. Two posts with the same H1 split clicks for the same search, so you merge or redirect one instead of paying to write a third.

Hand the redesign team a fix list, not a spreadsheet dump. Filter the AI check column for anything that is not OK. Missing H1s, titles over 60 characters and mismatched headings become a ticket list on day one, and the redesign does not carry old mistakes onto new templates.

Find thin posts worth updating first. Pull =GETP() and count words with the native word-count formula from the cleaning section above. Posts far shorter than the ones ranking above them are the cheapest wins: an update costs a fraction of a new article.

Brief a writer from what already ranks. Run Workflow 2 on a target keyword. The writer starts from the gaps in ten competitor outlines, and nobody spends an afternoon reading those pages.

---

Putting it all together

The content extraction formulas are building blocks. On their own, each one pulls a specific piece of data from a URL. Combined with AI formulas and native Sheets functions, they become the foundation for workflows that replace hours of manual research with a few minutes of formula work.

For content audits: Use =GETH1() + =GETH2() + =GETMETATITLE() + =GETMETADESCRIPTION() across your sitemap URLs. Add =AITEXT() for automated quality checks. You get a complete on-page audit in one sheet.

For competitive research: Use =SERP() to find competitors, =GETHEADINGS() to extract their outlines, and =AITEXT() to spot content gaps. Your content brief is built on data, not guesswork.

For research compilation: Use =VISIT() to gather source text, =AITEXT() to extract and structure findings, then synthesize across all sources in a single prompt.

Start with one formula and one URL. See the result. Then drag down.

You can try all of this on the free tier. No credit card needed. You get 100 scraping credits (a one-time allowance that never expires), enough to extract content from around 100 pages and see how these formulas fit your workflow.

Install SheetMagic free from the Google Workspace Marketplace and extract your first heading in the next sixty seconds.

---

More web scraping guides: Complete scraping guide | Batch processing | Getting started | AI formulas