Google Sheets är ett väldigt kraftfullt verktyg som klarar av mycket mer än att bara räkna siffror och rita upp fina (nåja) grafer. I denna artikel går jag igenom hur du kan extrahera URLer direkt från en XML Sitemap och samtidigt läser ut URLernas sidtitel och statuskod.
Öppna en ny Google Sheet
Innan vi kan sätta igång så kommer vi behöva ett nytt Google Sheet att jobba i, och den absolut enklaste vägen att starta ett nytt arbetsdokument är genom att gå direkt till https://sheets.new – det är ett trevligt litet hack som sparar mig mycket tid under ett år.
Gå sedan upp till menyn för Extensions och välj App Script.
App Script ger dig möjlighet att skriva egen JavaScript som du kan använda direkt i Google Sheets vilket öppnar upp många möjligheter. Det vi kommer att göra är att skapa ett par funktioner som läser in en XML Sitemap för att initialt plocka ut alla URLer som finns i den.
Vanligtvis använder jag verktyg som Screaming Frog för sånt här, men där får jag ut väldigt mycket information, och ibland behöver jag bara plocka ut lite småsaker, och då fungerar Google Sheets alldeles utmärkt. Gratisversionen av Screaming Frog är också rätt begränsad till hur många URLer du kan kolla av, och den begränsningen finns inte i Google Sheets.
Google App Script
Du möts nu av en helt tom funktion, och här ska vi in med en hel del kod. Som alltid så behöver vi sällan uppfinna hjulet igen, utan det finns nästan alltid information tillgänglig på Internet från någon som har skrivit en väl fungerande kod vi kan återanvända.
I mitt fall utgår jag ifrån den kod som Andrew Charlton har skrivit som är något jag ofta återkommer till.
/**
* Get XML sitemap URLs and metadata.
*
* @param {"https://example.com/sitemap.xml"} url - Input the XML sitemap URL.
* @param {"keyword"} filter [Optional] - keyword to filter URLs.
* @customfunction
*/
function sitemap(url, filter) {
// Validate if the URL is provided.
if (!url) return 'You need to enter the URL';
// Fetch the content of the sitemap from the given URL.
const contentText = fetchSitemapContent(url);
// If there was an error fetching the content, return the error message.
if (typeof contentText === "string" && contentText.startsWith("Failed")) {
return contentText;
}
// Parse the fetched XML content.
const document = XmlService.parse(contentText).getRootElement();
// Determine if the XML has a namespace.
const namespace = document.getNamespace();
// Handle extraction based on presence of a namespace and the type of sitemap (index vs regular).
if (!namespace) {
if (document.getName() === "sitemapindex") {
return extractSitemapIndices(document);
} else {
return extractURLs(document, filter);
}
} else {
if (document.getName() === "sitemapindex") {
return extractSitemapIndices(document, namespace);
} else {
return extractURLs(document, namespace, filter);
}
}
}
// Fetch the content of a sitemap from a given URL.
function fetchSitemapContent(url) {
// Fetch the content from the URL.
const response = UrlFetchApp.fetch(url, {
muteHttpExceptions: true,
method: "GET",
followRedirects: true
});
// Return an error message if fetching failed.
if (response.getResponseCode() !== 200) {
return `Failed to fetch sitemap from ${url}. Response code: ${response.getResponseCode()}`;
}
// Return the fetched content.
return response.getContentText();
}
// Extract sitemap URLs from a sitemap index file.
function extractSitemapIndices(document, namespace) {
const sitemaps = namespace ? document.getChildren('sitemap', namespace) : document.getChildren('sitemap');
let results = [['Sitemap Indices']];
sitemaps.forEach(sitemap => {
const loc = namespace ? sitemap.getChild('loc', namespace).getText().trim() : sitemap.getChild('loc').getText().trim();
results.push([loc]);
});
return results;
}
// Extract URLs and their metadata from a regular sitemap.
function extractURLs(document, namespace, filter) {
const urls = namespace ? document.getChildren('url', namespace) : document.getChildren('url');
let results = [['URLs', 'Last Modified', 'Change Frequency', 'Priority']];
urls.forEach(url => {
const loc = namespace ? url.getChild('loc', namespace).getText().trim() : url.getChild('loc').getText().trim();
// If a filter keyword is provided, only include URLs that contain the keyword.
if (!filter || (filter && loc.includes(filter))) {
const lastmod = namespace ? (url.getChild('lastmod', namespace) || {getText: () => ""}).getText().trim() : (url.getChild('lastmod') || {getText: () => ""}).getText().trim();
const changefreq = namespace ? (url.getChild('changefreq', namespace) || {getText: () => ""}).getText().trim() : (url.getChild('changefreq') || {getText: () => ""}).getText().trim();
const priority = namespace ? (url.getChild('priority', namespace) || {getText: () => ""}).getText().trim() : (url.getChild('priority') || {getText: () => ""}).getText().trim();
results.push([loc, lastmod, priority, changefreq]);
}
});
return results;
}
Kopiera hela detta kodblock och lägg in det i Google App Scripts.
Klicka sedan på Spara-ikonen följt av ”Run”, precis till höger om spara-knappen.
I det här läget kommer du med största sannolikhet behöva godkänna att Google för köra App Scripts, men det är bara att klicka sig igenom den processen.
Om du möts av en skärm som säger att ”Google hasn’t verified this app” så kommer du enklast runt det genom att klicka på ”Advanced” och sen på länken längst ner till vänster. I mitt fall står det ”Go to sitemap (unsafe)”. Jag döpte tidigare mitt App Script till just ”sitemap”, så det är därför det namnet står där.
Detta behöver du bara göra en gång, sen kommer dina App Scripts att fungera.
Det vi har gjort nu är att vi har skapat en egen funktion som heter sitemap() som vi kan använda oss av i Google Sheets. Denna funktion behöver en parameter som vi skickar in, och det är länken till den XML Sitemap som vi vill kolla.
Hämta URLer från en XML Sitemap till Google Sheets
Hoppa nu tillbaka till Google Sheets och ställ dig i cellen A1 (uppe till vänster).
Om du börjar skriva =sitemap( så ser du att vi nu har tillgång till den funktion vi precis skapade i App Script.
Fyll i länken till den XML sitemap du vill kolla av inom situationstecken enligt nedan och tryck på Enter.
=sitemap("https://dindoman.se/sitemap.xml")
I mitt fall tittar jag på en Sitemap som i skrivande stund har lite fler URLer än vad jag har på denna domän. Säkerställ att den URL du använder dig av innehåller URLer till exempelvis dina inlägg/artiklar.
Efter det att jag trycker på Enter ovan som kommer den att hämta in denna XML-fil och sedan skriva ut alla URLer som den innehåller, tillsammans med lite övrig information som också finns i scriptet vi la in.
Det enda du behöver göra är att byta ut länken till din XML Sitemap i funktionen som ligger i cell A1 så uppdaterar den ditt sheet automatiskt. Det är något du kan återanvända för flertalet olika sajter, hur många gånger du vill.
Hämta sidans titel från URLen
Kraften i Google Sheets tar inte slut här. Låt oss nu titta på hur vi kan använda en inbyggd funktion i Google Sheets för att hämta ut sidans meta titel, dvs det som i koden finns i taggen <title>.
I cellen E1 har jag skrivit en rubrik bara, så det är tydligt vad det är kolumnen innehåller, men sedan i cell E2 har jag använt mig av funktionen IMPORTXML() som du har tillgång till utan att behöva lägga till något eget.
Den här funktionen importerar data från (bland annat) en URL och med hjälp av XPath kan du sedan extrahera strukturerad data från sidan. Det här också en påminnelse om hur viktigt strukturerad data är på din sida generellt för SEO. Ju bättre struktur du har, desto lättare kommer det vara för Googlebot att förstå vad din sida handlar om.
Nu ska vi ju ”bara” plocka ut sidans titel, och denna typ av data finns ju på alla sidor, vilket gör det enklare.
=IMPORTXML(A2,"//title")
I cell E2 så skriver du koden ovan. Den kommer att titta på URLen som finns i cell A2 (samma rad) och sen med hjälp av XPath plocka ut sidans titel genom att vi specificerat ”//title”.
Andra användbara XPaths du kan använda
Få ut sidans meta description:
//meta[@name='description']/@content
Få ut sidans rel=canonical:
//link[@rel='canonical']/@href
Få ut all typ av Schema Markup på en sida:
//*[@itemtype]/@itemtype
Ta sedan tag i den lilla blå cirklen nere till höger i cell E2 och dra den nedåt för få informationen för alla de andra URLerna.
Precis lika enkelt är det att sedan lägga till en kolumn som räknar längden för varje sidas titel.
=LEN(A2)
Har kan du med fördel använda conditional formatting för att automatiskt markera alla celler som överstiger exempelvis 65 tecken i rött.
Kolla sidans statuskod direkt i Sheets
En annan smidig grej är att du väldigt enkelt kan lägga till en funktion i din App Script som tillåter dig kolla upp en sidas statuskod i en separat kolumn. Du vill säkerställa att alla sidor du har i din Sitemap svarar med statuskod 200.
Om du ser statuskoder i stil med 301 (Redirect) eller 404 (Not Found) så vill du undersöka varför dessa ligger i din Sitemap, för de bör inte finnas med där.
Börja med att lägga till nedan kod i din App Script, under all övrig kod du redan har där.
function getStatusCode(url){
var response = UrlFetchApp.fetch(url);
return response.getResponseCode();
}
Spara och klicka på Run, precis som tidigare.
Gå sedan tillbaka till din Google Sheet och skapa en kolumn för Statuskoder.
I mitt fall ställer jag mig i G2 och fyller i nedan kod och trycker på Enter.
=getStatusCode(A2)
Summering
Vi har nu med hjälp av Google Sheets och App Script hämtat alla URLer från en XML Sitemap för att sedan hämta ut varje individuell sidtitel, titellängd och statuskod för dessa.
Det enda du behöver göra för att få ut informationen från en annan XML Sitemap är att byta ut länken i cell A1 och trycka på Enter. Sen kan du med fördel komplettera med fler kolumner så det passar dina behov och ha ett enkelt och skalbart Sheet som du kan återavända under en lång tid framöver.
Frågor eller funderingar? Tveka inte att lämna en kommentar nedan.
