Free SEO Crawler in Google Sheets with IMPORTXML and Apps Script

You can build a simple, free SEO crawler in Google Sheets from two ingredients: the IMPORTXML function, which uses XPath queries to pull the title, meta description, headings, links or content from a page, and a short Apps Script that checks the HTTP status code and redirects – something IMPORTXML can’t do. This works well for small, server-rendered sites (e.g. WordPress); for large sites and pages generated by JavaScript, Screaming Frog, Sitebulb or your own script will do a better job.

Let me say this up front – if you own a large e-commerce store or content site, what you’ll find here may not be enough. But for those of you with somewhat smaller sites, including company websites, content sites or small online shops, I want to show how you can build yourself a simple crawler. If you put in enough time, you’ll create something along the lines of Sitebulb or Screaming Frog – and completely for free.

Bear in mind, though, that paid tools aren’t paid for no reason. They simply work, whether the site is huge, password-protected or written in Angular. The crawler I’m going to suggest unfortunately won’t be able to examine dynamic pages generated by JavaScript frameworks. To make sensible use of the sheet, you need at least a basic knowledge of how websites are built and how to extract certain information from a site’s source code.

Google Sheets

If you’ve ever had to make tables, calculate something in a spreadsheet or share data with others, you’ve probably heard of Google Sheets. It’s a free app from Google that lets you create, edit and share spreadsheets – all without installing anything on your computer. All you need is a web browser and an internet connection!

The biggest advantage of Google Sheets is that it works in the cloud. What does that mean in practice? You can open your files on any device – a computer, a tablet, even a phone – wherever you are. You don’t have to carry a USB stick around or send files by email. As long as you have internet access, all your spreadsheets are always at hand.

Another thing that really makes Google Sheets stand out is real-time collaboration. Imagine you’re putting together a report with a colleague. With Excel you’d have to save, send it by email, wait for a reply, then download and edit. In Google Sheets? You can work together, at the same moment, in the same file. You enter the data and they add comments. Everything runs smoothly and changes are visible straight away. And best of all – even if you forget to click ‘save’, Google Sheets does it for you automatically!

How do you get started? It couldn’t be simpler! If you have a Google account (e.g. Gmail), you basically already have access to Google Sheets. Just go to sheets.google.com and you’re ready – you can start creating your first spreadsheets.

What will we need?

Let me start by saying I’m no Excel/Sheets wizard. You could even say I’m more intermediate than advanced. But I’ve got the determination to reach my goal, the knowledge and ChatGPT on my side. If there’s something I don’t know or can’t do, ChatGPT will happily suggest a suitable solution. What’s more, I’ll show you how to turn your idea into the right prompt and get the right formula or script in return. And don’t worry – if I managed something, you’ll manage it without any trouble. Don’t be discouraged!

First, you need a Google account with access to Google Drive. It doesn’t matter whether you use a paid Google account or not. A paid account may perform better and let you get larger batches of data faster. That’s what I observed back when I was using Google Workspace (a paid Google account). The confirmed difference concerns Apps Script quotas: a personal account can make 20,000 UrlFetch calls a day, and a Google Workspace account 100,000.

Second, I recommend creating a free (naturally) account with ChatGPT or another AI assistant. That lets you talk to the AI and get a set of formulas tailored to your needs. Don’t worry, you won’t have to do everything yourself from scratch.

The third thing is having the website you want to examine. Sites built on WordPress are ideal. Whatever the platform, for our convenience it’s important to have an XML sitemap, e.g. at https://semurai.pl/sitemap_index.xml. For WordPress sites, plugins such as Yoast SEO, Rank Math or AIOSEO will take care of it.

Now we go to the kitchen, make a coffee, come back to the computer and get going.

Building an SEO crawler in Google Sheets

Time to start building our tool. Let me explain a little about what an SEO crawler actually is and what you can expect from your sheet once you’ve followed all the steps. Before we get to that, though, a few important notes.

What is an SEO crawler?

An SEO crawler is a tool that crawls websites in a similar way to search engines (e.g. Google) to gather information about a site’s structure, content and errors. Such programs help analyse sites for search engine optimisation (SEO). A crawler visits every page of a site, follows links, analyses the content and collects data on titles, descriptions, images, loading speed and many other elements that affect a site’s visibility in search results.

