Sådan bruger du Google-regneark til at tjekke for brækkede links
Frit oversat, og omskrevet efter artikel skrevet af Adham El Banhawy
Havde selv brug for denne Google Sheet løsning 😉
Ved arbejde kan vi alle godt lide at automatisere opgaver. Hvis det opdages, at der gentages et eller flere trin for en given opgave, bør vi dedikerer indsatsen for at undgå gentagelser.
En af de mere trivielle opgaver, Forfatteren fik på universitet var, en liste over URL’er fra universitetes interne søgemaskine, at finde de links, der enten er brækkede eller omdirigeret til nye links.
Normalt er dette en meget simpel opgave. Alt der skulle gøres var, at klikke på linket i URL-kolonnen og se, om det virkede, omdirigeres eller ikke findes.
Men hvad hvis du får en liste, der er mere end 100 rækker lang? Faktisk rejste en administrator på universitetet kort efter et problem om, at deres samlede liste over søgeord også skulle kontrolleres for ødelagte og omdirigerede links. Og den liste var over 2000 rækker lang.
Her kommer Google Apps Script/funktioner til gavn!
I dette tilfælde bruger vi Googles Apps script-editor til at skrive egen brugerdefinerede google-ark-funktion (som =SUM()) for at lave http-anmodninger til en liste over links fra regnearket, og funktionen returnerer den statuskode, der returneres fra den anmodning.
Det, der er godt ved denne scripting-funktion, er, at den er integreret i alle regneark, og din kode kan skrives i javascript .
Trin 1: Opret/importer regneark i Google Sheets
Den nemme del. Opret et nyt Google-ark, og importer enten din liste over links, eller tilføj dem manuelt i de kolonner, du ønsker.
Tilføj en kolonne, der kontrollerer status for linkene i URL-kolonnen. I dette tilfælde er det kolonnen Status Code.
Trin 2: Skriv en brugerdefineret funktion i Script Editor
Dette er den magiske del. Gå til menuen “Udvidelser” fra værktøjslinjen, og klik på “Apps Script”.
Dette åbner en ny side, hvor du kan skrive din brugerdefinerede funktion. Standardfunktion Navnet er myFunction(). Dette ændres til et mere beskrivende navn getStatusCode().
function getStatusCode(url){
var response = UrlFetchApp.fetch(url);
return response.getResponseCode();
}Formålet med denne funktion er at tage en enkelt URL ind og returnere den HTTP-statuskode, der returneres, når der oprettes forbindelse til den adresse. Derfor gives funktionen en enkelt parameter kaldet url.
Dernæst oprettes en variabel kaldet response og bruger fetch metoden fra UrlFetchApp class til at lave en anmodning om at hente URL’en. Dette vil returnere et Http-svarobjekt. Derefter benyttes getResponseCode()– metoden til at hente svar koden fra Http-headers.
Den talkode, der angiver et godt sundt link, er 200 . Den berygtede 404 betyder, at siden ikke er fundet/fjernet, og 3xx betyder en side omdirigering. Der er mange andre, som du kan finde her .
Trin 3: Test funktionen i regnearket
Nu kan du se din funktion i aktion. I cellen ved siden af den første URL skal du skrive =getStatusCode(a2) og trykke på enter for at udføre funktionen.
Til testformål valgtes 4 forskellige links, som vides at have forskellig statuskode, hvor rækkefølgen er 200, 404, 503 og 301.
Når funktionen bruges til test af de andre links, modtages uventede værdier. 404- og 503-linkene returnerede uventede status, men funktionen gav en ubelejlig #ERROR! men ved at holde musen over, kan vi læse den korrekte kode. Dette sker, fordi fetch()- funktionen kun returnerer vellykkede kald, som grundlæggende er 2xx-koder.
Hvad med det sidste link? Skulle det ikke returnere en 301?? Igen kan vi skyde skylden på fetch()-funktionen, da serveren omdirigerede den til den nye sideplacering, der gav den en 200 -kode til sidst, som forklarer værdien i arkcellen.
Trin 4: Tilbage til koden
Nu når faldgruberne vises i vores kode, skal der foretages nogle justeringer for at fange de uønskede, vi testede. Til at begynde med findes fejlene, som i dette tilfælde er de andre Http-fejlkoder.
Vi ønsker også at stoppe fetch() i at følge omdirigeringer og returnere den oprindelige omdirigerings kode.
For at gøre det skal vi sende en anden parameter til fetch()- metoden for at justere nogle af dens egenskaber.
function getStatusCode(url){
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
var url_trimmed = url.trim();
var response = UrlFetchApp.fetch(url_trimmed, options);
return response.getResponseCode();
}
Vi opretter den anden parameter, kaldet options, som et objekt. Den første egenskab, vi ændrer, er ‘ muteHttpExceptions ‘ til true. Ved at gøre dette stopper vi fetch()– metoden i at vise en fejl, der bryder vores funktionsflow. Som du måske har gættet, er standarden false, og derfor, ændres den til true.
Den anden egenskab, vi ændrer, er ‘ followRedirects ‘. Dette er den egenskab, der fortæller fetch()– metoden, om den skal følge 3xx-redirects til den endelige URL eller ej. Vi indstiller dette til false, så vi ikke lader os narre af de 200, vi fik før.
Du har måske bemærket, at vi også har ændret URL. Som i en kommentar, kan URL’er med indledende eller efterfølgende mellemrum forårsage fejl. For at tage højde for dette tilfælde bruger vi metoden .trim() på url – strengen for at fjerne eventuelle indledende eller efterfølgende mellemrum.
Nu hvor vores options egenskaber er indstillet, sender vi den til fetch()– metoden som en anden parameter og gemmer vores kode.
Når du nu går tilbage til regnearket og tester din funktion igen, skulle du se, at funktionen ikke længere returnerer den grimme #ERROR! besked, og den bliver heller ikke narret af en 301-omdirigering.
Trin 5: Optimer effektiviteten og undgå kvote begrænsninger!
En interessant fejl, der dukkede op:
Service invoked too many times for one day: urlfetch
Dette er Google, der fortæller os, at vores “URL Fetch calls” script har kørt for mange anmodninger på én dag og nået den tilladte kvote, som er 20.000 anmodninger/dag eller 100.000 anmodninger/dag på Google Workspace kontoer.
For at løse dette problem kan vi bruge et CacheService- objekt, som opretter en cache (en lokal lagring), der er knyttet til vores script. Nu omskriver vi scriptet som følger:
function getStatusCode(url) {
var url_trimmed = url.trim();
// Check if script cache has a cached status code for the given url
var cache = CacheService.getScriptCache();
var result = cache.get(url_trimmed);
// If value is not in cache/or cache is expired fetch a new request to the url
if (!result) {
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
var response = UrlFetchApp.fetch(url_trimmed, options);
var responseCode = response.getResponseCode();
// Store the response code for the url in script cache for subsequent retrievals
cache.put(url_trimmed, responseCode, 21600); // cache maximum storage duration is 6 hours
result = responseCode;
}
return result;
}
Hvad er anderledes?
Den første ting, var at initialisere og hente et cache-objekt på linje 4. Denne cache vil have alle url/responseCode-par, vi har gemt fra tidligere kørsler. Hvis det er første gang det kører, vil det bare være et tomt objekt, hvilket er fint.
På linje 5 søger vi i cachen efter den givne url for at se, om vi har anmodet om det før og gemmer det i result. Hvis det er første gang, scriptet ser denne url, vil resultatet være, undefined og så vil scriptet udføre koden inde i if-blokken, hvilket er den samme logik som før, bortset fra en lille ændring.
cache.put(url_trimmed, responseCode, 21600)I stedet for straks at returnere svar koden til arket, gemmer denne linje svar koden med dens tilsvarende url i cache-objektet, vi oprettede tidligere. Den tredje parameter er et tal, der repræsenterer den tid, denne værdi kommer til at vare (i sekunder “21600”). Vi har brugt den maksimale udløbstid her på 6 timer.
Så hvordan løser dette problemet? Når regnearket åbnes første gang en dag, vil det køre alle anmodninger fra bunden for hver url, der er defineret. Men i de efterfølgende 6 timer, uanset hvor mange gange du opdaterer siden eller hvor mange andre brugere, der ser den, vil scriptet hente de cachelagrede resultater i stedet for at anmode om dem igen og igen, hvilket sparer tid og undgår Googles kvotegrænse. Du kan stadig tilføje nye webadresser til arket, og de vil hente nye anmodninger i starten, hvorefter resultaterne vil blive gemt i den samme cache.
Og det er det! Du kan bruge de få linjer med simpel javascript-kode til at udnytte Googles App Scripts og køre det på hundredvis eller tusindvis af webadresser i et regneark uden at skulle klikke på hvert link manuelt.
Der er mange andre metoder og tricks, du kan bruge til at tilpasse denne funktion, men det afhænger helt af, hvilken information du leder efter, og hvad du vil gøre med den.