/ knowledge

How to use Google Drive to monitor your websites

I know some people don't like Google as a company, but they actually create some really good stuff (if you ignore Google Hangout which is terrible...).
A lot of people know Google Drive (former Google Docs) to share and work on documents, but this platform can be used for a lot of other very interesting purposes.

What you will build

1

Your Google Spreadsheet will be automatically refreshed every 5 minutes with the results of your monitoring script, displaying response code of the page, response time, content of the page, and so on...
If one of your website is down, an email will be sent to you. When it will be back online, an other email will tell you the good news.

How to do it

  1. First, create a new Spreadsheet on your Google Drive account (yes, screen is in french).

2

  1. Fill the spreadsheet with the information below :

3

  • You have to let the first line empty, it will be filled by the script
  • On line 2, add all websites you want to monitor in the "A" column
  • Specify that you want to do a "GET" command in column "B"
  • Specify (if you want it) one of the options below in column "C" :
    • should-contain
    • should-not-contain
    • response-code
  1. At this moment, your spreadsheet is ready, you just need to add the script and the trigger.
    To do this, go in Tools>Script editor, and paste the whole script below: (be careful to put your email address in the "MyEmailAddress" field at the beginning)

     function isCellEmpty(cellData) {
     return typeof(cellData) == "string" && cellData == "";
     }
    
     function CheckUrls() {
    
     var doc = SpreadsheetApp.getActiveSpreadsheet();
     var cell = doc.getRange('a1');
     var mailMessage = "";
     var resultColumn = 3;
     var nb_sites = 1;
     var MyEmailAddress = "YOUR_EMAIL_ADDRESS";
    
     // Check if the last test has an error, if yes no email is (re)sent if there is still errors
     var hasErrorAlready = false;
     while (cell.offset(nb_sites,0).getValue() != "")
     {
     if (cell.offset(nb_sites,resultColumn+2).getBackgroundColor() == "#ff0000")
     {
    
         hasErrorAlready = true;
     }
     nb_sites++;
     }
    
     // leftmost resultcolumn
     cell.offset(0,resultColumn).setValue(new Date());
     cell.offset(0,resultColumn+1).setValue("Content type");
     cell.offset(0,resultColumn+2).setValue("Success ?");
     cell.offset(0,resultColumn+3).setValue("Seconds");
     cell.offset(0,resultColumn+4).setValue("Comments");
    
     for (var i = 1; i < nb_sites; ++i)
     {
     var cellData = cell.offset(i,0).getValue();
     if (!isCellEmpty(cellData))
     {
    
       var command = cell.offset(i,1).getValue();
       var optionData = cell.offset(i,2).getValue();
       if (optionData=="") optionData="{}";
       var options = Utilities.jsonParse(optionData);
    
       var hasError = false;
       var startTime = new Date();      
    
       if (command=="" | command=="GET")
       {
    
         var responseCode = 404;
         var requestContentText = "";
         var results = "";
         var headers;
         var requestType ="";
    
         var expectedResponseCode = 200;
         if (options["response-code"]!=undefined)
           expectedResponseCode = options["response-code"];
    
         try
         {
           var response = UrlFetchApp.fetch(cellData);
           responseCode = response.getResponseCode();
           requestContentText = response.getContentText();
           headers = response.getHeaders();
           if (headers!=undefined)
             contentType=headers["Content-Type"].toLowerCase();
         }
         catch (e)
         {
           requestContentText = e.message;
         } 
    
         cell.offset(i,resultColumn).setValue(responseCode);
         if (responseCode!=expectedResponseCode)
         {
           hasError = true;
           results += "Expected response code: " + expectedResponseCode + ". ";
         }
    
           cell.offset(i,resultColumn).setComment(requestContentText);
    
         var colOffset = resultColumn+4;
         var containsError = (requestContentText.toLowerCase().indexOf("error") != -1);
    
         if (options["should-contain"]!=undefined)
         {
           var shouldContain = options["should-contain"].toLowerCase();
           var doesContain = (requestContentText.toLowerCase().indexOf(shouldContain) != -1);
           if (!doesContain)
           {
             results += "Not found: " + options["should-contain"] + ". ";
             hasError = true;
           }
         }
    
         if (options["should-not-contain"]!=undefined)
         {
           var shouldNotContain = options["should-not-contain"].toLowerCase();
           var doesContain = (requestContentText.toLowerCase().indexOf(shouldNotContain) != -1);
           if (doesContain)
           {
             results += "Found: " + options["should-not-contain"] + ". ";
             hasError = true;
           }
    
         }
         cell.offset(i,colOffset).setValue(results);
       }
    
       // timer
       var endTime = new Date();
       var timeDiff = endTime-startTime;
    
       // success? (no errors)
       cell.offset(i,resultColumn+2).setValue(!hasError);
    
         if (hasError)
         {
           cell.offset(i,resultColumn+2).setBackgroundColor("red");
           mailMessage += "ERROR on " + cellData + " : " + results + "\n\n";
    
         }
         else
           cell.offset(i,resultColumn+2).setBackgroundColor("green");  
    
         // time spent (in seconds)
         cell.offset(i,resultColumn+3).setValue(timeDiff/1000);
     }
     else
     {
       break;
     }
     }
    
     if (mailMessage!="" && hasErrorAlready == false)
     {
     MailApp.sendEmail(MyEmailAddress, "Monitoring Alert on your websites", mailMessage);
     }
    
     if (mailMessage=="" && hasErrorAlready == true)
     {
     mailMessage = "All websites are back online !";
     MailApp.sendEmail(MyEmailAddress, "Monitoring Alert Ended", mailMessage);
     }
     }
    
     function getTime()
     {
     var startTime = new Date();
     Browser.msgBox(startTime);
     var endTime = new Date();
     var timeDiff = endTime-startTime;
     Browser.msgBox(timeDiff);
     }
    

Save the script and give it a name.

  1. You can now test everything ! :)
    To do this, on the script editor page, select the CheckUrls function in the list and click the "run" button (you may need to authorize the script to modify your spreadsheet). Now, go back to your spreadsheet and you will see data displayed in real-time.
    You should have something like the first screenshot of this article, if not, it's debugging time for you ;)

  2. If everything was OK, you just need one more step to create the trigger which will run the script every 5 minutes.
    Near the "run" button in the script editor, click on the button to display triggers of the project, and create a trigger with the options below:
    7

Save everything, and that's it, Google will now take care of your monitoring.