To be fair, the sheet we’re going to build doesn’t ‘walk’ through links on its own like a real crawler. It’s more of a URL list scraper: you supply a list of URLs (e.g. from the sitemap), and the sheet fetches selected data for each of them. For a small site, the result is very similar.

What do apps like Sitebulb or Screaming Frog do?

Tools such as Sitebulb and Screaming Frog are popular SEO crawlers that help with website audits. With them you can:

• Check whether the site has link problems (e.g. links that lead nowhere – so-called 404 errors).
• Identify optimisation gaps, e.g. titles and meta descriptions that are too long or too short, missing H1 headings, unoptimised images.
• Analyse the internal linking structure, which is key to proper indexing by search engines.
• Find problems with page loading speed or files that hamper accessibility.
• Check whether the site has correctly implemented canonical tags and SEO metadata.

These tools give you full control over an SEO audit, so you can see how search engines ‘see’ your site and what you can improve to increase your visibility in Google.

Isn’t Google Search Console enough?

Google Search Console (GSC) is a powerful tool, but it has its limits. GSC provides data on how Google indexes your site, what errors it has and how the site performs in search results (e.g. how many clicks and impressions individual pages get). It’s great for monitoring the health of a site and checking key metrics, but it doesn’t replace an SEO crawler.

The main differences are that GSC:

• Shows data about URLs Google knows and has processed – not about every page you have on your site.
• Doesn’t allow full, detailed technical audits – e.g. it doesn’t display complete lists of missing metadata or internal linking problems that crawlers such as Screaming Frog or Sitebulb can detect.

To sum up, Google Search Console gives you valuable data straight from Google, but SEO crawlers are essential when you want full control over technical optimisation and to catch SEO problems before they affect your rankings. That’s why having an SEO crawler is genuinely useful. Even better if it’s essentially free!

What do we want to achieve?

My main motivation for building the whole SEO crawler concept was to quickly find places where I could add sensible internal links between posts, and to get a complete set of information about the visibility of each post/URL. I assume every post on the site will be visible for many different keywords. On top of that, different tools may provide different data. Combining that data and information gives me the most comprehensive picture of the situation.

Or I could have a tool for pulling content from pages for further processing. The possibilities are practically endless. It all really depends on what matters most to us.

For the purposes of this guide, let’s say we want to create a sheet that tells us:

• What our meta tags look like
• How long our meta tags are
• What content we actually have on the page
• How long the actual content we’re interested in is
• Which h1/h2/h3 headings we use in our text
• How many images there are in the content
• Which keywords a given URL ranks for
• What estimated search volume a given article can generate

And you could probably extract a lot more. Let’s focus on these, though. They’ll be a good basis for further work. The last two points are data from external tools (GSC, Senuto, Ahrefs, etc.) – I’ll come back to them at the end.

Creating the SEO crawler

Enough talk, time to get down to business! Start by opening a blank Google Sheet. If you’re logged in to your Google account, type sheets.new into your browser’s address bar (sheet.new works too). That takes you straight to a new, blank spreadsheet. You can also simply go to your Google Drive and create a new file.

What information about our site can we pull into Google Sheets?

Let’s start by creating the structure of our document. That will make it easier to communicate here, between us, and with ChatGPT too. Remember that you can always add your own solutions. In fact – I hope you’ll develop this sheet to fit exactly your needs and capabilities! I just want to show you the possibilities you have when building your own SEO crawler in Google Sheets!

Here are the elements we’ll want to add to our document:

• URL
• Status code (HTTP status)
• Title
• Title length
• Meta description
• Meta description length
• Post title (H1)
• Page content
• Page content length
• Number of h2 headings
• Number of h3 headings
• Number of other headings

You can of course add more! These, however, will be especially useful to start with. Let’s go through the individual elements we’ll put in our sheet.

Before you start, remember this

