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.

---

---

More Scripts

• ‘Award-winning’ digital PR headlines with GPT-3
• Append your keywords with all locations, from any country
• Automatically generate title tags & meta descriptions with GPT-3
• Bulk check indexing and coverage reports in Google Sheets