Create a 404 checker with Pipedream and Google Sheets

What we will create

Pipedream offers a flexible approach to setting up your own workflows. This tutorial walks you through setting up a workflow that integrates a Google Sheets document. The workflow includes steps to retrieve URLs from the Google Sheets document, then send them in an API call to the HttpStatus API and stores their response in another tab of the same Google Sheets document. Calling the API requires a NodeJS script, however, it is included in a ready-to-use workflow template.

The workflow will automatically validate a list of URLs from the Google Sheets document using the HttpStatus API and returns the validation output (including status codes, number of redirects, complete redirect chains, and any possible errors) along with the requested URL to another tab in Google Sheets documents.

In Pipedream, you can set a schedule by using a trigger how often a workflow should run. This makes it easy to check a list of URLs at regular intervals (for example, every 1 minute or hour), every day or specific days of a week or month.

For each API call the NodeJS script checks the response for client (4xx) or server (5xx) errors and sends you an email with the status code and the corresponding URL so that immediate action can be taken where necessary.

Prerequisites

1. Make a copy of the Google Sheets document. Make sure you are logged in with your Google account so you can copy the sheet.
2. Create a free Pipedream account so you can start exploring all the features and resources available on the platform.
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 Pipedream.
4. Make a copy of the Pipedream 404 checker workflow template.

By meeting these requirements, you can configure your workflow that integrates with Google Sheets. This tutorial provides you with a start to setting up a workflow with Pipedream; the possibilities are endless.

Configure your workflow in Pipedream

Open the shared template link, and make sure you are logged into Pipedream. Next, select a project or create a new one. Choose a workflow name and create the workflow.

Open the workflow in the Builder and select the trigger as the first step of the workflow. This trigger already contains a daily schedule, but you can customize it to suit your needs. Click 'Save and continue' and then click the 'Generate sample event' button. Select the sample event from the dropdown menu and click on 'Continue'. Then click 'Continue' again to open the next step of the workflow.

In step 2, you need to connect from Pipedream to the Google Sheets document you copied earlier. When the connection between Google Sheets and Pipedream is established, select the name of the copied Google Sheets document. Set the configuration of this step like you see in the screenshot below.

Now test step 2 by clicking the 'Test' button and check the output. This output confirms Pipedream can read all URLs in column A.

Step 3 of the workflow contains the NodeJS scripts. You don't need to change anything in this script, but you may do so if you want to set up the workflow differently.

This script runs through all the retrieved URLs from the previous step 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.

The HttpStatus Free Plan is restricted to one request per second. To meet this requirement you have to set a delay of 1 second between each request.

For each API call the NodeJS script also checks the response for client (4xx) or server (5xx) errors, and sends you an email with the status code and the corresponding URL.

Before you can test this step, you will need to add the API access key as an environment variable to the project.

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.

This key must be entered as an environment variable in order to get access to the API. Open your project and click on 'Variables' in the project's sidebar menu. Click the button 'New Variable' to add an enviroment variable. Set the configuration of this variable like you see in the screenshot below, and click 'Create'.

To return to the workflow, click on the 'Resources' link in the sidebar. Then, test the script's ability to read the API key and retrieve successful responses from the API by clicking the 'Test' button for step 3. This output confirms that Pipedream has received responses from the API.

Click 'Continue' to enter the next and final step of this workflow. In this step, you also need to connect Pipedream to the Google Sheets document you copied earlier. When the connection between Google Sheets and Pipedream is established, select the name of the copied Google Sheets document. Set the configuration of this step like you see in the screenshot below.

Now test this final step by clicking the 'Test' button and check the output. You will see that two updated rows were added to the Status tab of the Google Sheets document.

Check the Google Sheets document to see the results. What we want to see returned here is the URL, the number of redirects, any errors, the status code and a redirect chain of up to 10 redirect hops if there are any redirects.

Now that all workflow steps are performing as expected, the workflow is ready to be deployed. Simply click 'Deploy' to successfully deploy your workflow!

Your workflow is now set up and waiting for events. You can adjust the trigger to start the workflow earlier or more frequently. Additionally, you can add a URL to the Google Sheets document that returns a 404 error to test if you receive an email in your mailbox.

This brings us to the end of this tutorial. I hope this will enable you to create your own workflows in Pipedream, combining the HttpStatus API with other apps.

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 free Pipedream account is limited to 3 workflows and daily quota of 25 credits, which means that users can only perform a certain number of actions on the platform within that time frame. Pipedream charges one credit per 30 seconds of compute time. Most workflow executions use a single credit regardless of the number of steps.