Nowadays, I’m working with Google Cloud Services. I’m exploring new ways to built things easier and cheaper. This post shows you how to build an uptime monitor with Google Sheets and app script features.

You can use this script for any reason. You can check your websites or HTTP service statuses with Google servers for free!

Google allows us to write our codes in the Google Docs ecosystem. So we can build our extensions or plugins. In this case, I told you how to build an uptime monitor with a logging system.

If you do not need to know technical stuff you can get a copy of my file from here directly.

How did I build this?

We need to create a function that gets URLs from the sheet. Then we’ll check all URLs one by one to get HTTP statuses. After that, we’ll write the status and timestamp back to the sheet.

How to create an uptime monitor with Google Sheets

How to create uptime monitor with Google Sheets

  1. Create a Google Sheets File

    Go https://sheets.google.com then create a Google Sheet file.

  2. Create to prepare document

    We need these columns to script work.
    Url | Status | Last Check
    Uptime monitor with google sheets

  3. Paste the script to script editor

    Go on menu Tools->Script Editor. Then paste the codes below.
    Uptime monitor with google sheets

  4. Go to triggers page

    In the script editor, you will see the Triggers menu on left. Click there and configure a trigger that allows us to run our script automatically.
    Uptime monitor with google sheets

  5. Create a trigger

    Click the Add Trigger button on the right bottom.

    Uptime monitor with google sheets

  6. Configuring the trigger

    We’ll set up the trigger as a cron job. You need to set options like in the photo below
    Setting up trigger as cron job

The script that includes code

function checkURLs() {

  var rows = new Array;
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('URLs');
  var logsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Logs');
  rows = sheet.getSheetValues(2, 1, sheet.getLastRow(), sheet.getLastColumn());

  for (index in rows) {

    var isAlive = false; //
    var row = rows[index]; //Get current for from rows
    var url = row[0]; //Get url from row
    var _index = parseInt(index) + 2;

    if (typeof url !== 'undefined' && url !== '') {

      try {
        //try to ping
        response = UrlFetchApp
          .fetch(url)
        //if success set alive true.
        isAlive = true;
      } catch (error) {
        //do nothing.
        sheet.getRange(_index, 2).setValue('failed');
        sheet.getRange(_index, 3).setValue(getTime());
        //Write a log to logs section
        logsSheet.appendRow([
          url, error, getTime()
        ])
      }

      if (isAlive) {
        //write live status
        sheet.getRange(_index, 2).setValue(response.getResponseCode());
        sheet.getRange(_index, 3).setValue(getTime());
        continue;
      }

    }

  }

}

function getTime() {
  var d = new Date();
  var timeStamp = d.getTime();
  return d.toLocaleString(); // "12:35 PM", for instance
}

I hope this will good for you. Thanks in advice.

See you in next tutorial!