When searching for information online, or even when using ChatGPT, you’ll probably get formulas that for some reason don’t work. Everything will look correct, yet you’ll keep getting a formula error message. That’s a sign the wrong character was probably used to separate the formula’s arguments. What’s going on? The argument separator depends on the spreadsheet’s locale (File → Settings → Locale). With English-language locales (e.g. United States or United Kingdom), the separator is a comma ‘,’. With locales such as Poland, where the comma is the decimal separator, arguments are separated with a semicolon ‘;’. All formulas in this guide are written for an English-language locale, i.e. with commas. For example:

This is a correct formula that will work in your Google Sheet with an English-language locale. Note the ‘,’ character after ‘A2’.

=IMPORTXML(A2,"/*")

This is also a correct formula, but for a sheet with a locale that uses semicolons (such as Poland). In an English-language sheet you’ll get a parse error, because the semicolon after ‘A2’ isn’t interpreted as an argument separator there.

=IMPORTXML(A2;"/*")

Watch out for a trap: if your locale needs semicolons, only replace the commas between function arguments. Commas inside an XPath query, e.g. in contains(@class,'entry-content'), are part of the XPath syntax and must stay commas.

I lost a lot of energy figuring that out 🙂 It’s similar with function names: with a non-English interface language, Sheets may display localised function names (e.g. the Polish DŁ instead of LEN). This is controlled by the ‘Always use English function names’ option in the spreadsheet settings. In this guide I use the English names.

The second thing: Google Sheets has limits! Don’t expect to be able to go through a site with many thousands of pages. Going through a site with a few dozen pages, on the other hand, should be no problem. I’ve gathered the specific limitations below.

Limits and limitations of a Google Sheets crawler

Limitation	What it means in practice
IMPORTXML / IMPORTHTML / IMPORTDATA refresh	According to Google’s documentation, these functions update data every hour. After a change on the site, the result in the sheet won’t change immediately.
No status code in IMPORTXML	IMPORTXML only returns data from the document. You can’t check the HTTP status (200, 301, 404) or redirects with it – you need Apps Script for that.
Custom function run time (Apps Script)	A maximum of 30 seconds per execution. A slow site = a #ERROR! in the cell.
Daily UrlFetch call quota	20,000 a day on a personal account, 100,000 on a Google Workspace account.
Cache (CacheService)	A maximum of 6 hours (21,600 s), keys up to 250 characters, values up to 100 KB. Google notes that data may be evicted from the cache earlier.
JavaScript-rendered pages	IMPORTXML and UrlFetchApp fetch the raw HTML from the server without executing JavaScript. You won’t see content loaded by scripts.
Server-side blocking	Requests come from Google’s infrastructure, not from your browser. Some protection layers (e.g. Cloudflare, hosting firewalls) may reject them – in that case, instead of data you’ll get an error fetching the URL.

The last point isn’t a documented rule, just a common situation: if the same formula works on one site but stubbornly returns a fetch error on another, suspect the server’s anti-bot protection first.

Next to each element below you’ll find a number of stars. That’s my rating of how difficult it is to fetch a given element. The more stars, the harder it may be to prepare the data properly, or the more likely you’ll need extra tools such as Apps Script or digging into the HTML code.

URL (★★☆☆☆)

There are two options here. Either we paste in individual URLs of our choice, which is very simple and works 99% of the time, or we can use the XML sitemap. When using the XML sitemap, however, we may run into problems fetching it. The structure of the sitemap can differ slightly depending on the CMS or even the plugin. Yes, the ‘it depends’ game starts already at this stage.

An important point: the sitemap_index.xml file is a sitemap index – it contains the URLs of further sitemaps (e.g. post-sitemap.xml, page-sitemap.xml), not the URLs of pages. So point the sheet to a specific sitemap with posts or pages.

That’s why the simplest thing you can do is copy the URLs from the sitemap or paste in the URLs you want to check, and spare yourself the SEO roulette of ‘will it fetch or won’t it’.

Which queries can pull URLs from a sitemap? The most reliable is one that ignores the XML namespace (sitemaps declare the sitemaps.org namespace, which can make a simple selector return nothing):

=IMPORTXML(A2,"//*[local-name()='loc']")

A shorter version you can try first:

=IMPORTXML(A2,"//loc")

If instead of an XML sitemap you point to a regular HTML page (e.g. a page listing posts or an HTML sitemap), you’ll get the link URLs with the @href attribute:

=IMPORTXML(A2,"//a/@href")

Note: a common mistake is writing //a/href (without the @) – that formula returns nothing, because href is an attribute, not an element. And the table you see when you open an XML sitemap in a browser is just a visualisation applied by the plugin (an XSL stylesheet). IMPORTXML fetches the raw XML, so selectors like //td/a usually won’t work on an XML sitemap.

How do I check whether the sheet connects to my sitemap correctly?

If you take on the challenge of pulling data from the sitemap and it seems like it should work, but something isn’t quite right, try this. Enter this formula in any cell:

=IMPORTXML(A2,"/*")

You should get a dump of the whole file, similar to this:

That’s a clear sign the connection to your file is set up correctly, but something is wrong with the selectors you’ve passed. Sometimes the easiest thing is to paste in the URLs you’re interested in by hand – it’ll save you time and nerves.

Response status code (★★★★☆)

Want to check whether the URL you’re connecting to works at all? Or maybe it has a redirect set up? You can get this information in various forms and with varying degrees of complexity. This time, though, I’ll focus on giving you a simple piece of information: the status code. IMPORTXML can’t do this, so we have to use something called Apps Script – that is, write a script in JavaScript that performs a specific action.

To use Apps Script, choose the Extensions menu in your sheet, and then Apps Script.

A new tab with the editor will open. I won’t explain how to use it in an advanced way right now. Just take the code below and paste it into the editor.

Here’s the code you need to paste into the editor (updated version – it handles empty cells, connection errors and different letter case in the Location header):

function getStatusCode(url) {
  if (!url) return "";
  const urlTrimmed = String(url).trim();
  const cache = CacheService.getScriptCache();
  // a cache key can be max. 250 characters long
  const key = urlTrimmed.length <= 240 ? "s_" + urlTrimmed : null;
  let result = key ? cache.get(key) : null;

  if (!result) {
    try {
      const response = UrlFetchApp.fetch(urlTrimmed, {
        muteHttpExceptions: true, // don't stop on 404, 500 etc.
        followRedirects: false    // show 301/302 instead of the target page's code
      });
      result = String(response.getResponseCode());
      if (key) cache.put(key, result, 21600); // 6 hours – the maximum for CacheService
    } catch (e) {
      return "Error: " + e.message; // e.g. non-existent domain, timeout
    }
  }
  return Number(result);
}

function getRedirects(url) {
  if (!url) return "";
  const urlTrimmed = String(url).trim();
  const cache = CacheService.getScriptCache();
  const key = urlTrimmed.length <= 240 ? "r_" + urlTrimmed : null;
  let result = key ? cache.get(key) : null;

  if (!result) {
    try {
      const response = UrlFetchApp.fetch(urlTrimmed, {
        followRedirects: false,
        muteHttpExceptions: true
      });
      const headers = response.getHeaders();
      const location = headers["Location"] || headers["location"];
      result = location ? String(location) : "no redirect";
      if (key) cache.put(key, result, 21600);
    } catch (e) {
      return "Error: " + e.message;
    }
  }
  return result;
}

Once you’ve pasted the code, press CTRL+S in the editor or click the save icon (the floppy disk). You don’t need to click Run. Instead, go back to the sheet and, in the column where you want the results to appear, type:

=getStatusCode(URL_CELL)

Press Enter and wait a moment. The script will display the status code of the given URL. Feel free to run this script for lots of URLs! Just copy the formula into new cells and point it at further URLs. It’s the easiest way to get the status of your pages.

The same script contains a second function – getRedirects. If a URL returns 301 or 302, it shows you where it redirects to (the value of the Location header):

=getRedirects(URL_CELL)

Remember that this is only the first hop of the redirect, not necessarily the final URL. If you get another URL that redirects, you have a redirect chain – it’s worth shortening. The server may also return a relative URL (e.g. /new-url/).

Two things worth knowing. First, the result is stored in the cache for up to 6 hours, and a custom function only recalculates when the value of the cell it refers to changes – after you fix a redirect on the site, the result in the sheet may not change straight away. Second, each cell is a separate request to the server, so with hundreds of URLs it’s easy to hit the time limit or the server’s own limits (code 429). If you have thousands of URLs to check, you’ll be better off with bulk checking status codes with a Python script.

