Check status codes and redirects using an Airtable base document

What we will create

Airtable offers a scripting extension which makes it possible to interact with an Airtable base document. This tutorial walks you through setting up an Airtable base document and adding a script to the document using the Airtable scripting extension. The script is able to retrieve URLs from the Airtable base document, then send them in an API call to the HttpStatus API and store their response in another tab of the same Airtable base document.

We will be using JavaScript in this tutorial, but the scripting extension also comes with Airtable-specific classes and methods for accessing and updating data in your base, as well as for user input and output. The script is ready to use out of the box, and provides a starting point for customising the base document and the script.

The script automatically validates a list of URLs from the Airtable base document using the HttpStatus API and returns the validation output (including status codes, number of redirects, complete redirect chains and any errors) along with the requested URL to another tab in the Airtable base document.

Airtable also offers automations to run the script at a scheduled time. This makes it easy to check a list of URLs at regular intervals (e.g. every 1 minute or hour), every day or on specific days of the week or month.

Prerequisites

1. Make a copy of the Airtable base document. Open the base document and click the 'Copy base' link next to the document title. Make sure you are logged in with your Airtable account so you can copy the sheet to your own workspaces.
2. A paid Airtable subscription is required to use Airtable extensions. After creating an Airtable account on the Free plan, your account will automatically be upgraded to a 14-day free trial of the Team plan, which allows you to install the Airtable scripting extension.
3. Subscribe to the HttpStatus Free Plan: you will need to sign up for a Free Plan on httpstatus.io in order to obtain an API key (X-Billing-Token). This key will be used to authenticate your requests when accessing the HttpStatus web API from within Airtable.
4. Copy the Airtable script from the current page (see below) and paste it into the script editor of the Airtable base document.

By meeting these requirements, you can configure your Airtable base document with the scripting extension. This tutorial will show you how to set up a way to check status codes and redirects with Airtable. Combine this with Airtable's automations and the possibilities are endless.

Configure your Airtable base document

Make a copy of the Airtable base document. Open the base document and click the 'Copy base' link next to the document title. Make sure you are logged in with your Airtable account so you can copy the sheet to your own workspaces.

There are three tabs in the base document: URLs, Status and Settings. The URLs tab contains all the request URLs that you wish to validate using the HttpStatus API.

The Status tab collects the output of the API calls and contains four columns for storing the request URL, the number of redirects and error information if they occur. When the script is run, it will automatically create the required URL and status code columns.

To be able to access the API from your Airtable base document, you need to add the API access key to the first cell of the API key column in the Settings tab.

If you have subscribed to the HttpStatus Free Plan, you can find your personal access key (API key / X-Billing-Token) within the Nadles platform.

Now it's time to add the script. To do this, you first need to add the scripting extension by clicking the extension button in the top right of the Airtable base document. This will open the Marketplace for Extensions. Search for 'scripting extension' if it is not listed as featured extension (purple box) and click on the result to install the extension.

Once the extension is installed, you can open a script editor in the Airtable base document.

Copy the script below, and paste it into the editor, then click the 'Run' button.

// Airtable configuration
const urlsTable = base.getTable("URLs");
const statusTable = base.getTable("Status");
const settingsTable = base.getTable("Settings");
const urlField = "URLs";  // Field in URLs table containing request URLs
const delay = 1000;  // 1 second delay to respect rate limit

// API configuration
const apiEndpoint = "https://api.httpstatus.io/v1/status";
const apiKeyColumn = await settingsTable.selectRecordsAsync({fields: ["API key"]});
const apiKey = apiKeyColumn.records[0].name;

// Fetch all records from the URLs table
let urlRecords = await urlsTable.selectRecordsAsync({fields: ['URLs']});
console.log("Fetched URL records:", urlRecords.records.length);

// Function to dynamically add fields to Airtable with a specified type if they don't exist
async function ensureFieldExists(table, fieldName, fieldType) {
    const tableFields = table.fields.map(field => field.name);

    if (!tableFields.includes(fieldName)) {
        // Define options for number fields to satisfy Airtable's schema requirements
        const options = fieldType === "number" ? { precision: 0 } : undefined;

        await table.createFieldAsync(fieldName, fieldType, options);
        console.log(`Created new field: ${fieldName} of type ${fieldType}`);
    }
}

for (let record of urlRecords.records) {
    const requestUrl = record.getCellValue(urlField);
    if (!requestUrl) {
        console.log(`No URL found for record ${record.id}`);
        continue;
    }

    console.log(`Making API request to ${apiEndpoint} with URL: ${requestUrl}`);

    try {
        // Make the API call with Authorization header and JSON body containing the requestURL
        // Add other options to the JSON body as needed. However, requestUrl is required.
        const response = await fetch(apiEndpoint, {
            method: "POST",
            headers: {
                "x-billing-token": apiKey,
                "Content-Type": "application/json"
            },
            body: JSON.stringify({ requestUrl: requestUrl })
        });

        if (!response.ok) {
            throw new Error(`Request failed with status ${
              response.status
            }`);
        }

        const apiResponse = await response.json();  // Parse JSON response

        // Prepare data for updating or creating a new record in the Status table
        const statusRecordData = {
            "URL": requestUrl,
            "# Redirects": apiResponse.response?.numberOfRedirects || 0,
            "Error": apiResponse.response?.chain?.[0]?.errorType || null,
            "Error message": apiResponse.response?.chain?.[0]?.errorMessage || null
        };

        // Loop through the chain array to dynamically add redirect URL and status fields
        const redirects = apiResponse.response?.chain || [];

        for (let i = 0; i < redirects.length; i++) {
            const urlFieldName = `URL ${i + 1}`;
            const statusFieldName = `Status code ${i + 1}`;

            // Ensure the fields exist with correct types: URL fields as type URL, Status codes as type Number
            await ensureFieldExists(statusTable, urlFieldName, "url");
            await ensureFieldExists(statusTable, statusFieldName, "number");

            // Add data to the status record object
            statusRecordData[urlFieldName] = redirects[i].statusCode !== 0? redirects[i].url : null;
            statusRecordData[statusFieldName] = redirects[i].statusCode || null;
        }

        // Update the Status table
        await statusTable.createRecordAsync(statusRecordData);
        console.log(`API response for ${requestUrl} saved successfully`);

    } catch (error) {
        console.error(`Error processing ${requestUrl}: ${error}`);
    }

    // Wait for 1 second before the next API call to avoid rate limits
    await new Promise(resolve => setTimeout(resolve, delay));
}

console.log("All URLs processed");

This script runs through all the URLs retrieved from the URLs tab and sends each URL in an API call to the HttpStatus API. There is a 1 second pause between each request so that the API rate limiter is not triggered.

As soon as the API response is received, the Airtable base document is updated with the results for a request URL.

This brings us to the end of this tutorial. I hope this will enable you to checks status codes and redirects using an Airtable base document.

Things to keep in mind

• The HttpStatus Free Plan is restricted to 500 API calls. Choose one of the other HttpStatus API plans to perform a higher number of API calls.
• The HttpStatus Free Plan is restricted to one request per second. If you do not set a 1-second delay between each API call the API will respond with the status '429 Too Many Requests'. These requests consume the available API calls, so please be careful.
• A paid Airtable subscription is required to use Airtable extensions. After creating an Airtable account on the Free plan, your account will automatically be upgraded to a 14-day free trial of the Team plan, which allows you to install the Airtable Scripting extension.