Apr 10, 2025

Streamlining Security Management: Integrating Guardian Security API with Google Sheets

By blog · 7 minute read

In today's fast-paced cybersecurity landscape, effective vulnerability management requires both powerful tools and accessible data. While dedicated security platforms offer robust capabilities, sometimes you need a simpler way to view, share, and analyze your security data. This is where the combination of the Guardian Security Intelligence API and Google Sheets creates a powerful, flexible solution.

Why Integrate Guardian Security with Google Sheets?

The Guardian Security platform provides comprehensive vulnerability scanning and management, but there are several compelling reasons to bring this data into Google Sheets:

Simplified Reporting: Create custom reports that focus on the metrics that matter most to your team or executives
Data Analysis: Leverage Google Sheets' built-in sorting, filtering, and pivot tables to analyze vulnerability trends
Collaboration: Share vulnerability data with team members who may not have access to the full Guardian platform
Integration: Combine vulnerability data with other business metrics for more comprehensive risk assessments
Automation: Schedule automatic refreshes to keep your security overview current

Building Our Google Sheets Integration

Using Google Apps Script, we've created a custom function that pulls vulnerability data from the Guardian Security API and formats it for easy visualization in Google Sheets.

Key Features of Our Integration

Risk Score Visualization: Color-coded risk scores help quickly identify high-risk assets
Vulnerability Counts by Severity: See critical, high, and moderate vulnerabilities at a glance
Conditional Formatting: Automatic color coding makes it easy to spot problems
One-Click Formatting: Apply professional formatting with a single menu click
Simple API Access: Basic function call requires just your API key

The Technical Implementation

The solution consists of three main components:

Data Retrieval Function: Fetches asset information and vulnerability counts from the Guardian API
Formatting Function: Applies conditional formatting based on risk scores and vulnerability counts
Helper Function: Simplifies the user experience with a one-parameter function call

Here's how the data retrieval works behind the scenes:

First, we get the list of assets from Guardian's API
For each asset, we fetch detailed information including risk score and vulnerability counts
We format the data into rows with the most important information
Finally, we return a neatly organized array that Google Sheets renders as a table

Let's explore the key code sections that make this possible:


