Website Status Check

nightwolfdev - Jan 1 '21 - - Dev Community

Is your website currently up and running? Wouldn’t it be nice to get notified if your website was down? Using Google Apps Script and Google Sheets, let’s create something that will check a list of your websites daily and email you if any of them appear to be down.

Spreadsheet Setup

Spreadsheet Set Up

  1. Create a new spreadsheet.
  2. Rename the sheet called Sheet1 to Websites.
  3. Create four columns:
    • Name
    • URL
    • Status
    • Last Check
  4. Define the website name and url for each website you’d like to check. The Status and Last Check columns will be populated by the script when it’s finished running.

Script Editor

Script Editor

Let’s start writing some code! Google Sheets has a handy script editor available.

NOTE: The script editor can now be found under Extensions > Apps Script.

  1. Navigate to Tools > Script Editor.
  2. The script editor will include a starting function. You can remove all the code.
  3. Navigate to File > Save. Give the script project a name and select Ok.

Check Website Status

Create a function called checkStatus. This is the function that will get the data from the Websites sheet and make requests to each website, checking their statuses.

function checkStatus() {

}
Enter fullscreen mode Exit fullscreen mode

Let’s continue to add code to the function. Create a variable called sheet. This gets the sheet called Websites and returns a Sheet object, which will provide additional functions we’ll need.

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Websites');
Enter fullscreen mode Exit fullscreen mode

Create a variable called rows. This is an array of the rows and columns of data from the Websites sheet.

var rows = sheet.getDataRange().getValues();
Enter fullscreen mode Exit fullscreen mode

Create a variable called issues. This will keep track of how many issues were found.

var issues = 0;
Enter fullscreen mode Exit fullscreen mode

The first entry in the array of rows and columns is the row of column headings. The column headings aren’t really needed. Let’s remove them from the array using the shift function, which removes the first entry in an array.

rows.shift();
Enter fullscreen mode Exit fullscreen mode

Every time the checkStatus function is run, the prior values and formatting in the Status and Last Check columns should be cleared. Create a range starting at column C row 2 and ending at column D, which basically means go as far as there is data in column D. Clear everything using the clear function.

sheet.getRange('C2:D').clear();
Enter fullscreen mode Exit fullscreen mode

Let’s loop through the rows of data now.

for (var i = 0; i < rows.length; i++) {

}
Enter fullscreen mode Exit fullscreen mode

Create a variable called row for each iteration of the loop. The row is actually an array of columns. The first column starts at 0, so row[0] would be the column called Name, which is the name of the website.

Create a variable called name to store that value. Create a variable called url to store the website’s url, which would be row[1].

Create a variable called status with its value set to a string of OK. Create a variable called color with it’s value set to a hexadecimal color of #bfb. This will become the background color of the cell if the status is OK.

Create a variable called timestamp. This will provide a date and time of when the check was run.

var row = rows[i];
var name = row[0];
var url = row[1];
var status = 'OK';
var color = '#bfb';
var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'M/d/Y h:m a');
Enter fullscreen mode Exit fullscreen mode

If a url exists in the cell, make a request to the url and save the response code.

If the response code is anything other than 200, which means OK, then change the status value from OK to ISSUE. Change the color value from #bfb to #faa, which is red. Increment the issues count by 1.

Update the Status and Last Check columns with the status and timestamp values and set the background color as well.

When using the fetch function of the UrlFetchApp class, there are rate limitations. Google doesn’t like it being called too frequently in a short time frame. Adding a 1 second delay is a recommendation from Google to avoid that limitation.

if (url) {

  var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
  var responseCode = response.getResponseCode();

  // There's an issue if the response code is greater than 200.
  if (responseCode > 200) {
    status = 'ISSUE';
    color = '#faa';
    issues++;
  }

  // Update Status and Last Check columns with results.
  sheet.getRange(i + 2, 3, 1, 2).setValues([[status, timestamp]]).setBackground(color);

  // There are rate limits when using UrlFetch so it's recommended to add a delay in between each request.
  Utilities.sleep(1000);

}
Enter fullscreen mode Exit fullscreen mode

Send Email Notification

Instead of checking the spreadsheet all the time, let’s create a function called notify that will send an email alerting us that there were issues found.

If the email client allows for html emails, the word spreadsheet will be a link back to the spreadsheet. If the email client can’t handle html emails, it will just display plain text.

There is a limit on how many emails can be sent per day.

function notify() {

  var recipient = Session.getEffectiveUser().getEmail();
  var subject = 'Website Status Check: Issues Found';
  var body = 'Check spreadsheet for issues found.';
  var spreadsheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  var html = '<p>Check <a target="_blank" href="' + spreadsheetUrl + '">spreadsheet</a> for issues found.';
  var options = { htmlBody: html };

  if (MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail(recipient, subject, body, options);
  }

}
Enter fullscreen mode Exit fullscreen mode

When the loop is finished, let’s check if the number of issues is greater than 0. If there are issues, call the notify function to send the email.

if (issues > 0) {
  notify();
}
Enter fullscreen mode Exit fullscreen mode

Custom Menu

Instead of running the checkStatus function from the script editor, let’s create a custom menu so we can run it from the spreadsheet’s menu toolbar instead!

The best time to create a custom menu is when the spreadsheet first opens. Use the onOpen trigger, which is executed when the spreadsheet is first opened.

Add a custom menu to the spreadsheet called Manage. Selecting the Manage menu will display a menu option called Check Status. Selecting Check Status will run the function called checkStatus!

function onOpen() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var menuOptions = [{
    name: 'Check Status',
    functionName: 'checkStatus'
  }];

  spreadsheet.addMenu('Manage', menuOptions);

}
Enter fullscreen mode Exit fullscreen mode

Time Based Trigger

Time Based Trigger

Having a custom menu that runs the script is nice, but it’s a manual process. To automate the process, let’s have the script run on a time based trigger!

  1. Within the script editor, navigate to Edit > Current project’s triggers.
  2. Select Add Trigger.
  3. At the “Choose which function to run” field, select checkStatus.
  4. At the “Select event source” field, select Time-driven.
  5. At the “Select type of time based trigger” field, select Day timer.
  6. At the “Select time of day” field, select a time range you’d like to run the script.
  7. At the “Failure notification settings” field, select how often you’d like to be notified if the script failed to run.

Final Code 

// Create custom menu when spreadsheet opens.
function onOpen() {

  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

  var menuOptions = [{
    name: 'Check Status',
    functionName: 'checkStatus'
  }];

  spreadsheet.addMenu('Manage', menuOptions);

}

// Check status of each website in Websites sheet.
function checkStatus() {

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Websites');
  var rows = sheet.getDataRange().getValues();
  var issues = 0;

  // Remove column headings row.
  rows.shift();

  // Clear Status and Last Check columns.
  sheet.getRange('C2:D').clear();

  // Loop through rows in sheet and make a request to website url.
  for (var i = 0; i < rows.length; i++) {

    var row = rows[i];
    var name = row[0];
    var url = row[1];
    var status = 'OK';
    var color = '#bfb';
    var timestamp = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'M/d/Y h:m a');

    if (url) {

      var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
      var responseCode = response.getResponseCode();

      // There's an issue if the response code is greater than 200.
      if (responseCode > 200) {
        status = 'ISSUE';
        color = '#faa';
        issues++;
      }

      // Update Status and Last Check columns with results.
      sheet.getRange(i + 2, 3, 1, 2).setValues([[status, timestamp]]).setBackground(color);

      // There are rate limits when using UrlFetch so it's recommended to add a delay in between each request.
      Utilities.sleep(1000);

    }

  }

  // Notify me if there are issues.
  if (issues > 0) {
    notify();
  }

}

// Send email notification.
function notify() {

  var recipient = Session.getEffectiveUser().getEmail();
  var subject = 'Website Status Check: Issues Found';
  var body = 'Check spreadsheet for issues found.';
  var spreadsheetUrl = SpreadsheetApp.getActiveSpreadsheet().getUrl();
  var html = '<p>Check <a target="_blank" href="' + spreadsheetUrl + '">spreadsheet</a> for issues found.';
  var options = { htmlBody: html };

  if (MailApp.getRemainingDailyQuota() > 0) {
    MailApp.sendEmail(recipient, subject, body, options);
  }

}
Enter fullscreen mode Exit fullscreen mode

Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!

Image
12 Facts About Lawyers For Accidents At Work To Bring You Up To Speed The Water Cooler
Image
Why People Are Talking About Mobility Scooters For Sale Near Me Right Now
Image
15 Twitter Accounts That Are The Best To Find Out More About Asbestos Trust Fund Payouts
Image
10 Meetups On Largest Asbestos Settlement You Should Attend
Image
7 Tips To Make The Most Out Of Your Accident Lawyer
Image
The 10 Most Scariest Things About Mesothelioma And Asbestos
Image
5 Laws That Anyone Working In Pavement Mobility Scooters For Sale Should Be Aware Of
Image
Unlock Affordable Storage Solutions: Your Guide to Storage Unit Cost in Palatka, FL
Image
The 10 Worst Lawyers For Accidents At Work FAILURES Of All Time Could Have Been Prevented
Image
Why Accident Lawyer Savannah Is Your Next Big Obsession
Image
Many Of The Common Errors People Make Using Injury Attorneys
Image
There Are A Few Reasons That People Can Succeed In The Mesothelioma Non Asbestos Exposure Industry
Image
Best Free Clinical Notes Software for Healthcare Professionals in 2024
Image
The Most Pervasive Problems With Asbestos Payout
Image
What Freud Can Teach Us About Heavy Duty Mobility Scooters For Sale
Image
Twój Raj na Ziemi: Jak Wybrać Idealne Meble do Sypialni?
Image
Bi-Fold Door Repair Explained In Fewer Than 140 Characters
Image
10 Untrue Answers To Common Mental Health Questions: Do You Know The Correct Answers?
Image
10 Tips For Getting The Most Value From Mesothelioma Attorney
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .