How to monitor APIs with Google Sheets

by Saulius - posted in: Blog

In this tutorial you will learn how to setup a simple API monitoring flow by using only Google Sheets spreadsheet.

We will build simple API monitoring spreadsheet, using Google Sheets with the scheduled tests.

Tests will run HTTP requests against the list the API endpoints we specify.

Lets get started.

Prerequisites

There is one main prerequisite for this tutorial:

Google account with the access to Google Drive / Google Sheets. Any free personal Gmail account will work.

Create Google Sheet

Using your web browser, go to Google Drive space, navigate to Google Sheets and create a new Google Sheets spreadsheet.

api monitoring with google sheets new file

Prepare the layout

Next we will add simple table like structure, which has Description, API endpoint, Status and Last Check columns. This is where we going to maintain API monitoring details.

While we are here, we will add a couple of public API endpoints for testing. Im adding couple of new rows with the links to:

api monitoring with google sheets setup layout

Extending Google Sheet with the custom code

Now we have to add some custom code.

Google Sheets (as well as other Google Workspace apps) support Apps Script language for extensions.

In the spreadsheet at the top menu, go to Extentions => Apps Script. This will create you a new apps script file. We will use JavaScript programming language for customizations.

// create a custom menu item to run tests manually
// when spreadsheet opens.
function onOpen() {

    var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();

    var menuOptions = [{
        name: 'Run API tests',
        functionName: 'checkStatus'
    }];

    spreadsheet.addMenu('API Tests', menuOptions);
}

// main function to run API tests
function checkStatus() {

    var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('apis');
    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 the rows in sheet 
    // and make a request to API endpoint.
    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(), 'Y/M/d h:m a');

        if (url) {

            var response = UrlFetchApp.fetch(url, {muteHttpExceptions: true});
            var responseCode = response.getResponseCode();

            // If the response code is greater than 200
            if (responseCode > 200) {
                status = 'warning';
                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 
            // therefore we add a delay in between each request.
            Utilities.sleep(1000);
        }
    }
}

Here is what is does:

gets the API endpoint urls from our spreadsheet
makes HTTP get requests to all listed API endpoint urls
updates the status column for each API
adds the button to the existing spreadsheet menu bar to allow for manual test runs.

Add the below code to code.gs file in your Apps Scripts project

api monitoring with google sheets apps script

Authorize and run API tests manually

Now when you go back to your spreadsheet and reload it, you will see additional item on the top menu bar called API Tests with Run API tests in the dropdown.

Once you click this button for the first time, Google will ask you to authorize the script.

Click Continue and accept the warning about unverified app (click advanced, and verify to the script).

You will provide the script an access to your spreadsheets and possibility to connect to internet.

Now, click API Tests => Run API tests again and you should see tests running against your API endpoints.

api monitoring with google sheets success

Add a timer

We love automation, specially once it comes to monitoring and APIs.

In order to add the scheduler to your script, got to your Apps Script section, and select Triggers section. Add a new trigger.

You will now have to select the function to run, which in our case checkStatus. Event source for scheduled runs is Time-driven. Select your preferred frequency.

Save the new trigger and you will see it appear in the triggers list.

api monitoring with google sheets set trigger

Conclusion

You have now created the simple automated API monitor with Google spreadsheets.

Hope you find it useful. Let me know.