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
- Create a new spreadsheet.
- Rename the sheet called Sheet1 to Websites.
- Create four columns:
- Name
- URL
- Status
- Last Check
- 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
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.
- Navigate to Tools > Script Editor.
- The script editor will include a starting function. You can remove all the code.
- 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() {
}
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');
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();
Create a variable called issues. This will keep track of how many issues were found.
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.
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 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();
Let’s loop through the rows of data now.
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.
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 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);
}
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);
}
}
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();
}
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);
}
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!
- Within the script editor, navigate to Edit > Current project’s triggers.
- Select Add Trigger.
- At the “Choose which function to run” field, select checkStatus.
- At the “Select event source” field, select Time-driven.
- At the “Select type of time based trigger” field, select Day timer.
- At the “Select time of day” field, select a time range you’d like to run the script.
- 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);
}
}
Visit our website at https://nightwolf.dev and follow us on Facebook and Twitter!
Top comments (7)
Thanks but i am having a error when i run the script it shows me TypeError: Cannot read property 'getDataRange' of null. I have verified that I am using the correct spreadsheet name. You have any idea as to what maybe happening? I have just removed the mail function and rest is same
Make sure your sheet name is called Websites.
In case anyone on this thread is interested, I posted another article explaining how to programmatically move rows, create custom menu and send emails.
Dude, literally I was looking to do something similar to check the websites in my work. We have differents sites and it is difficult to trace every one before an user starts spamming us.
This will gonna help us to catch and repair it before anyone else notices the site downed.
Grat article, simple, easy and understandable.
Glad it can help your use case. Thank you!
Thank you for this article: my entry-point for Google Apps Script
You're welcome! Glad it helped.