For those who don’t know what this might be useful for or what header statuses are:

Page header statuses (HTTP status codes) are response codes returned by the server in reply to a request from a web browser. They tell you whether the request succeeded or ran into a problem. Below are the most common status codes:

1. 200 OK – The request was processed successfully and the server returns the expected resource (e.g. an HTML page).
2. 301 Moved Permanently – The resource has been permanently moved to a different URL. The browser automatically goes to the new address.
3. 302 Found – A temporary redirect to a different URL. The resource may be available at the old address again in the future.
4. 404 Not Found – The server didn’t find the resource at the given URL. The page doesn’t exist or has been deleted.
5. 410 Gone – The resource has been permanently removed and will no longer be available at this address.
6. 503 Service Unavailable – The server is temporarily unavailable (e.g. due to overload or maintenance). The problem is usually temporary.
7. 429 Too Many Requests – The server rejected the request because too many requests were sent in a short time (rate limits were exceeded).

These codes help browsers, search engine bots and users understand what’s happening with a given request and how to respond to it.

Title (★☆☆☆☆)

Fetching the text from the <title> tag is dead easy. Practically every page has a title tag, so something is bound to come through. Fetching the title takes one small formula:

=IMPORTXML(CELL_ADDRESS,"//title//text()")

Adding text() to a formula such as =IMPORTXML(D3,"//title//text()") extracts just the text from a specific HTML element – in this case, the <title> element.

Here’s what each part of the formula does:

• IMPORTXML(D3, "//title"): fetches the whole <title> element from the page whose URL is in cell D3.
• //text(): extracts only the text content inside that element, skipping the HTML tags.

Example: if the page has this HTML code:

<title>Example page title</title>

The formula =IMPORTXML(D3,"//title//text()") will return only ‘Example page title’, without the <title> tags.

Note: some themes put more than one <title> tag in the page code (e.g. inside SVG graphics). If the formula returns several rows, limit the query to the head section: =IMPORTXML(CELL_ADDRESS,"//head/title").

Meta description (★☆☆☆☆)

Fetching the meta description is also fairly simple. Here, however, it’s more common for the meta description to be empty or not added at all. Lately I’ve more often come across content placed in the og:description tag than in the meta description.

og:description is a meta tag used in the Open Graph protocol, which is used mainly by social media platforms (e.g. Facebook, LinkedIn) to display a content preview when a page is shared. This tag defines a short description of the page that appears in such previews.

The meta description, on the other hand, is a standard HTML meta tag that provides a short description of the page for search engines (e.g. Google) and may be displayed in search results as the snippet below the link to the page.

The difference:

• og:description is intended for social platforms and used when links are shared.
• meta description is used by search engines – it isn’t a ranking factor, but it affects how your result may look in Google, and therefore your click-through rate.

The two tags can contain different descriptions to suit different contexts.

Even so, pulling the text from the meta description isn’t hard either! All it takes is one simple formula with a simple selector.

=IMPORTXML(CELL_ADDRESS, "//meta[@name='description']/@content")

If you’d like to fetch the content of the og:description tag (or any other tag), use this formula:

=IMPORTXML(CELL_ADDRESS, "//meta[@property='og:description']/@content")

This can come in handy, e.g. when checking how a page looks on social media. Because you can pull almost any tag or content from your site! You just need to know what you want to fetch. Two examples that are very useful in an audit – the canonical URL and meta robots:

=IMPORTXML(CELL_ADDRESS, "//link[@rel='canonical']/@href")

=IMPORTXML(CELL_ADDRESS, "//meta[@name='robots']/@content")

If the tag doesn’t exist, IMPORTXML returns a #N/A error (imported content is empty). To see a readable message in those places, wrap the formula in IFERROR, e.g. =IFERROR(IMPORTXML(CELL_ADDRESS, "//meta[@name='description']/@content"), "MISSING").

Post/page title and other headings (★★☆☆☆)

Fetching the title of a post or page can be either dead easy or a bit demanding. If your site is built properly in line with SEO principles, the title of every page or post will sit in an <h1> tag containing the name of the page (e.g. About us) or of the post. In that case, all you really need is one very simple formula that doesn’t even need explaining:

