Reporting API - Google Sheets custom integration

This tutorial explains how to create a custom integration that appends mabl test results to a Google Sheet using a post-execution webhook, JavaScript, and the mabl Reporting API.

🚧

Before you start

To set up this custom reporting workflow, you or someone on your team should be familiar with the following:

• Building webhooks
• Creating and maintaining custom JavaScript code
• Working with APIs Concepts and basic usage of APIs

API setup

To implement this workflow, you need access to the Google Sheets API and the mabl Reporting API.

Google Sheets API

To use the Google Sheets API for programmatically editing a Sheet, Google requires you to use a Google Cloud project. For this tutorial, a free tier Google Cloud project is sufficient. Below are the steps to prepare your Google Cloud project and Google Sheet to access from your webhook.

1. Create a Google Cloud project (free tier available), if you do not already have one
2. Enable the Sheets API for your project
3. Create a Service Account to authenticate with the Sheets API from your webhook. You do not need to provide any roles or permissions to this Service Account.
4. Create a key for the Service Account from step (3), which is securely downloaded to your computer. This key is used in your webhook code to authenticate with the Sheets API.
5. Create a new Google Sheet or open an existing Google Sheet where you want the data to go.
6. Click the Share button and share the Google Sheet with the Service Account email address created in step (3) as an Editor.

Mabl Reporting API

Have a workspace owner create or use an existing mabl Viewer API key. This key is used in your webhook code to authenticate to the mabl reporting API.

Webhook code

Now you can start building your post-execution webhook somewhere that it can be called from mabl after each cloud run. For example, you might create this as a Google Cloud Function or AWS Lambda.
At a high level, when mabl plan runs complete, the webhook code does the following:

1. Extract the plan run ID from the post-execution payload: plan_execution.id
2. Get plan run results for the extracted plan run ID using the mabl Reporting API
3. Transform the plan run results into a flat spreadsheet format with rows and columns
4. Append the flattened results to the Google Sheet that you shared when you set up the Google Sheets API.

Import libraries

First, the webhook code needs a couple libraries to communicate with Google and mabl APIs: googleapis and axios. You can install these in your webhook NodeJS project using NPM:

npm install googleapis@111 [email protected] --save

With these libraries, you can start the webhook code by importing them and setting up some information to interact with the mabl Reporting API and Google Sheets.

Set up API info

Replace the values of MABL_API_KEY, SPREADSHEET_ID, SPREADSHEET_PAGE, and GOOGLE_SA_CREDENTIALS_FILE with the appropriate values.

const {google} = require('googleapis');
const axios = require('axios');

// mabl API info
const MABL_API_KEY = '<API-KEY>';
const MABL_ENDPOINT_URL = 'https://api.mabl.com/results/planRun';

// Spreadsheet that has been shared with Google Service Account email address.
// Spreadsheet ID can be found in the URL for the spreadsheet like https://docs.google.com/spreadsheets/d/SPREADSHEET-ID-IS-HERE
const SPREADSHEET_ID = '<SPREADSHEET-ID>';
const SPREADSHEET_PAGE = 'Sheet1';

// Google scopes needed to append to spreadsheet
const SCOPES = ['https://www.googleapis.com/auth/spreadsheets'];

// File containing Google Service Account key created and downloaded in Setup step (4)
const GOOGLE_SA_CREDENTIALS_FILE = 'path/to/google-service-account-key.json';

Define test result fields

Next, define what fields you want to extract, in order, and push to the spreadsheet. If a particular field does not exist or does not have a value for a run result, an empty cell indicates this in the resulting spreadsheet row. The fields specified below, in the given order, should correspond to the columns defined in the Google Sheet, although the column headings in the Google Sheet do not need to use the exact field name to describe the column.

// Ordered fields on individual test runs
const TEST_RUN_FIELDS = [
  "initial_url",
  "browser",
  "browser_version",
  "execution_runner_type",
  "scenario_name",
  "test_id",
  "test_version",
  "test_name",
  "test_type",
  "branch",
  "test_run_id",
  "test_run_app_url",
  "is_ad_hoc_run",
  "start_time",
  "end_time",
  "run_time",
  "status",
  "success",
  "failure_category",
  "emulation_mode"
];

// Ordered fields on plan runs, to be appended after the test run fields.
const PLAN_RUN_FIELDS = [
  "workspace_name",
  "plan_id",
  "plan_run_id",
  "plan_name",
  "trigger_type",
  "triggering_deployment_event_id",
  "application_name",
  "environment_name",
  "status",
  "success"
];

Build the webhook functions

Now you can build the functions you'll need for the webhook to do its job. First, create a function for steps (2) and (3) that retrieves the detailed test run results for a given plan run ID and transforms them into a flattened list of values matching the fields above.

async function getPlanRunResults(id) {
  const response = await axios.get(`${MABL_ENDPOINT_URL}/${id}`, {
    auth: {
      username: 'key',
      password: MABL_API_KEY,
    }
  });
  return response.data.test_results.map(result => {
    const testRunFields = TEST_RUN_FIELDS.map(field => result[field]);
    const planRunFields = PLAN_RUN_FIELDS.map(field => response.data[field]);
    return [...testRunFields, ...planRunFields];
  });
}

Next build functions to load the Service Account credentials to authenticate with Google and use them to push the flattened run results to the spreadsheet.

async function loadGoogleCredentials(filename) {
  return new google.auth.GoogleAuth({
    keyFile: filename,
    scopes: SCOPES,
  });
}

async function appendToSheet(googleAuth, data) {
  const sheets = google.sheets({version: 'v4', auth: googleAuth});
  return await sheets.spreadsheets.values.append({
    spreadsheetId: SPREADSHEET_ID,
    range: SPREADSHEET_PAGE,
    valueInputOption: 'RAW',
    insertDataOption: 'INSERT_ROWS',
    resource: {
      values: data
    }
  });
}

Finally, you can use these functions in the top-level function that accepts the webhook payload and pushes detailed run results to the Google Sheet.

async function handlePlanRunWebhook(webhookPayload) {
  const planRunId = webhookPayload.plan_execution.id;
  const planRunResult = await getPlanRunResults(planRunId);
  const googleAuth = await loadGoogleCredentials(GOOGLE_SA_CREDENTIALS_FILE);
  await appendToSheet(googleAuth, planRunResult);
  console.log(`Successfully processed plan run ID [${planRunId}]`);
}

Create a post-execution webhook

Once your webhook is up and running, provide the URL to mabl as a post-execution webhook that is called after each run.