I built an extension for Google Sheets which allows you to import your sitemap and sitemap indexes to Google Sheet document directly. Probably SEO executives will love this extension because they are working with sitemaps often.

If you do not want to know technical stuff you can get a copy from this link.

How to Import Sitemap?

Google Sheets Sitemap Import
Google Sheets Sitemap Import

You can see a screenshot on the right that shows you a menu in Google Sheets. When you add this extension to your google sheets workplace, you are going to be able to import sitemaps or sitemap indexes to your sheet.

It’s really simple, you will click the Load Sitemap menu item then you will see a prompt that is asking you an URL for sitemap or sitemap index.

Load sitemap screen for Google Sheets Sitemap Import

Paste Sitemap URL

When you paste the URL you need to click the OK button. Then Google Sheet starts to process your sitemap to append the active sheet.

Loading sitemap process is completed alert

Wait Until Process Complete

You can see an alert that gives you information about the process. You will see this when the process is completed.

Enjoy the results

As you can see all results came from the sitemap file. These results are from my blog’s sitemap.

How to Install Sitemap Import Extension?

When you create a Google Sheet document you need to follow these steps:

Time needed: 1 minute.

How to install Google Sheets Sitemap Import Extension?

  1. Create a document

    Create a document or open your available document.

  2. Find Tools Menu

    Then Tools->Script Editor
    Then you will see a code editor screen.

  3. Paste the code to editor

    You need to copy all code that I wrote below. Then paste it into the editor area in the script editor.
    You can delete myFunction code block.

  4. Apply the changes

    You can save all changes/codes with the save button. Then you’re ready to go!

The Code That You Need

//Create onOpen function that fires automatically when sheet opened.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Seo Toolbox')
    .addItem('Load Sitemap', 'loadSitemap')
    .addToUi();
}

//Create function that fires when menu item clicked.
function loadSitemap() {

  //Create Google Sheet System Instances
  var ui = SpreadsheetApp.getUi(); //for accesing ui, promt and alerts

  //Ask for sitemap url
  var response = ui.prompt('Load Sitemap', 'Enter Sitemap URL:', ui.ButtonSet.OK_CANCEL);

  //If user clicked yes button.
  if (response.getSelectedButton() === ui.Button.OK) {

    //Handle exceptions like wrong url, 404 sitemap file etc.
    try {

      //Create an array for urls to write
      var urls = [];

      //Load xml file with http request
      var xml = loadXML(response.getResponseText());

      //Access xml file's root element
      var root = xml.getRootElement()

      if (root.getName() === 'sitemapindex') {//Check if type sitemap index or url sitemap

        //Get list of sitemap urls
        var sitemaps = root.getChildren();//sitemap

        //for each sitemap URL
        for (i = 0; i < sitemaps.length; i++) {

          //Get child elements of sitemap element
          var sitemap = sitemaps[i].getChildren();

          //For each child element of sitemap element
          for (a = 0; a < sitemap.length; a++) {
            var element = sitemap[a];
            //Find loc element for sitemap URL
            if (element.getName() === 'loc') {
              xml = loadXML(element.getText());
              appendRows(xml.getRootElement().getChildren());
            }
          }
        }

      } else if (root.getName() === 'urlset') {//if sitemap is url sitemap.

        appendRows(root.getChildren());

      }

      ui.alert('Sitemap load completed.');

    } catch (error) {

      //Fire an alert when something went wrong
      ui.alert(error);

    }

  }

}

function loadXML(url) {
  return XmlService.parse(UrlFetchApp.fetch(url).getContentText())
}

function appendRows(items) {

  var urls = [];
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); //to write rows

  for (var i = 0; i < items.length; i++) {
    var children = items[i].getChildren();
    var row = [];
    for (var a = 0; a < children.length; a++) {
      var child = children[a];
      if (child.getChildren().length === 0) {
        row.push(child.getText());
      }
    }
    urls.push(row);
  }

  //write rows to sheet
  sheet.getRange(sheet.getLastRow() + 1, 1, urls.length, urls[0].length).setValues(urls);

}

I hope you enjoy this post! Thanks in advice.

See you in next tutorial!