How to Extract URLs from a XML Sitemap? [Using Google Sheets]

In this tutorial, you will learn how to extract URLs from a XML sitemap using a custom function of Google sheets.

Follow these steps to extract URLs of XML sitemap:

Let’s start, first get the URL of XML sitemap from you want to extract URLs. eg: www.example.com/sitemap.xml.
Now you need to create a new Google spreadsheet. Type sheets.new in the URL bar to create a new Google spreadsheet.
Now, go to menu bar and select script editor under the tools option.
Now you will redirect to script page, in this script page, we will create a custom JavaScript function. For this copy below code and paste into your script editor and save it with your desire name.

/**
* Returns URLs in sitemap.xml file
*
* @param {"https://www.google.com/gmail/sitemap.xml"} sitemapUrl REQUIRED The url of the sitemap
* @param {"http://www.sitemaps.org/schemas/sitemap/0.9"} namespace REQUIRED Look at the source of the xml sitemap, look for the xmlns value 
* @return Returns urls <loc> from an xml sitemap
* @customfunction
*/

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 
  }
}

Now formula has been created, continue to next process:

Get back to sheet and type =sitemapurl() which is the function name that we have created in the script editor.
This function requires two parameters, first is sitemap URL (eg: http://www.countingcharacters.com/sitemap.xml) and second is namespace (eg: http://www.sitemaps.org/schemas/sitemap/0.9) of sitemap.
After putting the parameters, press enter to run a function.
Now it’s done, you can see the list of URLs that are present in your sitemap.