Automated Excel Reports With Your Rest API And Basic Knowledge of JavaScript

Iev Strygul
Mar 27 · 5 min read

Recently, I had a task to automate creation of weekly Excel reports. I had a data in a database that was being constantly updated, and a Rest API that allowed me to query this database. In this blog post, I want to share with you a fast and easy solution to do automate excel reporting. All you need is a free Google Sheets account and basic knowledge of JavaScript. If you still do not have a Google sheets account, you can make one right now by following this link.

In my use case, I needed to create a weekly report of the downloads statistics of a number of Jira addons, so I will use it as an example.

To begin, let's create a new table and add a few headers .

Atlassian provides a Rest API that allows to acquire addon's statistics by sending a GET request to the following URL schema https://marketplace.atlassian.com/rest/2/addons/[ADDON_KEY]/distribution. So all we needed to do in our code — is to schedule weekly GET requests, process the data, and place it in the relevant cells in my table.

Navigate to Tools -> Scrip Editor. This is the place where the automation magic will happen.

As already mentioned above, the first thing we want to do, is to receive the data by REST. For this purpose, let's create the following function that takes an addon key as parameter, appends it to the mentioned above URL, does a GET request by this URL, and parses the response as JSON.

function getStats(appKey) {
var url = ‘https://marketplace.atlassian.com/rest/2/addons/' + appKey +’/distribution’;
var response = UrlFetchApp.fetch(url);
return JSON.parse(response.getAs(‘application/json’).getDataAsString());
}

We have the data now. Let's insert it in the right place in our table.

function writeStats(stats, sheetName) {
stats.unshift(new Date());
var sheet1 = SpreadsheetApp.getActive().getSheetByName(sheetName);
var newRow = sheet1.getLastRow() + 1;
var startingColumn = 7;
var height = 1;
var width = stats.length;
var range = sheet1.getRange(newRow, startingColumn, height, width);
range.setValues([stats]);
}

This function takes two parameters: 'stats' — the data that we got from our first function getStats(), and 'sheetName' — the name of the sheet where we want to insert this data. The sheet name could be found in the bottom of your table, it is called 'addons' in my case:

The first line of the function adds the current date to the statistics that we received by Rest:

stats.unshift(new Date());

The next line gets us the sheet object

var sheet1 = SpreadsheetApp.getActive().getSheetByName(sheetName);

In the next piece of code we define the coordinates of the cells of our table where we want to insert the new data

var newRow = sheet1.getLastRow() + 1;
var startingColumn = 7;
var height = 1;
var width = stats.length;

Then, we use these coordinates to get the 'range' of cells in our table, and insert the data.

var range = sheet1.getRange(newRow, startingColumn, height, width);
range.setValues([stats]);

In the next function, fetchDownloadsData(appsKeys) will take an array of addon keys as parameter and just unite the two previous functions to fetch the data from the Rest API and insert it in the table

function fetchDownloadsData(appsKeys) {
var downloadsStats = appsKeys.map(function(appKey){
return getStats(appKey).downloads;
});
writeStats(downloadsStats, ‘downloads’);
}

Now, we can create a main() function where we will define the addon keys as an array of Strings and apply the fetchDownloadsData() function to it.

function main() {
var appKeys = [
'com.exalate.jiranode',
'com.idalko.jira.plugins.igrid',
'tge.cloud',
'com.idalko.pivotgadget'
];
fetchDownloadsData(appKeys);
}

Click on the ‘save’ icon to save the code.

Now, the main() function should appear among the others in the drop down on your panel. Select it and click the triangle 'play' icon to test the result of your work.

If everything goes fine, the data should be appended to your table.

Select the columns and click Insert -> Chart to add a graph. A more detailed explanation on how to create charts in Google Sheets could be found here.

In the end, your document looked like this.

Now, let's add a 'trigger' to automate the execution of the main() function.

In your script window, click this button

It will open a new window where in the bottom right corner you can add a new trigger. Click on it.

It will open a new window where you can configure execution of the main() function according to your needs: hourly, weekly, annually…

Automatization of Excel reports is ready! If you need to get the Excel file on your computer, just click File -> Save as… and select Microsoft Excel.

P.S. If you want to have a look on my implementation, I have created a demo file that you can save and play with.