=IMPORTXML(CELL_ADDRESS, "//h1")

That’s it. Your post title is already loading into the sheet. If your page has several <h1> tags or other headings and you want to fetch them all and display them in a single cell, e.g. separated by commas, use this formula:

=TEXTJOIN(", ", TRUE, IMPORTXML(CELL_ADDRESS, "//h1"))

TEXTJOIN skips empty cells and also copes when IMPORTXML splits a heading across several columns (plain JOIN only accepts a single row or column). Feel free to change the comma to another character. Likewise, you can change //h1 to, for example, //h2, //h3 and so on. Better not to add /text() here – if the heading contains other tags inside it (e.g. <strong> or <span>), //h1/text() will return only fragments of the text or split the heading into pieces.

And how do you count headings? Just count the rows IMPORTXML returns:

=IFERROR(ROWS(IMPORTXML(CELL_ADDRESS, "//h2")), 0)

Replace //h2 with //h3 or with //h4 | //h5 | //h6 to count the other headings. You can count the images in the post content the same way, e.g. =IFERROR(ROWS(IMPORTXML(CELL_ADDRESS, "//div[contains(@class,'entry-content')]//img/@src")), 0) – we count the src attribute, because the <img> element itself has no text content. If the site uses lazy loading for images, the URL is sometimes moved to the data-src attribute – in that case replace @src with @data-src. Why ROWS and not COUNTA? Because when there’s no heading on the page, IMPORTXML returns a #N/A error, and COUNTA would count that error as 1. The IFERROR + ROWS combination correctly shows 0.

And if it turns out your site doesn’t have page titles/names in an h1 tag, you’ll need to find the right selector path and pull the content from the relevant part of the page code. Treat that as a temporary fix, because first and foremost you need to make sure <h1> tags are where they belong! Either way, more on how to find the right piece of code in the next section.

Page content (★★★★☆)

Honestly, the hardest part. There are lots of variables here that can come into play and cause problems. The first problem is trying to connect to a site that serves its content via JavaScript. IMPORTXML only fetches the HTML returned by the server and doesn’t execute scripts, so you can’t save dynamically loaded content to a Google Sheet this way*.

The second issue is fetching only the part of the content we’re actually interested in. Pulling everything between the <body> and </body> tags will leave us with a lot of ‘junk’ in the form of the main menu, sidebars or footer. Of course, if you need that data – go for it! But to fetch only the part of the content we care about, we’ll have to dig around in the code a bit and, unfortunately, there’s no shortcut here. The consolation is that once you get the feel of it and understand what’s going on, the next time will be much easier.

What’s more, in 99% of cases you only need to prepare one selector per site, and it will work on all pages of a given type! So in theory you provide the selector once and can pull content from many pages.

*you can try to get around this with an external service (a proxy) that renders the page and is called by the sheet, but that’s a whole different level of fun.

How do you go about finding the selector?

First and foremost, go to the page whose content you want to fetch. Once there, open the developer tools (usually CTRL+SHIFT+I or F12, or the relevant option in the browser menu).

You can also select the part of the page you’re interested in, right-click and choose ‘Inspect’ from the context menu:

Whichever option you choose, only now does the real fun begin: finding the right place in the page code. The general idea is to find the area (div) that highlights the part of the page you’re interested in, but without any unnecessary elements. The fewer outer divs you include, the more precise the data you’ll get!

Above you can see how different parts of the page were highlighted depending on the element I selected. If I needed to fetch all the content together with the post title, I’d need to pull the content from the <main> tag. If I only need the content, the <div class=”entry-content”…> area would be the one I’m after.

How do you pull content into Google Sheets once you know the area it sits in?

It’s very simple, because we’ve basically done the hardest part. Now it’s just a matter of fine-tuning the selector in the formula. For the example above, this formula will do the job:

=JOIN(" ", IMPORTXML(CELL_ADDRESS, "//div[contains(@class,'entry-content')]//text()"))

It’s built so that all the text fragments from areas with the ‘entry-content’ class are joined into one whole in a single cell. Without JOIN, each text fragment lands in a separate cell:

=IMPORTXML(CELL_ADDRESS, "//div[contains(@class,'entry-content')]//text()")

