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-
- Create a new google sheet.
- Enter the sitemap URL. You can find your sitemap at yourwebsite/sitemap.xml
- 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.
- 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']")
- If your sitemap, URL is in C4, then the above formula will get the sitemap URLs in C5 and the below columns.
- Here is a link to the SITEMAP IMPORT XML feature.