Build your own redirect checker using Google Sheets

What we will create

Google Sheets documents contain hundreds of built-in functions like AVERAGE, SUM, and VLOOKUP. When these aren't enough for your needs, you can use Google Apps Script to write custom functions and then use them in Google Sheets just like a built-in function.

This integration consists of a Google Sheets document. You can copy and use it. The Google Sheets document provides the custom function HTTPSTATUS which you can use to check a URL for status codes and redirect chains. It is not necessary to write your own script, but when you want the use more of the available features of HttpStatus API you can use this script as a starting point and extend it to your needs.

Prerequisites

1. Make a copy of the Google Sheets document (this also include the Google Apps Script project containing the custom function). Make sure you are logged in with your Google account so you can copy the sheet with the accompanying Apps Scripts file.
2. Create a Google Cloud Project: to be able to use Google Apps Script in your Google Sheets document, you will need to create a project on the Google Cloud Platform (GCP). Apps Script projects use Google Cloud Platform to manage authorizations. How this works is explained in this tutorial.
3. Link the GCP project to the Google Apps Scripts project by using the GCP Project number in the Project Settings of the Google Apps Scripts project. This tutorial also explains how this works.
4. 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 your Google Sheets document.

By meeting these prerequisites, you can set up your environment for integrating the HttpStatus web API into your Google Sheets using no-code or low-code solutions provided by tools like Google Sheets.

Configure your project on Google Cloud Platform (GCP)

Configure your project on Google Cloud Platform (GCP) for using the HttpStatus web API with Google Sheets and Google Apps Script.

To get started, open the 'Project Settings' in the Apps Script sidebar. To open up your 'Project Settings' in the Google Apps Script editor, you can follow these steps:

1. Open your copied Google Sheets document
2. Access the Script Editor: click on "Extensions" in the top menu bar and then select "Apps Script." This will open up the script editor in a new tab.



3. Navigate to Project settings: in the script editor window, click on the gear icon (Project Settings) located at the top right corner of the screen. This will open a dropdown menu with various options.



4. Click 'Change Project' under the 'Google Cloud Platform (GCP) Project' section.



5. Click 'here' under step 1. 'Choose or create a new Google Cloud Platform project here'. or click here. If you have never used Google Cloud before, you must click the checkbox to agree to the Google Cloud Platform Terms of Service.



6. Create a new project by clicking the select menu next to the Google Cloud logo



7. Click the button 'New Project' in the modal box or click here.



8. Give your project a name and click the create button.
9. Open your new project by clicking the Google Cloud logo to open the Welcom screen of your project. Click the 'APIs and Services' link to open the APIs overview.



10. Click the link 'OAuth consent screen'



11. For free Gmail accounts, select 'External' under User Type and click the Create button



12. On the Edit app registration screen set the required fields: app name, user support email, and developer contact information. You can put the same email for both email fields.



13. On the next page, you can click "add or remove scopes". You will then see a list of scopes available for your project. There is no need to select scopes, but if you run into problems in the following steps, select the scopes .../auth/userinfo.email and .../auth/userinfo.profile, and click the 'Update' button. Then scroll to the bottom of the 'Scopes' section and click 'Save and Continue'.



14. Next, add the users who should be granted access to your Google Apps Script. Click the 'Add Users' button, and add the mail addresses of the users who should have access to this Google Cloud Project by using the Google Sheets document.



15. After clicking 'Save and Continue', you can return to the main Google Cloud Platform page. Note that at this point, your project is still in 'testing'. This means that you will be limited to the 100 user cap limit. However, as you don't have to submit any verification applications to Google, you should be able to continue developing and testing your Google Apps Script.

Configure your Google Apps Script project

After configuring the security settings, you should find your 'Project Number' in the Google Cloud Platform dashboard:

Copy the 'Project Number' into your Apps Script manager settings from above and click 'Set project'. Your Apps Script project is now a 'Standard' GCP project under the 'testing' mode:

Use your copy of the Google Sheets document

You can now try to run your Apps script. With the Google Apps Script tab still open, click 'Editor' in the left sidebar menu.

Select the 'test' function from the select menu next to the 'Debug' button. Click on the 'Run' button and you should see the following prompt:

Click the 'Review Permissions' button. If the active user account was added when we configured our users earlier, you should see the following screen:

Select the Google Account you added as a test user and click the 'Allow' button.

Select the Google Account you added as a test user and click the 'Allow' button.

Click the 'Continue' link and you are ready to use Google Sheets to check URLs for status codes and redirect chains.

Before you can run any checks, you will need to enter your Nadles API key (X-Billing-Token) in the cell B2 of the 'Settings' tab. Run your checks in the 'Data' tab, add your URLs to cells in column A and add the custom function to the cells in column B. Use the TRANSPOSE function to write the output of the custom function HTTPSTATUS to a row (instead of a column).

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 copy and paste the function to multiple rows at once, the API will respond with the status '429 Too Many Requests'. These requests consume the available API calls, so please be careful.
• It is recommended to copy all cells containing data retrieved from the API and then overwrite them using 'Paste Special' > 'Values Only'. This prevents custom functions from being executed again in an active Google Sheets document or when it is reopened.
• For consumer users, Google imposes a limit of 20,000 calls per day, while Google Workspace accounts are allowed up to 100,000 calls.
• Google Workspace accounts currently do not require you to connect your Google Apps Script to a Google Cloud Platform project. However, this will change later this year.