Extract Sitemap URLs with Google Sheets

A sitemap is a file that lists all the pages on your website, making it easier for search engines to crawl and index your content. It’s a crucial tool for SEO, ensuring that your site’s pages are discoverable by search engines like Google. However, manually extracting data from a sitemap can be time-consuming and prone to errors.

That’s where our solution comes in: a Google Sheet script and template with a custom formula that automates the process. This tool offers ease of use, automation, and the ability to analyze your sitemap data effortlessly. Let’s dive into how it works!

Understanding the Custom Formula

The custom formula we’ve created, EXTRACTSITEMAP, simplifies the process of extracting URLs from your sitemap. Here’s how it works:

Syntax: =EXTRACTSITEMAP("sitemap_url")

Parameters: sitemap_url – The URL of your sitemap (e.g., https://example.com/sitemap.xml).

What It Returns: The formula returns a list of URLs extracted from your sitemap. The script handles the XML parsing, so you don’t need to worry about the technical details.

Using the Google Sheet Template (For Non-Coders)

Ready to get started? Follow these simple steps:

1. Access the template: Sitemap Extractor Google Sheet template. Make a copy of the template by clicking File > Make a copy.
2. Enter your sitemap URL in the designated cell (e.g., A1).
3. In another cell (e.g., B1), enter the formula: =EXTRACTSITEMAP(A1). The URLs will automatically populate below the formula cell.
4. If you need to extract more URLs, drag the formula down or use ARRAYFORMULA to expand the results.
5. The first time you run the script, you might need to authorize it. Follow the on-screen prompts to grant the necessary permissions.

Implementing the Script (For Users Who Want to Add to Existing Sheets)

If you prefer to add the script to your existing Google Sheet, follow these steps:

1. Open the Script Editor in Google Sheets: Go to Tools > Script editor.
2. Copy and paste the following JavaScript code into the editor:
   

   function EXTRACTSITEMAP(sitemapUrl) {
     try {
       var xml = UrlFetchApp.fetch(sitemapUrl).getContentText();
       var document = XmlService.parse(xml);
       var root = document.getRootElement();
       var sitemapNameSpace = XmlService.getNamespace('http://www.sitemaps.org/schemas/sitemap/0.9');
   
       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;
     }
   }

3. Save the script with a name, for example, “SitemapExtractor”.
4. Use the custom formula in your sheet.
5. Follow the on-screen prompts to authorize the script if needed.
6. If you need to update the script, simply edit the code in the Script Editor and save your changes.

Conclusion

Using the Google Sheet script and template, you can easily extract and analyze sitemap data without the hassle of manual extraction. This automation saves time and reduces errors, making your SEO efforts more efficient.

We encourage you to try this tool and share your feedback with us. Stay tuned for future enhancements and related tools that can further streamline your SEO workflow.

Ready to simplify your SEO? Give it a try and let us know how it goes! Share this blog post and subscribe to our updates for more tips and tools.