import sitemap xml to google sheets

How to import XML sitemap into google sheets

Importing sitemap into your google sheets can save a lot of time in terms of copy pasting and processing. Fortunately, in excel you can import your sitemap automatically without any hassles.

Steps to import sitemap into sheets-

  1. Create a new google sheet.
  2. Enter the sitemap URL. You can find your sitemap at yourwebsite/sitemap.xml
  3.  Copy one of the pages, or post sitemap URLs into the excel sheet. For example, https://www.aozata.com/post-sitemap.xml and paste it into a cell.
  4. Paste the following formula into another cell. (this will work if you are using the yoast sitemap).
    =IMPORTXML(C4,"//*[local-name() ='url']/*[local-name() ='loc']")
  5. If your sitemap, URL is in C4, then the above formula will get the sitemap URLs in C5 and the below columns.IMPORT SITEMAP TO SHEETS
  6. Here is a link to the SITEMAP IMPORT XML feature.