On newer WordPress sites, the area responsible for displaying the post content will most often be the one with the ‘entry-content’ class. On sites built to a client’s specific needs, these areas may be named completely differently!

This is what the fetched data looks like in Google Sheets:

Data fetched like this is perfect for reviewing content quality, checking its length or processing it further alongside other data! Just remember that a single cell has a text length limit, so very long articles may return an error – in that case, fetch e.g. only the paragraphs (//div[contains(@class,'entry-content')]//p).

How do you fetch the HTML code of a given page?

Google Sheets can do quite a lot, despite its limits. You may not know this, but you can pull, for example, an entire chunk of the HTML code of the page you’re interested in. What does that give you? Well, with the right formulas you can find internal links, outbound links, follow and nofollow links, but also images, alt texts or anything else you’re interested in that sits in the HTML code!

Before you reach for a script: you can extract most of this information with IMPORTXML alone, without fetching the HTML code. A few examples:

=IMPORTXML(CELL_ADDRESS, "//div[contains(@class,'entry-content')]//a/@href")
=IMPORTXML(CELL_ADDRESS, "//a[contains(@rel,'nofollow')]/@href")
=IMPORTXML(CELL_ADDRESS, "//div[contains(@class,'entry-content')]//img/@alt")

The first formula returns all links from the post content, the second – links with the nofollow attribute, the third – the alt texts of images (images without an alt attribute simply won’t appear).

If, however, you need the raw HTML code, you have to use Apps Script. You may also need to ask ChatGPT for help, e.g. with inserting the right div class. As you can see in the formula I created, the syntax is slightly different from the IMPORTXML formulas so far – you give the class in CSS style, with a dot:

=GET_HTML_BY_SELECTOR(CELL_ADDRESS,".entry-content")

This formula calls an Apps Script. Below is its corrected version – the previous one stopped fetching at the first </div> it came across, so if the content contained a nested div (and in WordPress there are usually plenty), the result was cut off. Now the script counts opening and closing divs:

// Custom function: returns the HTML code of the first div with the given class
// Usage in the sheet: =GET_HTML_BY_SELECTOR(A2,".entry-content")
function GET_HTML_BY_SELECTOR(url, selector) {
  if (!url || !selector) return "";
  try {
    const match = String(selector).trim().match(/^\.([\w-]+)$/);
    if (!match) return "Invalid selector – give a class, e.g. .entry-content";
    const className = match[1];

    const response = UrlFetchApp.fetch(String(url).trim(), { muteHttpExceptions: true });
    if (response.getResponseCode() !== 200) {
      return "Status code: " + response.getResponseCode();
    }
    const html = response.getContentText();

    // find the opening <div> with the given class
    const openRe = new RegExp("<div\\b[^>]*class=[\"'][^\"']*\\b" + className + "\\b[^\"']*[\"'][^>]*>", "i");
    const start = html.search(openRe);
    if (start === -1) return "No element found for selector: " + selector;

    // count nested divs until the one we started from is closed
    const tagRe = /<\/?div\b[^>]*>/gi;
    tagRe.lastIndex = start;
    let depth = 0;
    let tag;
    let end = html.length;
    while ((tag = tagRe.exec(html)) !== null) {
      depth += tag[0].charAt(1) === "/" ? -1 : 1;
      if (depth === 0) { end = tagRe.lastIndex; break; }
    }
    // a sheet cell has a text length limit – trim the result
    return html.substring(start, end).substring(0, 50000);
  } catch (e) {
    return "Error: " + e.message;
  }
}

This is still a simplified solution based on regular expressions, not a full HTML parser: it only supports a class selector (with a dot), returns the first matching div and may get confused by unusual code. For a quick analysis of your own site, it’s more than enough.

Counting the length of the fetched content

There’s so much talk about titles and meta descriptions having to fit within specific limits. Or agencies promising content of this or that length. Everything is worth measuring. And nothing could be simpler with Google Sheets and what we’ve done so far.

All we need to do is add this formula in the next column (or wherever we like):

=LEN(CELL_ADDRESS)

If your sheet displays localised function names (like the Polish =DŁ() in the screenshot above), that’s the same formula. And that’s it! We’ve counted the characters, including spaces, in our cell. Bear in mind that Google doesn’t give a fixed character limit for titles – in search results it truncates them where needed, usually to the width of the device’s screen. So the character count is a guide, not a hard limit. If we want to count words, things get a little more complicated, but it generally boils down to this formula:

=IF(TRIM(CELL_ADDRESS)="", 0, COUNTA(SPLIT(TRIM(CELL_ADDRESS), " ")))

TRIM removes extra spaces and SPLIT splits the text on spaces – COUNTA counts the resulting fragments, i.e. the words. There’s nothing stopping you from using both. Google Sheets can take a lot 😉

Finally

Don’t worry – you can generate almost everything you’ll find here with ChatGPT, as long as you ask the right questions! All the Apps Script code was generated by ChatGPT. Unfortunately, I’m not yet fluent enough in programming to write such solutions on my own… but I’m working on it! 🙂 One piece of advice: always test AI-generated code on a few URLs before running it on the whole list.

I’m aware that for larger sites a sheet like this will be completely useless… Although, to be honest, I’ve seen plenty of sites where similar sheets made it possible to check fairly quickly what to link internally and how, or to analyse whether a given piece of content should exist at all… how?

There’s nothing stopping you from importing data from Google Search Console, Ahrefs or Polish tools such as Senuto and Semstorm (think Semrush) into the sheet! On that basis you can build an extensive analysis that gives plenty of valuable information not only to the client but also to us – SEO specialists. Add the option of using APIs, proxies and similar solutions, and suddenly it turns out Google Sheets can make our lives seriously easier!

Just remember that a sheet like this is a supporting tool. If you’d rather have someone review your site comprehensively – technically, in terms of content and linking – see what an SEO audit looks like.

Frequently asked questions

Will IMPORTXML show a page’s status code (200, 301, 404)?

No. IMPORTXML only fetches the content of the document and doesn’t return the HTTP status. When a page returns an error, you’ll simply see a formula error in the cell. You can check the status code and redirect target with the Apps Script functions described above (UrlFetchApp with the followRedirects: false and muteHttpExceptions: true options).

Why does IMPORTXML return #N/A or a ‘could not fetch URL’ error?

The most common reasons: the XPath query found nothing (e.g. the tag isn’t on the page or there’s a mistake in the selector), the content is generated by JavaScript, the server or an anti-bot protection blocks requests from Google’s servers, or the formula uses the wrong argument separator. Start with the =IMPORTXML(A2,"/*") test – if it returns data, the problem lies in the selector.

Comma or semicolon in Google Sheets formulas?

It depends on the spreadsheet’s locale (File → Settings). With English-language locales, arguments are separated with a comma; with locales such as Poland – with a semicolon. Commas inside XPath queries stay unchanged.

How often does the data in the sheet refresh?

According to Google’s documentation, the IMPORTXML, IMPORTHTML and IMPORTDATA functions update data every hour. The Apps Script functions from this guide recalculate when the cell with the URL changes, and additionally keep the result in the cache for up to 6 hours.

How many pages can I check in Google Sheets?

A few dozen without any problem. Hard, documented limits include 20,000 UrlFetch calls a day on a personal account (100,000 on Google Workspace) and 30 seconds per custom function execution, but in practice the sheet’s loading time and the server’s own limits will get in the way sooner. For large sites, you’re better off reaching for Screaming Frog, Sitebulb or a Python script.

What are IMPORTHTML and IMPORTDATA useful for?

IMPORTHTML fetches a whole table or list from an HTML document – you give the URL, the type (“table” or “list”) and the number of the table/list on the page, e.g. =IMPORTHTML(A2,"table",1). IMPORTDATA loads CSV and TSV files, e.g. a data export from another tool published at a URL.

Try a simple sheet in practice!

If you need a ready-made, simple sheet you can use for your own site – I’ve got something for you! Visit the link below, copy the sheet to your Google Drive (File → Make a copy) and use it however you like. The sheet was originally made for my Polish readers, so its labels are in Polish. The Apps Script code is copied together with the sheet, but you can replace it with the corrected versions from this post.

You’ll find the simple content analysis sheet here: Content analysis in Google Sheets by SEMURAI