Google Sheets Formula to Export URLs from an XML Sitemap
Sitemaps are an easy way for webmasters to inform search engines about pages on their sites that are available for crawling. In its simplest form, a Sitemap is an XML file that lists URLs for a site along with additional metadata about each URL (when it was last updated, how often it usually changes, and how important it is, relative to other URLs in the site) so that search engines can more intelligently crawl the site.
Here are some of the most common ways to find the sitemap on a site:
- Try checking your domain with one of the following:
https://www.commonmind.com/sitemap
https://www.commonmind.com/sitemap.xml
https://www.commonmind.com/sitemap_index.xml
- Another way is to check your Robots file. Check the robots file to find your sitemap.
- If these two methods didn't work, do a google search:
site:commonmind.com filetype:xml inurl:sitemap
Once you've located the Sitemap on your site, add it to this formula:
=IMPORTXML("https://www.commonmind.com/sitemap_index.xml", "//*[local-name() ='url']/*[local-name() ='loc']")
When you hit enter, the formula will pull all URLs located in the sitemap.
