An Apps Script that Checks Server Header Response Codes

As a hobby affiliate, I have a keen joy for generating a bit of side hustle revenue. There's nothing wrong with that, and boy does it keep my SEO skills sharp.

Dealing with merchants can be tricky at the best of times (unless they're cool!) but something they do a lot is changing product URLs. URL changes are problematic because before you realise you're serving 404s to your audience. And no merchant I've worked with has ever bothered to let me know.

There's no money in a 404 error for an affiliate marketer!

On that note, I've devised a Google Apps script that will quickly check for the status code on a Thirstyaffiliates export. You can have it and play around with it. Hopefully, you'll find some broken or redirecting links that could better convert if your traffic was pointed elsewhere.

function checkURLStatus() {
  // Get the active spreadsheet and sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  
  // Find the header row and column indices
  var headerRow = data[0];
  var urlColIndex = headerRow.indexOf('Destination URL');
  var statusColIndex = headerRow.indexOf('redirect_status');
  var redirectUrlColIndex = headerRow.indexOf('redirect_url');
  
  // If redirect_status column doesn't exist, add it
  if (statusColIndex === -1) {
    statusColIndex = headerRow.length;
    sheet.getRange(1, statusColIndex + 1).setValue('redirect_status');
  }
  
  // If redirect_url column doesn't exist, add it
  if (redirectUrlColIndex === -1) {
    redirectUrlColIndex = statusColIndex + 1;
    sheet.getRange(1, redirectUrlColIndex + 1).setValue('redirect_url');
  }
  
  // Loop through each row starting from row 2 (after headers)
  for (var i = 1; i < data.length; i++) {
    var url = data[i][urlColIndex];
    var statusCode = '';
    var redirectUrl = '';
    
    // Skip empty URLs
    if (!url) {
      statusCode = 'No URL';
    } else {
      try {
        // Make the HTTP request
        var response = UrlFetchApp.fetch(url, {
          muteHttpExceptions: true,
          followRedirects: false
        });
        statusCode = response.getResponseCode();
        
        // Handle redirects
        if (statusCode === 301 || statusCode === 302) {
          redirectUrl = response.getHeaders()['Location'];
          
          // If redirect URL is relative, make it absolute
          if (redirectUrl && redirectUrl.startsWith('/')) {
            var urlObj = URL.parse(url);
            redirectUrl = urlObj.protocol + '//' + urlObj.host + redirectUrl;
          }
        }
      } catch (error) {
        statusCode = 'Error: ' + error.toString();
      }
    }
    
    // Write the status code and redirect URL to the sheet
    sheet.getRange(i + 1, statusColIndex + 1).setValue(statusCode);
    sheet.getRange(i + 1, redirectUrlColIndex + 1).setValue(redirectUrl);
    
    // Add small delay to avoid hitting quotas
    Utilities.sleep(100);
  }
}

// Add menu item to run the script
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('URL Tools')
    .addItem('Check URL Status', 'checkURLStatus')
    .addToUi();
}

// Helper function to parse URLs
function URL(url) {
  var match = url.match(/^(https?:)\/\/(([^:/?#]*)(?::([0-9]+))?)([/]{0,1}[^?#]*)(\?[^#]*|)(#.*|)$/);
  return match && {
    href: url,
    protocol: match[1],
    host: match[2],
    hostname: match[3],
    port: match[4],
    pathname: match[5],
    search: match[6],
    hash: match[7]
  };
}

In the case below we're checking the status of a URL in column B: "Destination URL", and updating the contents of the adjacent cell in column AD: "redirect_status".

Make your affiliate links export from ThirstyAffiliates (or your affiliate plugin of choice) this will work perfectly. Make sure that either, your columns are title "Destination URL" and "redirect_status" or edit the corresponding lines 8 and 9.

Paste in the apps script code and click the save button to save your project to Google Drive.

Now it's time to click run. You'll probably need to authenticate the app to run by following the usual Google authorisation steps. All very easy.

Here's mine running:

That should populate your status column. It'll take some time but rest assured it works.

By the way, the code adds a little UI element for you so you don't need to open the extensions dialogue again:

I think I'll develop this and add a few more tools next. Have fun!