Google Sheets IMPORTXML SEO checks come down to one formula: =IMPORTXML(url, xpath) fetches a page and returns whatever the XPath points to — the title, the H1, the meta description, the canonical tag or every URL in a sitemap. Put URLs in column A and formulas beside them, and you have a lightweight on-page audit.
This guide covers the formulas that actually hold up on real sites, the errors you will meet, and how to go one step further: tracking status codes and SSL certificate expiry for a list of domains in the same sheet, using a free CSV API and, when the list grows, Apps Script.
How do I use the IMPORTXML function in Google Sheets?
The syntax is IMPORTXML(url, xpath_query, [locale]). The URL must include the protocol (https://) and can be a cell reference; the XPath query is a string. The full reference is in the Google Docs Editors Help for IMPORTXML. A working set of SEO columns for a URL in A2:
Title: =IMPORTXML(A2, "/html/head/title")
Title length: =LEN(B2)
H1: =TEXTJOIN(" | ", TRUE, IMPORTXML(A2, "//h1"))
Description: =IMPORTXML(A2, "//meta[@name='description']/@content")
Canonical: =IMPORTXML(A2, "//link[@rel='canonical']/@href")
Meta robots: =IMPORTXML(A2, "//meta[@name='robots']/@content")
Hreflang: =TEXTJOIN(", ", TRUE, IMPORTXML(A2, "//link[@rel='alternate']/@hreflang"))If your spreadsheet locale uses a comma as the decimal separator (most of Europe), Sheets expects semicolons between arguments instead of commas. Check File → Settings → Locale if a formula that works elsewhere is rejected.
Details that break most IMPORTXML sheets
- Use
/html/head/title, not//title. The short form also matches<title>elements inside inline SVG icons, so the result spills into several cells and overwrites the rows below or fails with #REF!. - Multiple H1s. IMPORTXML returns one value per match. Wrapping it in
TEXTJOINkeeps the row intact and makes duplicates visible at a glance. - Attribute case. XPath is case-sensitive:
@name='description'will not matchname="Description". An empty cell where you know a description exists usually means this. - JavaScript-rendered content. IMPORTXML reads the HTML as the server sent it and does not run scripts. If a framework injects the title client-side, the sheet shows a blank or a placeholder — which is also a fair preview of what a simple crawler sees.
- Images without alt.
=COUNTA(IMPORTXML(A2, "//img[not(@alt)]/@src"))counts images with no alt attribute at all. An emptyalt=""on a decorative image is correct markup, so this query deliberately skips it.
To flag long titles, use Format → Conditional formatting with a custom formula such as =$C2>70. For what belongs in those tags in the first place, see our guide to title and meta description.
Can Google Sheets import an XML file?
Yes, as long as the file is reachable by URL — IMPORTXML parses XML as well as HTML. The classic SEO use is pulling every URL from a sitemap. A sitemap declares a default namespace, so //loc returns nothing; match on the local name instead:
=IMPORTXML("https://example.com/sitemap.xml", "//*[local-name()='loc']")For a sitemap index, the same formula returns the child sitemap URLs; run it again on each of them. A local XML file on your computer has to be published somewhere first — the import functions only fetch URLs.
Other Google Sheets formulas every SEO should know
| Function | What it pulls | SEO use |
|---|---|---|
IMPORTXML(url, xpath) | Any HTML or XML node | Titles, H1s, canonicals, hreflang, sitemap URLs |
IMPORTHTML(url, "table", n) | The n-th table or "list" on a page | Competitor pricing tables, lists inside articles |
IMPORTDATA(url) | A CSV or TSV file | Exports, reports, API responses in CSV |
IMPORTFEED(url) | RSS or Atom feed | Tracking new posts on a site |
REGEXEXTRACT(text, regex) | Part of a string | Extracting the path or a UTM parameter from a URL |
References: IMPORTHTML and IMPORTDATA. According to Google, IMPORTHTML, IMPORTFEED, IMPORTDATA and IMPORTXML recalculate every hour (IMPORTRANGE every 30 minutes) while the file is open. That is fine for a daily audit and too slow for live monitoring.
One thing matters more than it seems: the requests are made by Google's servers, not your browser. A site with bot protection or geo-restrictions may serve Google a challenge page or an error, and any rate-limited API counts every one of those requests as coming from Google's shared addresses.
Common IMPORTXML errors and what they mean
| Error | Likely cause | What to do |
|---|---|---|
| #N/A “Could not fetch url” | The site did not answer Google, returned an error, or showed a bot challenge | Check the status code and redirects with a header checker |
| #N/A “Imported content is empty” | The XPath matched nothing | Check the path and attribute case against the page source |
| #REF! “Array result was not expanded” | Several values returned, cells below are occupied | Free the space or wrap in TEXTJOIN / INDEX(..., 1) |
| Endless “Loading...” | Too many import formulas at once | Split the list across sheets or move the work to Apps Script |
Monitoring status codes and SSL expiry in Google Sheets
IMPORTXML tells you what is on a page. For “is the site up”, “what status code does it return” and “how many days until the certificate expires”, a ready-made CSV answer is easier to work with. enterno has an open API that needs no key:
https://enterno.io/api/open/<tool>?q=<domain>&format=csvThe response is a two-column CSV, key and value, which IMPORTDATA reads directly. Keyless tools include http-status, headers, ssl, dns, whois, mx, redirects and robots; the full list is served at https://enterno.io/api/open/.
=IMPORTDATA("https://enterno.io/api/open/http-status?q=example.com&format=csv")The sheet expands into rows like these:
key,value
url,https://example.com
code,200
message,OK
final_url,https://example.com/
redirect_count,0
response_time_ms,82
server,cloudflare
content_type,text/htmlcode is the HTTP status, final_url is where the redirects end, response_time_ms is the response time. If a code is unfamiliar, the HTTP status codes reference explains each one.
Pulling a single value with VLOOKUP
For a domain list you want one field per cell. With the domain in A2:
Status code: =VLOOKUP("code", IMPORTDATA("https://enterno.io/api/open/http-status?q="&A2&"&format=csv"), 2, FALSE)
Days left on SSL: =VLOOKUP("certificate.days_left", IMPORTDATA("https://enterno.io/api/open/ssl?q="&A2&"&format=csv"), 2, FALSE)
Cert status: =VLOOKUP("certificate.status", IMPORTDATA("https://enterno.io/api/open/ssl?q="&A2&"&format=csv"), 2, FALSE)
Valid until: =VLOOKUP("certificate.valid_to", IMPORTDATA("https://enterno.io/api/open/ssl?q="&A2&"&format=csv"), 2, FALSE)FILTER on the key column works too; VLOOKUP is just shorter. Add conditional formatting — =$C2<14 to paint certificates with under two weeks left, =$B2<>200 for sites not returning 200. Why the expiry date deserves a column of its own is covered in how to check an SSL certificate.
The rate limit and HTTP 429
The open API limits requests per IP address: 20 per minute for http-status, 30 for ssl, 10 for whois. IMPORTDATA calls it from Google's servers, and those addresses are shared by many spreadsheets. On a sheet with dozens of rows some formulas will get 429 Too Many Requests and show #N/A, even if you personally made only a handful of calls. Every formula is a separate request: three IMPORTDATA columns on twenty domains means sixty requests on each recalculation. Formulas are fine for your own few sites; beyond that, move to Apps Script.
Checking dozens of sites with Apps Script
Apps Script is the JavaScript runtime built into Sheets, opened from Extensions → Apps Script. Unlike formulas, a script controls its own pace, writes results into cells and runs on a schedule. Requests go through UrlFetchApp; CSV is parsed with Utilities.parseCsv from the Utilities class.
// Sheet: domains in column A from row 2; days until certificate expiry go to column B
function checkSsl() {
const sheet = SpreadsheetApp.getActiveSheet();
const last = sheet.getLastRow();
if (last < 2) return;
const domains = sheet.getRange(2, 1, last - 1, 1).getValues();
domains.forEach(function (row, i) {
const domain = String(row[0]).trim();
if (!domain) return;
const url = 'https://enterno.io/api/open/ssl?q=' +
encodeURIComponent(domain) + '&format=csv';
const resp = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
const cell = sheet.getRange(i + 2, 2);
if (resp.getResponseCode() !== 200) {
cell.setValue('HTTP ' + resp.getResponseCode()); // 429 means the rate limit was hit
} else {
const rows = Utilities.parseCsv(resp.getContentText());
const hit = rows.find(function (r) { return r[0] === 'certificate.days_left'; });
cell.setValue(hit ? Number(hit[1]) : 'no data');
}
Utilities.sleep(2500); // pause so one run does not burn the limit at once
});
}
// Run once by hand; after that the check runs on a schedule
function installTrigger() {
ScriptApp.newTrigger('checkSsl').timeBased().everyHours(6).create();
}On the first run Google asks you to authorize access to the spreadsheet and to external URLs. Run installTrigger once and the check repeats every six hours. UrlFetchApp also leaves from Google's addresses, which is why the pause between requests matters. Scripts have their own runtime and daily fetch quotas, listed in the Apps Script quotas page.
Using an API key with /api/v4
With dozens of domains, tie the requests to your own account instead of Google's shared IPs: call /api/v4 with your key in the X-API-Key header. Take the endpoint and its parameters from the API documentation, and keep the key in Script Properties (Project Settings → Script Properties), not in a cell:
const key = PropertiesService.getScriptProperties().getProperty('ENTERNO_API_KEY');
const resp = UrlFetchApp.fetch(v4Url, {
headers: { 'X-API-Key': key },
muteHttpExceptions: true
});Anyone with edit access to the spreadsheet can open its bound script and read those properties, so never share a sheet that holds a key with “Anyone with the link can edit”. And if what you really need is an alert the minute a site goes down rather than a report every few hours, a spreadsheet is the wrong tool — that is what uptime monitoring is for; plans are on the pricing page.
How to verify a row by hand
When a cell looks wrong, check the site directly — you will see the details that do not fit into a cell:
- HTTP header and availability check — status code, redirect chain and headers when IMPORTXML returns #N/A.
- SSL certificate checker — chain, issuer, validity dates and hostname match when the status is not
valid. - API documentation — which tools and fields you can pull into a sheet or script.
If the sheet outgrows itself and you would rather write the checks in code, the next step is website checks in Python.
FAQ
Is structured data good for SEO?
Structured data makes a page eligible for rich results such as product, recipe or breadcrumb snippets and helps search engines understand the content. Google does not describe it as a direct ranking factor. See our guide to structured data for SEO for what to mark up.
Does Google have a free SEO tool?
Yes. Google Search Console is free and shows indexing status, search queries, clicks and crawl errors for sites you verify. Google Sheets with IMPORTXML complements it with on-page data Search Console does not report, such as your titles and H1s side by side.
Why does IMPORTXML return #N/A for a page that opens in my browser?
The request comes from Google's servers, not your browser. The site may block them as bots, show them a challenge page, or treat them differently by location. Another common cause is content added by JavaScript, which IMPORTXML does not execute.
How often does IMPORTXML refresh?
Google states that IMPORTXML, IMPORTHTML, IMPORTDATA and IMPORTFEED recalculate every hour while the file is open. To force a fresh fetch, re-enter the formula, or use an Apps Script that runs on a button or a trigger.
Why do some rows show 429 errors?
The open API is rate-limited per IP address, and IMPORTDATA reaches it from Google's shared addresses. With many formulas the limit runs out. Use fewer formulas, move the checks to Apps Script with pauses, or use an API key.
