Code.gs

//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);

}