LinkedIn
Twitter
When it comes to data analysis of API data, end users like API Project Managers or Marketing staff, like to use the well-known sheets. Despite their disadvantages (data fragmentation, outdated data, lack of sharing...), it empowers users to generate ad-hoc and personalized views in a simple and quick manner.
This article shows a simple example of using the Apigee Analytics API in Google sheets: data is imported into a sheet, for the end-user to create his own analysis (graphs, calculations, ...).
First of all, create a new blank Google Sheet (https://docs.google.com/spreadsheets).
In the "Tools" menu, select the "Script Editor" item. A new window opens, where you can type in your code. Google Sheets lets you customize the appearance of your sheet menu. We'll use that feature to add a menu item that will contain the shortcut to our function to be executed.
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Apigee').addItem('Refresh Average response time', 'getAve rageResponseTime').addToUi();
}As you can see, this will add a menu called "Apigee", including a menu Item "Refresh Average response time", when the sheet is loaded. When this menu item is clicked, it will call the "getAverageResponseTime" function.
To simplify the code, we will create an intermediate function "getData", that will make the Apigee Analytics API call. Note that the API call requires basic authentication: therefore we'll need a header containing the base-64-encoded credentials of the org user.
function getData(org, env, query, authorizationToken){
// Create the URL from the parameters
var url = 'https://api.enterprise.apigee.com/v1/o/'
+ org
+ '/environments/'
+ env
+ '/stats'
+ query;
// Add the base-64 authorization header
var headers = {
'Authorization': 'Basic ' + authorizationToken
};
var options = {
'method': 'get',
'headers': headers
};
// Call the URL
var response = UrlFetchApp.fetch(url, options);
return response;
}
We'll now create a function that will use the JSON response of the "getData" function to populate our sheet as needed. The encoded credentials for the header can be simply generated by using postman (REST client plugin for Chrome): at the top left of the request window, click "Basic Auth" and type in the credentials. Then hit the "refresh headers" button, which will generate a header containing the base-64-encoded credential value.
function getAverageResponseTime() {
// Set the environment variables
var organization="emea-demo3";
var environment="test";
var query = "/apis?select=avg(target_response_time)&timeRange=09/01/2015%2000:00~09/08/2015%2000:00&timeUnit=day";
var authorizationHeaderToken = "YourEncodedCredentialsHere";
// Call the Analytics API
var response = getData(organization, environment, query,authorizationHeaderToken);
// Parse the response
var payload = JSON.parse(response);
// Get the response in an array to parse it
var arr_dimensions = payload.environments[0].dimensions;
// Set Sheet "Title"
SpreadsheetApp.getActiveSheet().getRange('A1').setValue("Average response time of proxies in org " + organization + " in environment " + environment);
//Set starting point to populate the sheet
var j=3;
SpreadsheetApp.getActiveSheet().getRange('A2').setValue("Proxy Name");
SpreadsheetApp.getActiveSheet().getRange('B2').setValue("Avg. response time in ms");
// Parse the response and set the values
for(var i in arr_dimensions){
SpreadsheetApp.getActiveSheet().getRange('A'+j).setValue(arr_dimensions[i].name);
SpreadsheetApp.getActiveSheet().getRange('B'+j).setValue(arr_dimensions[i].metrics[1].values);
j++;
}
}
Replace the "authorizationHeaderToken" value with the encoded credentials, and update the "organization" and "environment" values to reflect yours. The "query" parameter should also be replaced by the query you want to execute. At the end of the article, you'll find a link to reference page of Analytics API calls.
Once you have saved the scripts, reload your Google Sheet. You will now see the "Apigee" menu and the "Refresh Average response time" menu item. Click it, and your sheet should get populated with the Apigee Analytics, as requested in the query.
In case you are having trouble during the execution of the script, use the "View/Logs" and "View/Execution Transcript" functions of the script editor.
Thanks for reading and please feel free to leave your feedback!
More about Apigee Analytics APIs can be found here:
