Website Status Check
Tags: googleappsscript, googlesheets, javascript
Published: 2021-01-01 23:17:00

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](https://dev-to-uploads.s3.amazonaws.com/i/6r9ljyq1dj4ze82q6ydk.png)

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](https://dev-to-uploads.s3.amazonaws.com/i/us6y19dcr31wbyk309lx.png)

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.

```javascript
function checkStatus() {
 
}
```

Let’s continue to add code to the function. Create a variable called **sheet**. This gets the sheet called Websites and returns a [Sheet](https://developers.google.com/apps-script/reference/spreadsheet/sheet) object, which will provide additional functions we’ll need.

```javascript
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Websites');
```

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

```javascript
var rows = sheet.getDataRange().getValues();
```

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

```javascript
var issues = 0;
```

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.

```javascript
rows.shift();
```

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](https://developers.google.com/apps-script/reference/spreadsheet/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.

```javascript
sheet.getRange('C2:D').clear();
```

Let’s loop through the rows of data now.

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

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.

```javascript
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 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](https://developers.google.com/apps-script/reference/url-fetch) 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.

```javascript
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);
 
}
```

## 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](https://developers.google.com/apps-script/guides/services/quotas) on how many emails can be sent per day.

```javascript
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);
  }
 
}
```

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.

```javascript
if (issues > 0) {
  notify();
}
```

## Custom Menu

Instead of running the checkStatus function from the script editor, let’s create a [custom menu](https://dev.to/nightwolfdev/create-a-custom-menu-in-google-sheets-3nc) 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!

```javascript
function onOpen() {
   
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
 
  var menuOptions = [{
    name: 'Check Status',
    functionName: 'checkStatus'
  }];
   
  spreadsheet.addMenu('Manage', menuOptions);
 
}
```

## Time Based Trigger

![Time Based Trigger](https://dev-to-uploads.s3.amazonaws.com/i/dh25rbsfuz0m7ohb9ezl.png)

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

```javascript
// 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);
  }
 
}
```

---
**Visit our website at [https://nightwolf.dev](https://nightwolf.dev) and follow us on [Facebook](https://www.facebook.com/NightWolfDevelopment) and [Twitter](https://twitter.com/nightwolfdev)!**