Integrating Jira with Google Data Studio - Part 1

I've really been enjoying working with Jira in Google Data Studio lately, and I wanted to share.

(See Part 2 here: https://www.dhirubhai.net/pulse/integrating-jira-google-data-studio-part-2-william-kennedy/)

There are two approaches I'll cover here, and both will net you the ability to view a custom set of Jira data in a spreadsheet and connect that spreadsheet as a data source in Data Studio.

1. Atlassian's awesome Jira Cloud for Google Sheets integration
2. JSON data from REST API calls in the browser

Jira Cloud for Google Sheets

This two-part integration assumes you're using Jira Cloud. Follow the excellent and simple setup instructions to link your Atlassian account to start importing data from your favorite JQL into Google Sheets.

If that weren't enough, we will use the resulting Sheet as a Data Source in Data Studio.

REST (Not so) Easy

Another method follows Eric Shiflet's example to get Jira data into a Google Sheet. The same method can be applied to other REST endpoints, helpful to build blended data sources.

1. Create a new Jira API Token for this exercise: https://id.atlassian.com/manage-profile/security/api-tokens and encode using Base64 a string with your username and API token separated by a colon (username:token)
2. Create a new Google Sheet and open the Script Editor: https://script.google.com/
3. Copy this script and paste it into the Script Editor https://gist.github.com/wjkennedy/c8dd63e5f51814ba63ca6f75e1aa7f43
4. Add these details to your sheet (shout out to Eric Shiflet): URL, Return, FilterOptions, Base64 auth. Supply the Base64 string here.
5. You can now use the =ImportJSON function to provide the endpoint and parameters of your request
6. Call the ImportJSON function by entering this in a cell?=ImportJSON(URL, return filter, options, optional base64 pw)?where each of the four parameters is the cell where its value is defined. In the table above, it would be:?=ImportJSON(B1, B2, B3, B4)

1. You should see the output of your query as in the image above.
2. I find it best to use columns A and B to store your parameters then hide rows 1-4, placing your formula in A5
3. Your sheet is ready now for use as a data source in Google Data Studio!

Google Data Studio

Adding data and/or a chart will allow you to select a data source using the worksheet prepared earlier. I find it helpful to create a Worksheet per project/site and create worksheets with each of the requests I want to make. You can have worksheets for the JQL driving your favorite dashboard and various other REST endpoints, such as fields and filters.

I hope to explore this integration more in future articles and posts.

See part 2 here: https://www.dhirubhai.net/pulse/integrating-jira-google-data-studio-part-2-william-kennedy/