On-Page SEO Audit in Google Sheets for 100 URLs

A new client hands you a 120-page site and asks what is wrong with it. You need every page title, every meta description, and every H1, with the character counts, before you can say anything useful. Exporting a crawl and pivoting it into something a client can read costs you most of an afternoon.

There is a shorter path. Paste the URL list into column A of a Google Sheet, add five formulas, and let the sheet fill itself while you write the summary.

Here is the formula the whole audit is built on.

What comes back

You can already see two problems in four rows. Row 3 is a bare service name with no location. Row 4 runs far past what Google will show. Now do that for all 120 pages.

Do it for the whole column

Paste your URL list into A2 and down. Put three formulas across row 2, then select them and drag to the last row.

Three formulas, 120 rows, 360 cells that fill themselves. Each cell is one page fetch and one integration credit, so a 120-page audit of all three fields costs about 360 credits. Solo's 10,000 credits a month covers about 27 audits that size.

Two things to know before you drag.

First, these formulas run right in the cell and give you the answer straight away. They are not background jobs, so there is no placeholder to wait on and nothing lands in a separate tab.

Second, they are not frozen. The Keep Formula setting does not apply to the page-scraping formulas, so a recalculation can fetch the pages again. Results are cached for 30 minutes per formula and URL, so a quick re-run costs nothing. Once the columns look right, select B through I, copy, then Edit, Paste special, Values only. Now the audit is data and it will never re-bill.

Flag what is broken

Raw titles are not an audit. What the client wants is a column that says what to fix. That part is free, because it is plain Google Sheets.

What comes back

The same two formulas work on the description in F and G, with 155 and 70 as the cut-offs instead of 60 and 30. Pick your own numbers. The point is that the sheet applies them the same way to every row, which is the part a human audit gets wrong around page 40.

The H1 needs a different test. Google is fine with a page having no H1, but your client's template probably should not skip one, and two H1s on a page usually means a stray heading in a banner.

What comes back

Filter column D or I to "Too long" and you have the fix list. That is the deliverable the client actually reads.

Four fields in one call

When you only need a quick look at a handful of pages, there is a cheaper way to get all of it at once.

What comes back

That is one page fetch per URL instead of three, so it is a third of the credits for the same four fields. It comes with a catch: PAGEDATA takes each URL as its own argument and does not accept a range. =PAGEDATA(A2:A121) will not audit 120 pages, so use it for a batch you can list by hand and use the drag-down columns for the full site.

The result spills across five columns and down one row per URL, plus a header row. Leave that space empty or Sheets shows a #REF! error.

Rewrite the flagged titles

Now the part that used to be the actual afternoon. Every flagged row needs new copy, and there are usually 30 of them.

What comes back

The IF wrapper matters more than it looks. Without it you run 120 AI calls to rewrite 30 titles. With it you run 30.

More prompts for this kind of work are in the AI for SEO guide. The same pattern writes the missing descriptions into column K. Feed it the title rather than the old description, because the pages that need a description usually do not have one to improve.

See what you are up against

A title can pass every character check and still lose. Before you hand the rewrites over, look at what currently ranks for the page's main keyword.

This one behaves differently from everything above. The cell shows ⏳ Scraping... while the job runs, then the results land in a new tab called Google Search Results, and the cell you typed in ends up reading ✅ 10 results → Google Search Results.

What comes back (first columns of the results tab)

More columns follow, including the displayed URL, highlighted snippet words, and sitelinks. The Source column names the cell that asked for each row, so a whole column of GSEARCH formulas collects into one tab and you can still tell the queries apart. Rows arrive in the order the jobs finish, not the order you typed them, so sort by Source before you read it.

Three lookups at 3 credits each is 9 credits. Run it for your 10 priority pages, not all 120.

Turn it into a client summary

The fix list is for you. The client wants one number per problem. Build that on a second tab with native Sheets formulas, which cost nothing to run.

What comes back

That table is the first slide of the onboarding deck, and it took one formula copied across a grid. It is the same reporting loop the rest of the agency workflows are built on. Next month you paste a fresh URL list into the audit tab and the same counts update.

For the handful of pages that look thin, one more formula tells you whether the page has any structure at all.

A service page with an H1 and nothing else is the one your client will ask about first. Now you can point at it. For anything the named formulas do not cover, such as a canonical tag or a schema block, pull it with a CSS selector.

Compared to Screaming Frog

Screaming Frog is the tool most SEO teams reach for here, and it is very good at this. Being fair about it is more useful than pretending otherwise.

Screaming Frog's own pages describe it as "the industry leading website crawler" and say it finds broken links, redirects, duplicate content, and structural problems, with JavaScript rendering and Search Console integrations on the paid licence. Nothing in this post replaces that. If you need to know every 404 and every redirect chain on a site you have never seen, crawl it.

What the sheet gives you is the other half of the job. The crawler tells you the title is 94 characters. It does not write you a better one. Here the flag and the rewrite sit in adjacent columns, the client can open the link without installing anything, and next month you paste a fresh URL list into the same file.

Use both. Crawl with the Frog, audit and fix in the sheet.

Copy this template

Eleven columns. Column A is your URL list. Row 2 holds the formulas, and you drag row 2 down to your last URL.

Download the template CSV. Import it into a blank sheet (File, Import, Upload) and the formulas are ready to drag down.

What to watch for

• PAGEDATA needs separate arguments. =PAGEDATA(A2, A3, A4) works. A range like =PAGEDATA(A2:A121) does not, because the whole range arrives as one value. For a long list, use the drag-down columns instead.
• Nothing here is frozen automatically. Keep Formula does not apply to the page-scraping formulas, so paste your results as values once the audit looks right. Otherwise a recalculation can fetch every page again.
• Some pages cost more than one credit. A page that needs JavaScript rendering costs 2, and a heavily bot-protected page can settle as high as 9. You are charged what the fetch actually cost, not a flat rate.
• Spilling results need room. PAGEDATA and GETHEADINGS spread across columns and down rows. If anything is in the way, Sheets shows #REF! instead of the table.

Run this on your own site first, before a client's. You will find a "Too long" row you have been looking at for two years. When you want the rest of the page, not just its metadata, extract the full content into a cell and ask AITEXT about it, or read the deeper guide to bulk meta extraction.