What trigger should I use for Bulk Website HTTP Status Code Checker in Google Sheets?

This script is designed for: On Demand. Configure it under Apps Script > Triggers (clock icon).

Google Sheets AutomationDifficulty: Intermediate⚡ On Demand

Bulk Website HTTP Status Code Checker in Google Sheets

Q: What OAuth permissions does Bulk Website HTTP Status Code Checker in Google Sheets require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/script.external_requests.

Loops through hundreds of URLs listed in Column A, performs lightweight HEAD/GET HTTP requests, and writes HTTP status codes (200, 301, 404, 500) into Column B.

Required OAuth Scopes: https://www.googleapis.com/auth/spreadsheets • https://www.googleapis.com/auth/script.external_requests

Code.gs (Apps Script V8 Runtime)

function checkUrlStatusCodes() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Urls');
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return;
  
  const urls = sheet.getRange(2, 1, lastRow - 1, 1).getValues();
  const results = [];
  
  for (let i = 0; i < urls.length; i++) {
    const url = urls[i][0];
    if (!url) {
      results.push(['Empty']);
      continue;
    }
    
    try {
      const res = UrlFetchApp.fetch(url, {
        muteHttpExceptions: true,
        followRedirects: false,
        validateHttpsCertificates: false
      });
      results.push([res.getResponseCode()]);
    } catch (err) {
      results.push(['Error: ' + err.message]);
    }
  }
  
  sheet.getRange(2, 2, results.length, 1).setValues(results);
}

Line-by-Line Code Explanation

Line 6: Reads all URLs in Column A into memory in 1 batch call.

Line 16-20: Executes HTTP request with `followRedirects: false` to detect 301/302 redirects accurately.

Line 27: Writes all HTTP response status codes back to Column B in a single batch write.

Step-by-Step Setup & Deployment

Create a tab named "Urls" with URLs in Column A starting at row 2.
Run `checkUrlStatusCodes` to audit status codes.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Sheets Automation Snippets

Google Sheets