Skip to content

Instantly share code, notes, and snippets.

@ilyasozkurt
Created Sep 24, 2021
Embed
What would you like to do?
//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);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment