The Ultimate Google Sheets SEO Audit Tool [FREE]

If you’re spending a lot of time doing SEO Audits for your own site or your clients, you’ll benefit greatly from this free SEO Audit tool that I’ve created on Google Sheets.

There are a lot of great sites out there that can help you perform SEO Audits but if you don’t have a big budget or you’re just starting out, you wouldn’t want to pay for these expensive tools. You can speed up your SEO Audits using this free template.

This tool will let you audit the following things for multiple URLs at once:

1. Title Tags
2. Meta Descriptions
3. HTTP Status Codes
4. Image Alt Texts
5. Mobile Friendly Test
6. Lighthouse SEO Score

You can read more about some of these audits in my On-Page SEO Analysis for Beginners post.

Google Sheets SEO Audit Tool setup

Follow these steps to start with your SEO Audit:

Step 1: Make a copy of the Google Sheets

Start by making a copy of the SEO Audit Tool.

Click on Make a copy.

Step 2: Populate URLs

You have two options here. If you want the tool to extract all the URLs from your XML Sitemap. You can just replace the sitemap in the Setup sheet.

If you have a list of URLs that you want to run the audit on, just paste the URLs in the column A of the Test Sheet.

Step 3: Extract URLs from Sitemap (Optional Step)

If you’ve inserted your own list of URLs then you don’t need to perform this step.

After you’ve inserted your sitemap, click on the SEO Audit menu item in the top navigation and click on Setup: Extract URLs from Sitemap.

The first time you try to run the Script, you’ll have to give authorisation. Follow the video below to allow access to the script.

Once you’ve given authorisation, you will have to click on Setup: Extract URLs from Sitemap again to extract all the URLs. The URLs will then appear in the first column of the Test Sheet.

Step 4: Generate API Key

To run the Lighthouse SEO Score and Mobile Friendly Test, you need to generate an API key. It’s free to do so.

Step 4.1: Create a Project in Google Cloud Console

To create a project, go to Google Cloud Console (You might be asked to sign in using your gmail account). Give your project a name and click on Create.

Step 4.2: Create a Project in Google Cloud Console

From the Left Navigation, go to API & Services and click on Credentials.

Step 4.3: Generate an API Key

Click on Create Credentials and then API Key. Your API Key will be generated. Copy the key and paste it in the API Key Section of the Setup Sheet in the SEO Audit Tool.

Step 4.4: Enable PageSpeed Insights API

On the Search Bar on top, type in PageSpeed Insights API, click on the marketplace result and click on Enable.

Step 4.5: Enable Search Console API

On the Search Bar on top, type in Search Console API, click on the marketplace result and click on Enable.

You’re good to go now. The setup is complete and you can start running the tests.

SEO Audit Tests

You can run each of these tests individually and skip the ones that you don’t need. Lets look at each test one at a time:

1. Title Tags Test

To run this test, click on the Extract Title Tags option from the SEO Audit Menu.

After the test is complete, it will show you the Title Tags for each URL and the characters. It’ll highlight all Title Tags that are either below 55 characters or above 65 characters. Try to keep your title tags within this range.

2. Meta Description Test

To run this test, click on the Extract Meta Description option from the SEO Audit Menu.

After the test is complete, it will show you the Meta Descriptions for each URL and the characters. It’ll highlight all Meta Descriptions that are either below 120 characters or above 158 characters. Try to keep your meta descriptions within this range. Missing Meta Descriptions will also be highlighted.

3. HTTP Status Code Test

To run this test, click on the Get Status Codes option from the SEO Audit Menu.

After the test is complete, it will show you the Status Codes for each URL. If you find any 301 or 302 codes, you can run the next test Find Final URL from the SEO Audit Menu to find out which URL the page is being redirected to.

4. Mobile Friendly Test

To run this test, click on the Run Mobile Friendly Test option from the SEO Audit Menu.

This is a very time intensive test, so chances are that the Google Script might time out after few URLs and you’ll see an error like this:

If this happens, don’t worry, just dismiss the notification and start the test again. It’ll start from where it left off.

5. Image Alt Text Check

To run this test, click on the Image Alt Text Test option from the SEO Audit Menu.

After the test is complete, it will show you the number of images found for each URL and how many of those images had missing Alt Text.

If you want to know which images don’t have an alt text, paste the URL in the Img Alt Text Checker sheet and click on the Run button. You’ll get the Source of all images and the Alt Text. You will easily be able to spot the missing ones.

6. SEO Score

To run this test, click on the Get SEO Score option from the SEO Audit Menu.

This test provides you with the Lighthouse SEO Score. This test is again very time intensive, so you might get Script Timed Out notifications. Just dismiss those and start the test again and it’ll run from where it left off in the previous cycle.

If you also want other PageSpeed Scores, you can download my FREE PageSpeed Insights and Lighthouse Score Monitor Tool.

7. Final SEO Audit Report

To get your SEO Audit Report, click on the Get SEO Audit Report Option from the SEO Audit menu item.

To clear the test results and run another test, click on the Clear Test Results and Audit Report option.

If you find this tool helpful, drop a comment in the section below. You can even let me know what other SEO tools would you find helpful.

Feel free to reach out if you have any questions.