How to Extract URLs from Sitemaps with Google Sheets?

Sachin Seo
1 min readApr 18, 2021

SEO HACK WITH GOOGLE DOC

How to simply get all the URLs of your sitemaps in an excel sheet without any help of a tool.

Now you can extract all the URLs in the sitemap using Google sheets.

But How? 🤔🤔

  1. Open Google Doc
  2. Go-To Tools
  3. Go To Script Editor
  4. Add A script mentioned below

function getSitemap(url) {
var results=[];
if(!url)return;
var sitemap=UrlFetchApp.fetch(url,{muteHttpExceptions:true, method:”GET”, followRedirects:true});
var document = sitemap.getContentText().split(“<url>”);
var docHead=document.splice(0,1);

for(var i=0;i<document.length;i++) results.push(document[i].split(“</loc>”)[0].split(“<loc>”)[1].replace(/&amp;/g,”&”));

return results;
}

5. Run The Script
6. Now go back to the sheet and put the sitemap URL in one tab. eg abc.com/sitemap.xml

7. In another tab use the formula =getsitemap(cell number) It will automatically extract all the URLs of the sitemap. If you find this helpful then kindly share and do like and comment.

Do subscribe to my youtube channel SACHINSEO

Do follow my Instagram accounts for such hacks sachinseo

#searchengineoptimization #seotechniques #seo #seotipsandtricks #seotips #seotips2021 #searchengineoptimizationseo #searchengineoptimizationtips #seotricks #seotricks2021 #seowithexcel #searchengineoptimisationexpert #searchengineoptimisation

--

--

Sachin Seo

A hands-on Digital Marketing professional with more than two years of progressive experience at the forefront of SEO, Ecommerce and technical SEO