Monitor your website with google sheet

Since sheet automation website went live, I have been worrying if the website is available. Even though the chance of the site going down is pretty low, I need a way to give me peace of mind. Manually checking the website is simply not practical.

There are many monitoring services that allow you to monitor your web site content, measure loading performance and generate fancy reports.

However, I feel most of them are overkill. What I need is really a simple solution that can:

• constantly monitor the website
• alert me when it is down

Of course I don’t want to run a cron job on my laptop or any server and deal with complex email API.

Luckily, there is an extremely simple solution with no cost in the world of google sheet.

The simplest solution

To be honest, I was surprised at the simplicity of the needed code. It’s only 10 lines with all the curly braces counted and can even be made less than 5 if the code is slightly compacted.

function monitorSheetAutomation(){
  var response = UrlFetchApp.fetch("https://www.sheetautomation.com/");
  if(response.getResponseCode() != 200){
    MailApp.sendEmail({
      to: "<my email>",
      subject: "SheetAutomation website is down",
      body: "Response: " + response.getContentText()
    });
  }
}

The script tries to fetch the homepage of the website. If the response code is 200, it means everything is ok. Otherwise, something may be wrong and an email with the response content is sent out immediately.

To make the function run automatically and repeatedly, a time trigger has to be created.

The above configuration will make sure the function runs every 5 minutes. You can make the interval shorter (could be down to 1 minute) or longer.

That’s it.

If I’m not getting alerts, I can now rest assured that everything is running as it should.

A better solution

There is always room for improvement.

I don’t really want to receive a ton of emails even when the site is down. Though the situation is urgent, one or two emails are more than enough.

And there are various situations when the site may become unreachable temporarily. So it may be a false alarm if we send out alert as soon as the url fetch call fails. A simple strategy would be to wait until the test fails 2 or 3 times in a row.

To minimise the number of emails and avoid false alarm, the script needs to remember the history.

Here is the improved version.

function monitorSheetAutomation(){
  var response = UrlFetchApp.fetch("https://www.sheetautomation.com/");

  if(response.getResponseCode() == 200){
    PropertiesService.getUserProperties().setProperty("site_monitor_count", 0);
  }else{
    var count = PropertiesService.getUserProperties().getProperty("site_monitor_count");  
    count = parseInt(count) + 1;
    PropertiesService.getUserProperties().setProperty("site_monitor_count", count);
    if(count == 3){
      MailApp.sendEmail({
        to: "<my email>",
        subject: "SheetAutomation website is down",
        body: "Response: " + response.getContentText()
      });
    }
  }
}

What it does is to track the number of consecutive failed calls. If the call is not successful the number is increased and saved in user properties, otherwise the number is reset to 0. Only when the number is 3, the alert email is sent out.

A powerful monitoring solution

I’ll stop here. The above solution works perfectly for me.

However, with google sheet and apps script, it’s absolutely possible to do a lot of things that those commercial monitoring services offer.

If the sheet automation site gets more complex, I might make the simple solution more powerful as well, with the ability to record page loading speed, save all historical data and visualize the overall performance of the website. We’ll see.

Comments

Add a Comment