Check HTTP Status Code Formula in Google Sheets with Apps Script
As brands lose organic ranking, they end up with backlinks pointing to 404 pages. Backlinks are critical to ranking, so when the link is broken, you’re not passing authority to the destination page and, as a result, it can drop in rank (as can you’re entire domain).
Performing frequent backlink audits to ensure older links still point to relevant destination pages is a good practice, especially when you remove old content from your site or migrate from another domain to a new site, and the URL structure changes. Utilizing a tool like Semrush, you can export your backlinks. While they typically have an internal tool to check whether a backlink resolves, it is often out of date.
Get HTTP Status Code Function in Apps Script
Instead, I import the backlinks into a Google Sheet and figured out an easy way to check the status of each backlink and whether it resolves. To do this, I wrote a small function using Google’s Apps Script.
To add it, open Extensions > Apps Script and paste the following code:
function getStatusCode(url) {
const url_trimmed = typeof url === 'string' ? url.trim() : '';
if (!url_trimmed) {
return 'No URL';
}
let cache = CacheService.getScriptCache();
let result = cache.get(url_trimmed);
if (!result) {
const options = {
'muteHttpExceptions': true,
'followRedirects': true // Set to false if you want to detect redirects instead of following them
};
const response = UrlFetchApp.fetch(url_trimmed, options);
const responseCode = response.getResponseCode();
cache.put(url_trimmed, responseCode.toString(), 21600);
result = responseCode.toString();
}
return parseInt(result);
}Save and return to the spreadsheet. Then you can add the function in the cell in B2:
=getStatusCode(A2)This function checks the HTTP response code for a given URL. It first validates that the input is a string and trims any extra whitespace. If no valid URL is provided, it returns No URL. The function then uses the built-in CacheService to store previously fetched status codes for up to six hours (21,600 seconds), avoiding redundant network requests for the same URL.
If the URL isn’t cached, it performs an HTTP request using UrlFetchApp.fetch() with the muteHttpExceptions option enabled, allowing the script to capture status codes even from failed requests, and followRedirects set to true so that it automatically follows any redirects. Once the response is received, it stores the resulting HTTP status code in cache and returns it as an integer.
Adding the Formula To Your Google Sheet
Drag the formula throughout the spreadsheet. You’ll need to give it some time to resolve each link, and the code will populate in the Status field. Now, you can identify every link that goes to a 404, and you can work on redirecting it to a relevant destination.
It’s important to note that this function does not execute instantaneously. Each network call to UrlFetchApp.fetch() requires time to resolve, and when processing multiple URLs, the cumulative delay can be significant. Developers should use caching effectively and, when working with large lists of URLs, consider implementing rate limiting or batching to prevent script timeouts or exceeding execution quotas in Apps Script.
Related Martech Zone Content
