How to Check XML Sitemap Errors in Google Sheet & Screaming Frog
- Dheeraj Soni
- 20 mins read
Are you facing an error in your sitemap and want to analyze it?
Then you are at the right place. In this article, I will share some tips to find errors in your sitemap quickly.
XML sitemaps are essential for your rankings and search engines also. So it is crucial to keep them error-free.
By the end of this article, I assure you that you will audit your sitemaps for any 404 or 500 errors.
There are two methods for checking XML sitemap errors.
1. Google sheets method [Free]
2. Screamingfrog [Free up to 500 URLs]
I will explain both methods in this article. But first, we will discuss what sitemaps are and why check sitemap validation.
What is XML sitemap?
An XML sitemap is a file that contains all the essential pages of your website. XML sitemaps help search engines crawl and index pages of your site. It will give a roadmap of your website to search engines. It also notifies search engines whenever you make changes to your existing pages.
There are four types of sitemap
1. Normal Sitemap
2. Image Sitemap
3. Video Sitemap
4. News Sitemap
Why Check XML Sitemap Validation
It is very important to keep your sitemap as validated and as a error free.
Few examples of invalid XML Sitemap includes
1. Check errors in xml tag format
2. Url in the sitemap has two version HTTP and HTTPS
3. Url not in the same host as the sitemap location
I hope now you can understand why check xml sitemap validation.
Let’s jump to the first method.
How to check XML sitemap errors in google sheet
I assure you that you will not find this method everywhere. However, you can check unlimited URLs with this method. The technique is a bit tricky, but if you follow this method step by step with me. Then I assure you that you will execute it without any problem.
The popularity of google Sheets is increasing day by day and the reason is easy to use and customs formulas that help to extract data from the web.
Yes, you can extract web data directly from the google sheet with the help of custom formulas and functions.
And we will do the same thing to check XML sitemap errors.
First of all, we will extract the sitemap URLs in the google sheet with the help of a custom Javascript function.
Then we will find the status code of all the XML sitemap URLs.
So let’s divine it
Step 1 – First of all grab the url of your stiemap.xml file
First of all, you need to find your sitemap URL. You can find your sitemap file by adding the following words in your site URL.
1 Domain.xyz/sitemap.xml
2 Domain.xyz/sitemap_index.xml
3 Domain.xyz/sitemap/
In the case of my website, the default sitemap location is https://thedheerajsoni.com/sitemap.xml redirects to https://thedheerajsoni.com/sitemap_index.xml
Right now, I am taking the example of this website ecomkeeda.com and the sitemap url is https://www.ecomkeeda.com/post-sitemap.xml
I hope till now you have found your sitemap URL and ready to go further.
Step 2 – Open a new google sheet in your browser.
Step 3 – Go to the menu bar and select script editor under the tool section.
Step 4. Now in a new browser window, the script editor opens. Here you will have to paste the following code. Remember not to close this window.
function sitemap(sitemapUrl,namespace) {
try {
var xml = UrlFetchApp.fetch(sitemapUrl).getContentText();
var document = XmlService.parse(xml);
var root = document.getRootElement()
var sitemapNameSpace = XmlService.getNamespace(namespace);
var urls = root.getChildren('url', sitemapNameSpace)
var locs = []
for (var i=0;i <urls.length;i++) {
locs.push(urls[i].getChild('loc', sitemapNameSpace).getText())
}
return locs
} catch (e) {
return e
}
}
Step 5. Now click on SAVE, then click on Run.
(Remember to save first; otherwise, you wouldn’t be able to run the program)
As you can see in the above image script is successfully executes. This means there is no error.
Step 6. Now go back to the spreadsheet tab and enter this formula in any column.
=sitemap(“your sitemap URL”, “Namespace”)
Here,
your sitemap URL” = You sitemap URL
Namespace= www.sitemaps.org/schemas/sitemap/0.9
The namespace is the same for everyone, so nothing to worry about.
In our case
Sitemap Url – https://www.ecomkeeda.com/post-sitemap.xml
Namespace Url – www.sitemaps.org/schemas/sitemap/0.9
So the formula in our case is
=sitemap("https://www.ecomkeeda.com/post-sitemap.xml","http://www.sitemaps.org/schemas/sitemap/0.9")
I hope you understand how to change this formula according to your site. If you face any difficulty, you can ask me in the comments. I am more than happy to answer your questions.
Step 7 – After implementing the formula, hit the enter button.
Boom ,
Now you can see all the sites map URLs list present in the google sheet.
If you don’t want to extract sitemap direct in the google sheet, you can simply copy paste, but It’s an advance skill and you should know about it.
Our half process is completed now we have to check the status code of all these URLs.
For getting the status code, we have to put a different function in the script editor so let’s continue.
Step 8. Now again, open the script editor.
Step 9 – Copy the below code and paste it into the script editor.
function getStatusCode(url){
var response = UrlFetchApp.fetch(url);
return response.getResponseCode();
}
Step 10. Now click on Save and run the script.
As you can see, there is no error and our script is successfully executed.
Step 11. Now go back to the spreadsheet, and in the next column, type in =getStatusCode(a2) and hit enter to execute the function.
Now drag this formula in all columns, and boom, you will get the status code of all your URLs, as you can see in the image below.
You can find any error in any URL if it exists. I hope now you will be able to check all your sitemaps with this fantastic method.
I hope you can also go through this process seamlessly with me. If you still face any errors, then feel free to ask in the comments. I am always here for your help.
Now let’s jump to our second method for checking errors in sitemaps.
How to check XML sitemap errors in Screaming frog
Screaming Frog is a crawler or spider which crawls your website according to you. We can crawl our complete website or any specific URLs with the help of this tool.
We can also crawl our metadata and many other things. It offers us various filters by which we can find errors on our site.
In short, it is our personal crawler and a very fantastic tool.
Let’s see how Screaming Frog can help us in finding errors in our XML sitemap.
In the free version, we can only check 500 URLs. For more URLs, you can go for the paid version. You can download Screaming Frog from here.
How To Check XML Sitemap Errors in Screaming Frog Tool
Step 1. Download and install Screaming frog on your system.
Step 2. Now open the Screaming Frog and go to Configure > Spider and deselect all items under the basic tab. [Only for Paid users]
Step 3. Now select the list mode by clicking on Mode > List.
Step 4. Now go on upload > Download XML sitemap, then a dialogue box opens. Enter your sitemap URL here and click on OK.
Step 5. Now it will show all your URLs from the sitemap. Now again, click OK.
Now crawler will run automatically and crawl your all sitemap URLs.
And It will show the status code of every URL and various other things.
Isn’t it awesome?
I hope now you can perform an audit with both methods without any problem.
Drop your favourite method in the comments.
If you have any questions feel free to ask them in the comments. I am more than happy to answer all your queries.
Occasional emails that will help you deliver value via SEO.
2 thoughts on “How To Check XML Sitemap Errors in Google Sheet & Screaming Frog”
Damn good knowledge..proud to be a co-worker with you and want to take a lot of knowledge from you ASAP. 😊
Thank you so much Sandesh