Quickly get sitemap URLs into Google Sheets

With this script you can import an XML sitemap into Google Sheets just by specifying the XML sitemap URL. 🚀

Check out the GIF to see it in action 🎥


How to add the Script to Google Sheets

1. Copy the script below:

function sitemap(url) {
    let results = [];
    if (!url) return;
    const sitemap = UrlFetchApp.fetch(url, {
        muteHttpExceptions: true,
        method: "GET",
        followRedirects: true
    });
    const document = sitemap.getContentText().split("<url>");
    const docHead = document.splice(0, 1);
    for (var i = 0; i < document.length; i++) 
      results.push(document[i].split("</loc>")[0].split("<loc>")[1]
    );
   results = results.filter(function (el) {
   return el != null;
   });

   return results;

}

2. Head over to Google Sheets

Or if you’re really smart, create a new sheet by going to: https://sheets.new

Select Script editor from the Tools menu.

Paste the script and save it.


3. Add the formula to any cell in your sheet

=sitemap("https://www.example.com/sitemap.xml")

Replace “https://www.example.com/sitemap.xml” with any XML sitemap URL and it will return a list of URLs from the sitemap.

*Custom functions in Google Sheets have a 30 second timeout. This means, for larger XML sitemaps, it won’t work.


Thanks for stopping by 👋

I’m Andrew Charlton, the Google Sheets nerd behind Keywords in Sheets. 🤓

Questions? Get in touch with me on social or comment below 👇


More Scripts

Submit your response

Your email address will not be published. Required fields are marked *