Check Links with Google Spreadsheet

In this tutorial I’ll show you how you can check multiple links with a Google Spreadsheet and a simple Google Apps Script.

You may find this article useful if you have a lot of links and want to find broken ones, or for example, if you are testing some API endpoints with different parameters, headers, or body and want to check http responses.

Spreadsheet + Google Apps Script:

https://docs.google.com/spreadsheets/d/1UyuKCRO8BWcIaluGEYTuUZglBryo0uFBji2a6tbBXCw/copy

Github Page:

https://github.com/Vryntel/Spreadsheet-Url-Tester

Spreadsheet Structure

The spreadsheet is composed by 2 sheet:

• Data
• Settings

The Data sheet use the following columns:

• Column A: URL
• Column B: HTTP request method (Supported HTTP request methods: GET, POST, DELETE, PATCH, PUT)
• Column C: params in JSON format

Example:
URL: https://mywebsite.com
PARAMS: {"param1":"test", "params2":"test2"}
FETCHED URL: https://mywebsite.com?param1=test&param2=test2

• Column D: headers in JSON format
• Column E: Body Type that can be JSON or TEXT
• Column F: body in JSON/TEXT format
• Column G: Response Code returned by the request
• Column H: Response Body returned by the request

In the Settings sheet you can edit some adavnced settings of the HTTP requests:

• validateHttpsCertificates: TRUE/FALSE –> If FALSE the fetch ignores any invalid certificates for HTTPS requests
• followRedirects: TRUE/FALSE –> If FALSE the fetch doesn’t automatically follow HTTP redirects; it returns the original HTTP response

Usage

After making a copy of the spreadsheet, in the top toolbar will appear the option “Url Tester” and then “Test Urls” (if it doesn’t appear try to reload the page).

To run the script you need to click Test Urls option. Only the first time it will ask you to grant permission for the script to be executed.

If you only need to use the spreadsheet to check for broken links, you can use only the column A to insert the links and in the column B you can leave the GET method for each url row. When you run the script, the response code will appear in the column G and if it’s OK it will be printed the code 200, otherwise another code, you can see all possible codes here:

https://developer.mozilla.org/en-US/docs/Web/HTTP/Status

Some notes:

• Google Apps Script has some limitations regarding URL Fetch calls ( at the moment is 20,000 / day). So if you have more than 20,000 URLs to test, consider to split them in more groups and test in multiple days. There are also other limitations regardings headers size, response size ecc. You can find all the limitations here:

https://developers.google.com/apps-script/guides/services/quotas

• You can hide (not delete) some columns that you don’t need, for example columns C, D, E, F.