function GUARDIAN_ASSETS_WITH_VULNERABILITIES(apiKey, baseUrl, includeAllAssets) {
  // Set default values if parameters are missing
  baseUrl = baseUrl || "/external";
  includeAllAssets = includeAllAssets === true;

  // Input validation
  if (!apiKey) {
    return [["Error: API key is required"]];
  }

  // Set up the request headers
  const headers = {
    "x-api-key": apiKey
  };

  try {
    // First, get all assets with failed scans or all assets
    const endpoint = includeAllAssets ? "/assets" : "/assets/failed-scan";
    const assetResponse = UrlFetchApp.fetch(baseUrl + endpoint, {
      method: "get",
      headers: headers,
      muteHttpExceptions: true
    });

    // Parse the response
    if (assetResponse.getResponseCode() !== 200) {
      return [["Error: Failed to fetch assets. Status code: " + assetResponse.getResponseCode()]];
    }

    const assets = JSON.parse(assetResponse.getContentText());

    // Initialize the result array with headers
    const result = [
      [
        "Asset ID", 
        "Hostname", 
        "IP Address", 
        "OS", 
        "Last Analyzed", 
        "Failed Scan Qty", 
        "Risk Score", 
        "Critical Vulnerabilities", 
        "High Vulnerabilities", 
        "Moderate Vulnerabilities"
      ]
    ];

    // Process each asset...
    for (let i = 0; i < assets.length; i++) {
      const assetBasic = assets[i];

      // Get detailed asset information to fetch risk_score and vulnerability counts
      const assetDetailResponse = UrlFetchApp.fetch(baseUrl + "/assets/" + assetBasic.id, {
        method: "get",
        headers: headers,
        muteHttpExceptions: true
      });

      let riskScore = 0;
      let criticalCount = 0;
      let highCount = 0;
      let mediumCount = 0;
      let asset = assetBasic; // Default to basic asset info

      // Update asset with detailed information if successful
      if (assetDetailResponse.getResponseCode() === 200) {
        asset = JSON.parse(assetDetailResponse.getContentText());
        riskScore = asset.risk_score || 0;
        criticalCount = asset.severity_critical || 0;
        highCount = asset.severity_high || 0;
        mediumCount = asset.severity_medium || 0;
      }

This code retrieves detailed information for each asset, including the risk score and vulnerability counts. The Guardian API provides these counts directly, saving us from having to manually count vulnerabilities and improving performance.

Once we have the detailed information, we format it and add it to our results:


      // Format date for readability
      const lastAnalyzed = asset.last_analyzed ? new Date(asset.last_analyzed) : "";

      // Add asset data to results
      const row = [
        assetBasic.id,
        assetBasic.hostname || "",
        assetBasic.ip || "",
        assetBasic.os || "",
        lastAnalyzed,
        asset.failed_scan_qty || 0,
        riskScore,
        criticalCount,
        highCount,
        mediumCount
      ];

      result.push(row);

      // Add brief pause to avoid hitting rate limits
      Utilities.sleep(100);
    }

    return result;

Setting Up the Integration

To use this integration in your own Google Sheets:

Open your Google Sheet
Go to Extensions > Apps Script
Create a new script file and paste the complete code (shared below)
Save and return to your sheet
Call the function in a cell: =GUARDIAN_VULNERABILITIES("your-api-key")
Use the "Guardian Security" menu to apply formatting

Visual Formatting with Conditional Rules

One of the most powerful aspects of this integration is the automatic formatting that makes vulnerabilities and risk scores visually apparent. Here's the code that handles this:


function applyGuardianVulnerabilityFormatting() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();

  // Skip if sheet is empty or has only headers
  if (values.length <= 1) return;

  // Find column indexes
  const headers = values[0];
  const riskScoreIndex = headers.indexOf("Risk Score");
  const criticalIndex = headers.indexOf("Critical Vulnerabilities");
  const highIndex = headers.indexOf("High Vulnerabilities");
  const mediumIndex = headers.indexOf("Moderate Vulnerabilities");

  // Check if required columns exist
  if (riskScoreIndex === -1 || criticalIndex === -1 || highIndex === -1 || mediumIndex === -1) {
    SpreadsheetApp.getUi().alert("Required columns not found. Please run the GUARDIAN_ASSETS_WITH_VULNERABILITIES function first.");
    return;
  }

  // Clear existing conditional formatting rules
  sheet.clearConditionalFormatRules();
  let rules = [];

  // Critical risk (80-100): Purple background
  let criticalRiskRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThanOrEqualTo(80)
    .setBackground("#E6CFFC") // Light purple
    .setFontColor("#7030A0") // Dark purple
    .setBold(true)
    .setRanges([sheet.getRange(2, riskScoreIndex + 1, values.length - 1, 1)])
    .build();
  rules.push(criticalRiskRule);

  // Add other conditional formatting rules for different risk levels...

This code analyzes your spreadsheet to find the relevant columns, then applies conditional formatting rules to highlight risk scores and vulnerability counts based on their severity.

Real-World Benefits

This integration has already proven valuable for several teams:

Security Managers: Get a quick overview of the most vulnerable assets
IT Teams: Prioritize patching based on risk scores and vulnerability counts
Executives: View simplified dashboards of security posture without technical details
Compliance Teams: Export formatted reports for audits and compliance reviews

Advanced Use Cases

Beyond basic reporting, this integration enables several advanced use cases:

Custom Dashboards: Create charts and graphs based on vulnerability data
Trend Analysis: Track vulnerability counts over time to measure program effectiveness
Risk Scoring: Combine with business impact data to create custom risk prioritization
Multi-Team Collaboration: Create different views for different stakeholders

Future Enhancements

We're constantly improving this integration. Future enhancements may include:

Historical tracking of vulnerability trends
Integration with patch management data
Automated email reports for high-risk findings
Custom risk scoring formulas

Conclusion

By bringing Guardian Security data into Google Sheets, we've created a flexible, accessible way to work with vulnerability information. This approach combines the robust security scanning capabilities of Guardian with the familiar, powerful analysis features of Google Sheets.

Whether you're a security professional looking for better reporting tools or an IT manager seeking to prioritize your team's efforts, this integration offers a straightforward way to leverage your security data more effectively.

Making It User-Friendly

To make the script more user-friendly, we added a simplified function call and an automatic menu:


/**
 * Simple wrapper function to call from a cell with just the API key.
 * This allows for easier use in spreadsheets.
 * 
 * @param {string} apiKey - Your Guardian Security API key
 * @return {array} Array of assets with their details and vulnerability counts
 * @customfunction
 */
function GUARDIAN_VULNERABILITIES(apiKey) {
  return GUARDIAN_ASSETS_WITH_VULNERABILITIES(apiKey);
}

/**
 * Creates a menu item to apply formatting with one click.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Guardian Security')
    .addItem('Apply Vulnerability Formatting', 'applyGuardianVulnerabilityFormatting')
    .addToUi();
}

The GUARDIAN_VULNERABILITIES function is a simple wrapper that makes it easier for users to call our main function without having to specify all the parameters. The onOpen function automatically adds a menu item to the Google Sheets interface when the spreadsheet is opened.

Complete Script

Here's the complete script you can copy and paste into your Google Apps Script editor:


/**
 * Fetches assets with vulnerability counts and risk scores from Guardian Security Intelligence API.
 * 
 * @param {string} apiKey - Your Guardian Security API key
 * @param {string} baseUrl - Base URL for the API (default: "https://baseURL/external")
 * @param {boolean} includeAllAssets - Whether to include all assets or only those with failed scans (default: false)
 * @return {array} Array of assets with their details, vulnerability counts and risk scores
 * @customfunction
 */
function GUARDIAN_ASSETS_WITH_VULNERABILITIES(apiKey, baseUrl, includeAllAssets) {
  // Set default values if parameters are missing
  baseUrl = baseUrl || "https://baseURL/external";
  includeAllAssets = includeAllAssets === true;

  // Input validation
  if (!apiKey) {
    return [["Error: API key is required"]];
  }

  // Set up the request headers
  const headers = {
    "x-api-key": apiKey
  };

  try {
    // First, get all assets with failed scans or all assets
    const endpoint = includeAllAssets ? "/assets" : "/assets/failed-scan";
    const assetResponse = UrlFetchApp.fetch(baseUrl + endpoint, {
      method: "get",
      headers: headers,
      muteHttpExceptions: true
    });

    // Parse the response
    if (assetResponse.getResponseCode() !== 200) {
      return [["Error: Failed to fetch assets. Status code: " + assetResponse.getResponseCode()]];
    }

    const assets = JSON.parse(assetResponse.getContentText());

    // Initialize the result array with headers
    const result = [
      [
        "Asset ID", 
        "Hostname", 
        "IP Address", 
        "OS", 
        "Last Analyzed", 
        "Failed Scan Qty", 
        "Risk Score", 
        "Critical Vulnerabilities", 
        "High Vulnerabilities", 
        "Moderate Vulnerabilities"
      ]
    ];

    // Process each asset
    for (let i = 0; i < assets.length; i++) {
      const assetBasic = assets[i];

      // Get detailed asset information to fetch risk_score and vulnerability counts
      const assetDetailResponse = UrlFetchApp.fetch(baseUrl + "/assets/" + assetBasic.id, {
        method: "get",
        headers: headers,
        muteHttpExceptions: true
      });

      let riskScore = 0;
      let criticalCount = 0;
      let highCount = 0;
      let mediumCount = 0;
      let asset = assetBasic; // Default to basic asset info

      // Update asset with detailed information if successful
      if (assetDetailResponse.getResponseCode() === 200) {
        asset = JSON.parse(assetDetailResponse.getContentText());
        riskScore = asset.risk_score || 0;
        criticalCount = asset.severity_critical || 0;
        highCount = asset.severity_high || 0;
        mediumCount = asset.severity_medium || 0;
      }

      // Format date for readability
      const lastAnalyzed = asset.last_analyzed ? new Date(asset.last_analyzed) : "";

      // Add asset data to results
      const row = [
        assetBasic.id,
        assetBasic.hostname || "",
        assetBasic.ip || "",
        assetBasic.os || "",
        lastAnalyzed,
        asset.failed_scan_qty || 0,
        riskScore,
        criticalCount,
        highCount,
        mediumCount
      ];

      result.push(row);

      // Add brief pause to avoid hitting rate limits
      Utilities.sleep(100);
    }

    return result;

  } catch (error) {
    return [["Error: " + error.toString()]];
  }
}

/**
 * Helper function to apply conditional formatting based on vulnerability severity and risk scores.
 * Call this function from your spreadsheet to automatically format the results.
 */
function applyGuardianVulnerabilityFormatting() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const values = dataRange.getValues();

  // Skip if sheet is empty or has only headers
  if (values.length <= 1) return;

  // Find column indexes
  const headers = values[0];
  const riskScoreIndex = headers.indexOf("Risk Score");
  const criticalIndex = headers.indexOf("Critical Vulnerabilities");
  const highIndex = headers.indexOf("High Vulnerabilities");
  const mediumIndex = headers.indexOf("Moderate Vulnerabilities");

  // Check if required columns exist
  if (riskScoreIndex === -1 || criticalIndex === -1 || highIndex === -1 || mediumIndex === -1) {
    SpreadsheetApp.getUi().alert("Required columns not found. Please run the GUARDIAN_ASSETS_WITH_VULNERABILITIES function first.");
    return;
  }

  // Clear existing conditional formatting rules
  sheet.clearConditionalFormatRules();
  let rules = [];

  // Add rule for Risk Score (higher is worse)
  // Risk score column
  const riskScoreRange = sheet.getRange(2, riskScoreIndex + 1, values.length - 1, 1);

  // Critical risk (80-100): Purple background
  let criticalRiskRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThanOrEqualTo(80)
    .setBackground("#E6CFFC") // Light purple
    .setFontColor("#7030A0") // Dark purple
    .setBold(true)
    .setRanges([riskScoreRange])
    .build();
  rules.push(criticalRiskRule);

  // High risk (60-79): Red background
  let highRiskRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(60, 79)
    .setBackground("#FFCCCB") // Light red
    .setFontColor("#CC0000") // Dark red
    .setBold(true)
    .setRanges([riskScoreRange])
    .build();
  rules.push(highRiskRule);

  // Medium risk (40-59): Orange background
  let mediumRiskRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(40, 59)
    .setBackground("#FFDCB0") // Light orange
    .setFontColor("#FF6600") // Dark orange
    .setBold(true)
    .setRanges([riskScoreRange])
    .build();
  rules.push(mediumRiskRule);

  // Low risk (1-39): Green background
  let lowRiskRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberBetween(1, 39)
    .setBackground("#E6F4EA") // Light green
    .setFontColor("#38761D") // Dark green
    .setBold(true)
    .setRanges([riskScoreRange])
    .build();
  rules.push(lowRiskRule);

  // Vulnerability severity formatting
  // Critical vulnerabilities - purple
  const criticalRange = sheet.getRange(2, criticalIndex + 1, values.length - 1, 1);
  let criticalRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(0)
    .setBackground("#E6CFFC") // Light purple
    .setFontColor("#7030A0") // Dark purple
    .setBold(true)
    .setRanges([criticalRange])
    .build();
  rules.push(criticalRule);

  // High vulnerabilities - red
  const highRange = sheet.getRange(2, highIndex + 1, values.length - 1, 1);
  let highRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(0)
    .setBackground("#FFCCCB") // Light red
    .setFontColor("#CC0000") // Dark red
    .setBold(true)
    .setRanges([highRange])
    .build();
  rules.push(highRule);

  // Medium/Moderate vulnerabilities - orange
  const mediumRange = sheet.getRange(2, mediumIndex + 1, values.length - 1, 1);
  let mediumRule = SpreadsheetApp.newConditionalFormatRule()
    .whenNumberGreaterThan(0)
    .setBackground("#FFDCB0") // Light orange
    .setFontColor("#FF6600") // Dark orange
    .setBold(true)
    .setRanges([mediumRange])
    .build();
  rules.push(mediumRule);

  // Apply all rules
  sheet.setConditionalFormatRules(rules);
}

/**
 * Simple wrapper function to call from a cell with just the API key.
 * This allows for easier use in spreadsheets.
 * 
 * @param {string} apiKey - Your Guardian Security API key
 * @return {array} Array of assets with their details and vulnerability counts
 * @customfunction
 */
function GUARDIAN_VULNERABILITIES(apiKey) {
  return GUARDIAN_ASSETS_WITH_VULNERABILITIES(apiKey);
}

/**
 * Creates a menu item to apply formatting with one click.
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Guardian Security')
    .addItem('Apply Vulnerability Formatting', 'applyGuardianVulnerabilityFormatting')
    .addToUi();
}

Note: This integration uses the Guardian Security API and requires a valid API key. Contact your Codenotary sales representative for API access if you don't already have it.