{"id":143,"date":"2019-05-25T14:18:43","date_gmt":"2019-05-25T14:18:43","guid":{"rendered":"http:\/\/www.countingcharacters.com\/blog\/?p=143"},"modified":"2021-12-19T11:16:27","modified_gmt":"2021-12-19T11:16:27","slug":"extract-xml-sitemap-urls","status":"publish","type":"post","link":"https:\/\/www.countingcharacters.com\/blog\/extract-xml-sitemap-urls\/","title":{"rendered":"How to Extract XML Sitemap URLs in Google Sheets?"},"content":{"rendered":"\n

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:<\/strong><\/p>\n\n\n\n

  1. Let’s start, first get the URL of XML sitemap from you want to extract URLs. eg: www.example.com\/sitemap.xml.
    <\/li>
  2. Now you need to create a new Google spreadsheet. Type sheets.new<\/a> in the URL bar to create a new Google spreadsheet.
    <\/li>
  3. Now, go to menu bar and select script editor under the tools option.

    \"Google<\/figure><\/li>
  4. 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.

    \"Google<\/figure><\/li><\/ol>\n\n\n\n
    \/**\n* Returns URLs in sitemap.xml file\n*\n* @param {\"https:\/\/www.google.com\/gmail\/sitemap.xml\"} sitemapUrl REQUIRED The url of the sitemap\n* @param {\"http:\/\/www.sitemaps.org\/schemas\/sitemap\/0.9\"} namespace REQUIRED Look at the source of the xml sitemap, look for the xmlns value \n* @return Returns urls <loc> from an xml sitemap\n* @customfunction\n*\/\n\nfunction sitemap(sitemapUrl,namespace) {\n  \n  try {\n    var xml = UrlFetchApp.fetch(sitemapUrl).getContentText();\n    var document = XmlService.parse(xml);\n    var root = document.getRootElement()\n    var sitemapNameSpace = XmlService.getNamespace(namespace);\n    \n    var urls = root.getChildren('url', sitemapNameSpace)\n    var locs = []\n    \n    for (var i=0;i <urls.length;i++) {\n      locs.push(urls[i].getChild('loc', sitemapNameSpace).getText()) \n    }\n    \n    return locs  \n  } catch (e) {\n    return e \n  }\n}<\/code><\/pre>\n\n\n\n
    Now formula has been created, continue to next process:<\/strong><\/p>\n\n\n\n
    1. Get back to sheet and type =sitemapurl()<\/strong> which is the function name that we have created in the script editor.
      \"Sitemap<\/figure><\/li>
    2. This function requires two parameters, first is sitemap URL (eg: http:\/\/www.countingcharacters.com\/sitemap.xml<\/strong>) and second is namespace (eg: http:\/\/www.sitemaps.org\/schemas\/sitemap\/0.9<\/strong>) of sitemap.
      \"Google<\/figure><\/li>
    3. After putting the parameters, press enter to run a function.
      <\/li>
    4. Now it’s done, you can see the list of URLs that are present in your sitemap.
      <\/li><\/ol>\n\n\n\n
      \"extract<\/figure>\n\n\n\n
      I hope you understand the XML sitemap URLs extracting process by using the custom function of Google sheets.<\/p>\n\n\n\n
      If you are interested in Google sheets formulas, so you should also read this blog that about IMPORTXML<\/a> function.<\/p>\n\n\n\n
      Still, you have any query, please let me know in the comment section.<\/p>\n\n\n\n
      If you learned something valuable, please share with others. \u2764\ud83d\udc4d<\/p>\n\n\n\n