Skip to content

Instantly share code, notes, and snippets.

@abfo

abfo/Code.gs

Last active Apr 10, 2020
Embed
What would you like to do?
Pull Azure Metrics into a Google Sheet (for i.e. Data Studio monitoring). For instructions see https://ithoughthecamewithyou.com/post/using-the-azure-monitor-rest-api-from-google-apps-script
// script settings
var ClientID = '';
var TennantID = '';
var ClientSecret = '';
var ResourceID = '';
var AuthBaseUrl = 'https://login.microsoftonline.com/' + TennantID + '/oauth2/v2.0/authorize';
var AuthTokenUrl = 'https://login.microsoftonline.com/' + TennantID + '/oauth2/v2.0/token';
var AuthScope = 'https://management.azure.com/user_impersonation offline_access';
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Azure Monitor')
.addItem('Authorize if needed (does nothing if already authorized)', 'showSidebar')
.addItem('Fetch Data', 'fetchData')
.addItem('Reset Settings', 'clearProps')
.addToUi();
}
function fetchData() {
var azure = getAzureService();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var now = new Date();
var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);
var timespan = Utilities.formatDate(yesterday, "GMT", "yyyy-MM-dd'T'00:00:00'Z'/yyyy-MM-dd'T'23:59:59'Z'");
// see https://docs.microsoft.com/en-us/rest/api/monitor/metrics/list
var metricUrl = 'https://management.azure.com/'
+ ResourceID
+ '/providers/microsoft.insights/metrics?timespan='
+ timespan
+ '&interval=P1D&metricnames=AverageResponseTime&aggregation=Average&api-version=2018-01-01';
var response = UrlFetchApp.fetch(metricUrl, {
headers: {
Authorization: 'Bearer ' + azure.getAccessToken()
},
'method' : 'get'
});
var json = JSON.parse(response.getContentText());
var avgResponseTime = json.value[0].timeseries[0].data[0].average;
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName('AvgResponseTime');
sheet.appendRow([Utilities.formatDate(yesterday, "GMT", "yyyy-MM-dd"), avgResponseTime]);
}
// functions below adapted from Google OAuth example at https://github.com/googlesamples/apps-script-oauth2
function getAzureService() {
// Create a new service with the given name. The name will be used when
// persisting the authorized token, so ensure it is unique within the
// scope of the property store.
return OAuth2.createService('azure')
// Set the endpoint URLs, which are the same for all Google services.
.setAuthorizationBaseUrl(AuthBaseUrl)
.setTokenUrl(AuthTokenUrl)
// Set the client ID and secret, from the Google Developers Console.
.setClientId(ClientID)
.setClientSecret(ClientSecret)
// Set the name of the callback function in the script referenced
// above that should be invoked to complete the OAuth flow.
.setCallbackFunction('authCallback')
// Set the property store where authorized tokens should be persisted.
.setPropertyStore(PropertiesService.getUserProperties())
// Set the scopes to request (space-separated for Google services).
.setScope(AuthScope)
// Below are Google-specific OAuth2 parameters.
// Sets the login hint, which will prevent the account chooser screen
// from being shown to users logged in with multiple accounts.
.setParam('login_hint', Session.getActiveUser().getEmail())
// Requests offline access.
.setParam('access_type', 'offline')
// Forces the approval prompt every time. This is useful for testing,
// but not desirable in a production application.
//.setParam('approval_prompt', 'force');
}
function showSidebar() {
var azure = getAzureService();
if (!azure.hasAccess()) {
var authorizationUrl = azure.getAuthorizationUrl();
var template = HtmlService.createTemplate(
'<a href="<?= authorizationUrl ?>" target="_blank">Authorize</a>. ' +
'Close this after you have finished.');
template.authorizationUrl = authorizationUrl;
var page = template.evaluate();
SpreadsheetApp.getUi().showSidebar(page);
} else {
// ...
}
}
function authCallback(request) {
var azure = getAzureService();
var isAuthorized = azure.handleCallback(request);
if (isAuthorized) {
return HtmlService.createHtmlOutput('Success! You can close this tab.');
} else {
return HtmlService.createHtmlOutput('Denied. You can close this tab');
}
}
function clearProps() {
PropertiesService.getUserProperties().deleteAllProperties();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment