Hello, Community! Today, we’re exploring how to leverage the Agent Collection API endpoint using Google Sheets, specifically with the built-in Apps Script feature. This powerful API allows you to fetch a collection of agents, filter, order, and paginate the result set. In this post, we’ll walk you through how to use it within Google Sheets and discuss some useful use-cases you can implement with it. I will even throw in the Agent Groups Collection API.
Endpoint:
GET /agents
GET /agent_groups
Base URL:
https://[HOSTNAME]
Authorization:
Bearer Token
How to Use the Agent Collection API Endpoint with Google Sheets
Preparations
-
Rename the first sheet to “Config”, and add the following rows:
-
Open your Google Sheet and click on Extensions > Apps Script.
-
You’ll see a new tab with an editor. This is where we’ll write our script to interact with the API endpoint.
-
Add this code which will read from the “Config” sheet and initialize the configurations:
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Config'); const API_TOKEN = sheet.getDataRange().getValues()[0][1]; const HOSTNAME = sheet.getDataRange().getValues()[1][1]; const baseUrl = `https://${HOSTNAME}`; function getOptions() { var headers = { "Authorization": "Bearer " + API_TOKEN, "Accept": "application/vnd.api+json" }; return { "headers": headers, "method" : "GET" }; }
Now we’ll add a couple of utility functions that will allow us to retrieve paginated data from the API with a single function call:
function getData(url) {
var data = [];
var included = [];
var page = 1;
var totalPages = 1;
while (page <= totalPages) {
var endpointUrl = url + "&page[offset]=" + page;
var response = UrlFetchApp.fetch(endpointUrl, getOptions());
var json = JSON.parse(response.getContentText());
totalPages = json.meta?.page?.total;
data = data.concat(json.data);
included = included.concat(json.included);
page++;
}
return { data, included };
}
The getData(url) function fetches all pages of data from a paginated NetBeez API endpoint. It initially sets page and totalPages to 1. In the while loop, it fetches data from the API for the current page, parses the JSON response, and appends data and included fields to their respective arrays. It also updates the totalPages from the response metadata. The loop continues until all pages of data are fetched. The function finally returns an object containing all data and included fields.
Now let’s explore some examples on how to interact with the API endpoint using Apps Script.
Fetching All Agents with Network information and the Agent Groups they belong to
The API provides various filter options to fetch agents based on different criteria. Here’s an example of fetching agents with specific filters:
function getAgentData() {
var url = `${baseUrl}/agents?type=beta&page[limit]=25`;
const { data, included } = getData(url);
var sheetName = "Agent Data";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}
let headerRow = ['id', 'name', 'isp_name', 'agent_type_name', 'ongoing_incident', 'agent_groups', 'ip_address', 'gateway', 'public_ip', 'mac', 'dashboard_link']
var rows = [headerRow];
const agentGroupsSet = new Set();
data.forEach(function(item) {
var row = [];
row.push(item.id);
row.push(item.attributes.name);
row.push(item.attributes.isp_name);
row.push(item.attributes.agent_type_name);
row.push(!!item.attributes.open_incident_id);
row.push(item.relationships.agent_groups.data.map(ag => { agentGroupsSet.add(ag.id); return ag.id}).join(','));
networkInterfaces = item.relationships.network_interfaces.data.map(ni => getNetworkInterface(included, ni.id));
var ipCell = '', gatewayCell = '', publicIpCell = '', macCell = '';
networkInterfaces.map(ni => {
ipCell += `${!!ipCell?', ':''}${ni.attributes.key}: ${ni.attributes.ip_address}`;
gatewayCell += `${!!gatewayCell?', ':''}${ni.attributes.key}: ${ni.attributes.gateway} `;
publicIpCell += `${!!publicIpCell?', ':''}${ni.attributes.key}: ${ni.attributes.external_ip} `;
macCell += `${!!macCell?', ':''}${ni.attributes.key}: ${ni.attributes.mac} `;
});
[ipCell, gatewayCell, publicIpCell, macCell].map(cell => row.push(cell));
row.push(`https://${HOSTNAME}/?#agent-tab/${item.attributes.category == 'remote_worker_agent' ? 'remote-worker' : item.attributes.category.replace(/_/g, "-")}-view/${item.id}?tab=agent-general-tab`)
rows.push(row);
});
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
getAgentGroups(agentGroupsSet.entries);
}
function getNetworkInterface(data, id) {
return data.find((ni => ni.id == id))
}
getAgentData(): This function retrieves data for all agents from a given API endpoint and inserts it into a Google Spreadsheet. It fetches the data, creates or accesses a sheet called ‘Agent Data’, and constructs rows of agent data including fields like id, name, ISP name, agent type, incident status, agent groups, IP addresses, gateway, public IP, MAC address, and a dashboard link. It then populates the sheet with this data. Note that you can add filters on this API call in order to fetch only the specific agents that interest you based on those filters. You can find out which filters you can apply on our NetBeez API documentation page.
getNetworkInterface(data, id): This function takes an array of data and an id as parameters, and returns the object from the data array that matches the given id. It’s primarily used to get the specific network interface data from the included data in the response of the API call.
The last data function is the one that fetches the Agent Groups:
function getAgentGroups() {
var url = `${baseUrl}/agent_groups?page[limit]=25`;
const { data } = getData(url);
var sheetName = "Agent Group Data";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
if (!sheet) {
sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);
}
let headerRow = ['id', 'name', 'agent_id']
var rows = [headerRow];
data.forEach(function(item) {
item.relationships.agents.data.forEach(a => {
var row = [];
row.push(item.id);
row.push(item.attributes.name);
row.push(a.id);
rows.push(row);
})
});
sheet.getRange(1, 1, rows.length, rows[0].length).setValues(rows);
}
The getAgentGroups() function retrieves agent group data from a specific API endpoint and populates a Google Spreadsheet with it. It fetches the data, creates or accesses a sheet called ‘Agent Group Data’, and builds rows for each agent group, with fields like id, name, and agent_id. It then writes these rows into the ‘Agent Group Data’ sheet. The function’s design allows it to create an individual row for each agent associated with a group, showing the relation between agents and their groups.
Adding a menu item to refresh the data
The following onOpen() function is used to create a custom menu in Google Sheets when the spreadsheet is opened.
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('NetBeez Data')
.addItem('Fetch All Data', 'getAgentData')
.addToUi();
}
In this specific implementation, it adds a menu item titled ‘NetBeez Data’ to the spreadsheet’s menu bar. Under this menu, it further adds a submenu item ‘Fetch All Data’. When clicked, this submenu item triggers the getAgentData function, which retrieves and populates the agent data into the spreadsheet.
This function uses Google Apps Script’s SpreadsheetApp class to interact with the Google Sheets UI and is automatically executed when the spreadsheet is opened due to its special name onOpen().
Already Staged Code: In case you’d like to dive right in and start playing with this code here is a predefined spreadsheet that is already setup. All you have to do is put in your own configurations.
Use-Cases
Here are some useful use-cases you can implement using the Agent Collection API endpoint:
-
Agent Monitoring Dashboard: Use Google Sheets to build a monitoring dashboard to display agent status, active incidents, and alert counts.
-
Resource Allocation and Planning: Analyze agent data within Google Sheets to optimize resource allocation, such as determining if any agents are reaching their target test limit or scheduled test limit.
-
Network Infrastructure Analysis: Investigate network interfaces, access points, and agent types to gain insights into your network infrastructure, identify bottlenecks, and improve overall network performance.
-
Agent Inventory Management: Keep track of agent categories, classes, software versions, and other attributes to manage agent inventory and plan future updates or expansions.
Please note that since Google Sheets and Apps Script are not as flexible or powerful as a programming language like Python, some functionality might not be available or would require additional workarounds.
That’s it! Now you have a better understanding of how to interact with the Agent Collection API endpoint using Google Sheets. Explore further, and feel free to share your experiences or ask questions in the community! Happy coding!