Checking a URL’s HTTP status code in Google Sheets is straightforward with the help of Google Apps Script. Follow these steps to set it up:
- Open Google Sheets
Start by launching the Google Sheet where you want to implement this functionality.
- Access Apps Script
Go to Extensions > Apps Script from the top menu. This will open a blank script editor.
- Paste the Script
Copy and paste the following code into the editor:
function getStatusCode(url) {
var url_trimmed = url.trim();
if (url_trimmed === '') {
return '';
}
var cache = CacheService.getScriptCache();
var cacheKey = 'status-' + url_trimmed;
var result = cache.get(cacheKey);
if (!result) {
var options = {
'muteHttpExceptions': true,
'followRedirects': false
};
try {
var response = UrlFetchApp.fetch(url_trimmed, options);
var responseCode = response.getResponseCode();
result = responseCode.toString();
cache.put(cacheKey, result, 21600); // Store in cache for 6 hours
} catch (error) {
result = 'Error: Unable to fetch URL';
}
}
return result;
}
- Save and Close
Click File > Save, name the script, and then close the editor.
- Use the Function
Now, return to your Google Sheet and follow these steps:
- Insert a URL: Enter a URL in a cell, e.g., A1 = www.example.com.
- Call the Function: In a new cell, type =getStatusCode(A1) to fetch the status code for the URL in A1.
- View the Result: The cell will display the HTTP status code (e.g., 200 for success, 404 for not found).
Example
- A1: https://k-o.pro
- B1: =getStatusCode(A1)
- Result in B1: 200
This method makes it easy to monitor HTTP status codes directly within Google Sheets, saving time and streamlining workflows.
P.S. Here’s additional code in case the first one doesn’t work for you:
function getStatusCode(url) {
var result = [];
url.toString().trim();
var options = {
'muteHttpExceptions': true,
'followRedirects': false,
};
try {
result.push(UrlFetchApp.fetch(url, options).getResponseCode());
}
catch (error) {
result.push(error.toString());
} finally {
return result;
}
}