Building a Google Data Studio connector for Jira

At FXStreet we are pivoting to a new era, we are now focusing on Data Driven decisions. The aim of this new approach is allow everyone setting goals for their projects, measure them and validate their ideas as soon as possible. One of the measures that our management team wanted to get was the amount of time invested in every project.

We wanted to centralized all the data from the projects in just one place and the time spent in each project was provided by Jira. To solve this issue, we decided to connect data collected by the Tempo Add-on that comes with Jira with Google Data Studio (GDS), a powerful BI tool developed by Google, to provide information about projects.

In this small tutorial, we are going to show how to build a GDS connector that connects to Jira API and shows the time invested by every department in a particular project.

We decided to use Microsoft technology in order to grab the data from Jira and send it to GDS. Figure 1 shows the high level architecture.

Figure 1: Top Level Architecture and Services Involved

GDS Connector

GDS Connector uses google script, so in order to create your custom connector go to google script and start a new project. According to the documentation your project has to include 2 files (Figure 2); code.gs and appsscript.json. The first one defines your code, the second your metadata. You can follow the simple 2 step process from the documentation.

Figure 2: Files included in the GDS Connector project

All the magic for our connector happens in the getData() function. Basically what we do is:

1. Prepare the DataSchema to receive all the information
2. Make the request for each project to the API

A couple of important things to mention here:

• The request parameter is not populated when debugging, is only populated when you execute the connector on GDS, so in order to properly debug your app you need to mock those parameters.
• The timeout of the connector when calling to external services cannot be configured, so we needed to split the calls by project otherwise it gave timeouts all the time.

Here is the complete code of the script:

function getConfig(request) {
  var config = {
    configParams: [
      {
        name: 'Url',
        displayName: 'Url',
        helpText: 'Enter the Url for which you would like to retrieve the data.',
        placeholder: 'Enter the API url'
      },
      {
        name: 'Projects',
        displayName: 'Projects',
        helpText: 'Enter the coma separated projects list.',
        placeholder: 'Projects'
      }
    ]
  };
  return config;
};
var jiraDataSchema = [
  {
    name: 'Project',
    label: 'Project',
    dataType: 'STRING',
    semantics: {
      conceptType: 'DIMENSION'
    }
  },
  {
    name: 'Team',
    label: 'Team',
    dataType: 'STRING',
    semantics: {
      conceptType: 'DIMENSION'
    }
  },
  {
    name: 'TimeSpent',
    label: 'TimeSpent',
    dataType: 'NUMBER',
    semantics: {
      conceptType: 'METRIC',
      isReaggregatable: true
    }
  }
];
function getSchema(request) {
  return {schema: jiraDataSchema};
};
function getData(request) {
  var dataSchema =[];
  request.fields.forEach(function(field) {
    for (var i = 0; i < jiraDataSchema.length; i++) {
      if (jiraDataSchema[i].name === field.name) {
        dataSchema.push(jiraDataSchema[i]);
        break;
      }
    }
  });
  var url = request.configParams.Url;
  var projectsConfig = request.configParams.Projects;
  var projectKeys = projectsConfig.split(',');
  
  var data = [];
  projectKeys.forEach(function(projectKey){
     getDataByProject(projectKey, data, dataSchema, url);
  })
return {
    schema: dataSchema,
    rows: data
  };
};
function getDataByProject(projectKey,data, dataSchema, apiUrl){
  var url = apiUrl+"&projectKey="+projectKey;
  var response = UrlFetchApp.fetch(url);
  var worklogs = JSON.parse(response.getContentText());
  
  
  worklogs.Values.forEach(function(worklog) {
    var values = [];
    dataSchema.forEach(function(field) {
      switch(field.name) {
        case 'Project':
          values.push(worklog.Project);
          break;
        case 'Team':
          values.push(worklog.Team);
          break;
        case 'TimeSpent':
          values.push(worklog.TimeSpent);
          break;
        default:
          values.push('');
      }
    });
    data.push({
      values: values
    });
  });
};
function getAuthType() {
  var response = {
    "type": "NONE"
  };
  return response;
}

Azure Function

Next, we just need to implement the request to Jira from our serverless app. In this case, we have implemented the function with C# despite you could also implement it with any of the available languages (Figure 4).

Figure 4. Azure Functions available languages

The cool thing about Azure Functions is that you can create your own API endpoint in a matter of seconds, even in the Azure Portal you can develop your code, click launch and that’s it, you have an available service to be called from whatever service.

Here is the complete code:

using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Threading.Tasks;
using FXStreet.Serverless.Azure.Functions.Configuration;
using FXStreet.Serverless.Azure.Functions.Services;
using Microsoft.Azure.WebJobs;
using Microsoft.Azure.WebJobs.Extensions.Http;
using Microsoft.Azure.WebJobs.Host;
using TempoRestClient.Client;
namespace FXStreet.Serverless.Azure.Functions
{
    public static partial class JiraWorkLogData
    {
        private static TraceWriter internalLogger = null;
[FunctionName("JiraWorkLogData")]
        public static async Task<HttpResponseMessage> Run([HttpTrigger(AuthorizationLevel.Function, "get", Route = null)]HttpRequestMessage req, TraceWriter log)
        {
            internalLogger = log;
            string projectKey = req.GetQueryNameValuePairs()
              .FirstOrDefault(q => string.Compare(q.Key, "projectKey", true) == 0)
              .Value;
var data = WorkLogSummary(projectKey);
return req.CreateResponse(HttpStatusCode.OK, data);
        }
private static JiraWorkLogResponseList WorkLogSummary(string project)
        {
            var token = AuthService.GetAccessToken();
            var settings = ConfigService.GetSettings(token);
            var config = new JiraWorkLogConfig(settings);
var teams = config.Teams;
            var projectKeys = new List<string>();
if (!string.IsNullOrEmpty(project))
            {
                projectKeys.Add(project);
            }
            else
            {
                projectKeys = config.ProjectKeys;
            }
var result = new JiraWorkLogResponseList();
foreach (var projectKey in projectKeys)
            {
                foreach (var team in teams)
                {
                    var time = GetWorkLogTime(projectKey, team.Key, config);
                    var teamTime = new JiraWorkLogResponse { Project = projectKey, Team = team.Value, TimeSpent = time };
                    result.Values.Add(teamTime);
                    internalLogger.Info($"{team.Value} -> {teamTime}");
                }
            }
return result;
        }
private static int GetWorkLogTime(string projectKey, string teamId, JiraWorkLogConfig config)
        {
            var tempoFilter = TempoRest.Configure(config.JiraApiUrl, config.JiraApiUserName, config.JiraApiPassword);
            var response = tempoFilter.FilterProjectKey(projectKey).FilterTeamId(teamId).ExecuteWorklogRequest();
var totalSeconds = response.Sum(x => x.TimeSpentSeconds);
            var hours = totalSeconds / 3600;
var result = hours;
            return result;
        }
    }
}

Wrapping up

Finally, we need to use our brand new connector in our GDS project. In order to do that create a new report (Figure 5).

Figure 5. Create a new blank project

Then click on the “Create New Data Source” blue button and after that select your connector at the bottom of the list (Figure 6).

Figure 6. Select your connector

And now insert the URL of your API in Azure functions and a comma separated with the abbreviates of the projects.

Now that we have our project set up, just choose whatever graph best suits you, change the dimensions and finally you will have your first Google Data Studio connected with Jira.