How to Export From Jira to Excel Using Make [Tutorial]
Exporting Jira issues to Excel is a lingering challenge for many project managers and product owners in charge of tracking new and ongoing projects.
In fact, anyone who has spent time in a reporting position knows how tiresome it is to generate and email Excel reports, and how doing so often feels like a major waste of time.
The worst part of this is that it’s all true: Jira exports to Excel are an incredible waste of time, at least when done manually.
Needless to say, it doesn't have to be boring or manual.
Nowadays, solutions to export from Jira to Excel are widely available and come in many shapes and forms, although few (if any) are as powerful as Make.
In this use case, we will show you how to export from Jira to Excel automatically. Plus, we added a second Make scenario that emails the resulting Excel reports on a weekly basis, so you can keep stakeholders informed without lifting a finger.
But before we get down to business, let’s take a look at what you’ll need to automate Excel and Jira once and for all.
Connecting Excel to Jira: Basic requirements
In order to deploy the following automated scenarios, you will need the following:
A Make account
A Jira account
A Microsoft account (Excel online can also be used)
An Excel spreadsheet
For the sake of simplicity, we will be using a spreadsheet with minimum data to create a simple report, but feel free to configure your spreadsheet to suit your real-life needs.
Finally, this use case is straightforward but will require some of your time. If you want to go grab a cup of coffee before proceeding, this is the perfect time to do it!
First scenario: Adding Jira issues to Excel
The first Make scenario does the grunt of the work for you by adding new Jira issues to Excel as they are created.
Once activated, the scenario will monitor Jira for issues, and add them to Excel automatically.
Step 1: Creating the scenario
To get started, you need to log into your Make account.
On your dashboard, click the “Create a new scenario” button on the top right corner.
You will be taken to the scenario builder, which is where you will create your automated scenarios.
Once there, go ahead and click on the circle sitting at the center of your screen. After you do that, a list of ready-to-use apps will pop up.
In that list, search for Jira, and once you find it select the Jira Cloud Platform app.
After you click on it, all the available Make modules for this app will appear in front of you.
Since the goal here is to automatically add new Jira issues to your Excel spreadsheet, you need to select the “Watch Issues” module from the list.
Now, let’s see how to set up the module.
Step 2: Configuring the Jira module
To access a project’s Jira data, you will need to connect your Jira account to Make, and then create a Webhook.
Start by clicking the “Add” button under “Webhook”, and then the “Add” button next to the “Connection” selector.
The connection will require your API token along with your Service URL and Username.
You need to create an API token within your Jira account. In case you don’t know how to obtain it, please check this help doc.
Once ready, you can create a webhook that will be used to instantly receive new issues as they are created in Jira.
As for the webhook, you can name it as you wish. Then, select “Issue” under “Hook type” selector, and tick the “Created” option from the “Events” options.
After doing this, hit “Save” to continue.
The Jira webhook is now set up to receive new issues instantly.
Let’s move on to the next step in the process.
Step 3: Configuring the Excel module
Here, we will integrate Microsoft Excel and Jira.
In your scenario, you will notice a plus sign when you move your cursor next to the Jira module.
Click it, and search for the Excel app, just like you did before with the Jira app.
Since you want to add the Jira issues to an Excel sheet, select the “Add a Worksheet Row” module from the list of options.
Now, you will need to add a connection and grant some permissions so Make can do the heavy lifting for you.
After you do that, please select the workbook and worksheet where you want Jira issues added.
Note: You will need to create the workbook and worksheet before configuring the scenario.
The worksheet we are using in this example contains the following columns:
Issue ID
Date
Summary
Bugs
Total Bugs
Improvement
Total Improvements
New Features
Total New Features
Assignee
The most important thing here is to map the data from the Jira module to the corresponding fields in the Excel module.
Understandably, your sheet may have different fields so you need to map the data accordingly.
If you decide to use the same fields in your sheet, here’s how the data elements are mapped.
Issue: ID > Issue ID field
Now variable (under the Date and time tab) > Date field
Issue: fields: Summary > Summary field
Issue: fields: Assignee: displayName > Assignee field
See the image below for reference:
For the Bug, Improvement, and New Feature fields, you will have to use one of Make’s built-in functions to ensure that only if the issue type matches that field name, the text “Yes” will be entered in that field on the sheet.
If not, it will be left blank.
Eg. Issue type = Bug, the “Bug” field on the sheet = ‘Yes’ and the other two fields remain empty.
As you see in the image above, the “IF” function located under the “General Functions” tab is used here to get the desired results.
The image below shows where to find the function and other elements used.
Make sure that you change the name after the “equals to” operator to that field’s name.
The “emptystring” keyword is located under the “Text and binary functions” tab.
The three “Total” fields will be calculated in another scenario (we’ll get there soon enough!).
The first scenario is now ready to go so let’s see how to test it before creating the next one.
Step 4: Testing the first scenario
Since this scenario uses a webhook, you need to hit the “Run once” button located at the bottom left of your screen.
Then, head over to Jira and create an issue.
If your scenario is set up correctly, the results should look like this:
If you’re happy with the results, simply switch the scenario on as shown below:
Good! Your first scenario is up and running, and will send new Jira issues (along with the relevant data) to your Excel spreadsheet.
Now, let’s create the second scenario, which will calculate the totals, generate the reports and email them to you.
Second scenario: Creating and sending Excel reports via email
This scenario complements the first one by creating weekly reports with the data gathered from Jira.
After creating the reports, it also sends them to the recipients you define, sparing you from having to compile and send the data.
Of course, you can change the details of the scenario to suit your needs. For example, if you follow agile methodology and prefer bi-weekly reports rather than weekly ones, it’s perfectly doable.
Let’s take a look at how to create it.
Step 1: Calculating and updating the worksheet with the totals
In order to create the second scenario, you will need to repeat the same process.
From your dashboard, create a new scenario.
Then, search for the Excel app and select the “Watch Worksheet Rows” module.
This module will fetch all the new rows of data from the same worksheet that collects issues from Jira.
Since you already connected your Microsoft account while creating the previous scenario, select your connection, as well as the same Excel spreadsheet in the module.
Then, tick the “Skip Empty Rows” option, and in the “Limit” field enter the maximum number of issues you want to fetch when the scenario executes. The higher the number of issues your team deals with on a weekly basis, the higher the number you’ll have to input here.
Next, you will have to use one of Make’s built-in tools - the Array aggregator - to aggregate the data.
There are two reasons for aggregating the data here:
Aggregates the new Row IDs so that the correct range can be used to calculate the totals in the next module
A single email with the report will be sent with the aggregated results (instead of multiple emails for each new row)
The Array aggregator is found under the Tools menu at the bottom of the scenario builder.
Once you add it to the scenario, you will need to do:
Tick the “Row ID” and “Row” fields
Select “Show advanced settings” and tick the “Stop processing after an empty aggregation” option
After the Array Aggregator, add the “Excel > Update a Worksheet Row” module.
In this module, the Total Bugs, Total Improvements, and Total New Features are calculated by using the COUNTA function dynamically.
The function needs to be dynamic so that the calculation includes only new rows of data while excluding those rows that have already been used in previous reports.
To do this, select the “Formulas Local” option under “Types of Values Being Entered” and then the “Row ID”.
Now enter the COUNTA formula in all the “Total” fields and map the Array[1]: Row ID after the column so that it counts from the first new row.
For reference on setting this up, see the image below:
Step 2: Downloading and emailing the Excel workbook
Before we get to download the workbook, a summarized report reflecting the totals will also be provided in the body of the email.
Sometimes, this is all the information the recipients want, and by including it automatically we will spare them from having to download the workbook.
So the next module in the scenario will generate a table with the totals to be included in the body of the email.
Here, you need to add the “Text aggregator” to the scenario. You will find this feature in the Tools menu at the bottom of your screen, or by searching it in the list of available apps after you click the little plus sign on your workflow.
After attaching the “Text aggregator” module to the “Excel > Update a Worksheet Row” module, it’s time to configure it.
In the “Text aggregator” module, tick the “Show advanced settings” option and select “New Row” as the Row separator.
Some HTML will need to be used to aggregate the total weekly Bugs, Improvements, and New Features so that they can be used in the table. Of course, you can adjust it according to your specific needs.
After adding the bit of code, click “OK”.
Next, add the “Excel > Download a workbook” module.
In it, choose the “By selecting from the path” option and then the Workbook.
Click “OK” after doing this.
To conclude, add the “Gmail > Send an email” module.
Add a connection to your Gmail business account (for personal Gmail accounts, there are extra steps required).
You can use the “Now” variable in the “Subject” line to show the current date and time.
The “Content” box contains HTML to create the table mentioned earlier.
At the end of the HTML, simply map the “text” data element outputted by the “Text Aggregator” module.
If you don’t want to use the table, simply map all the data that you require from the “Excel > Upload a workbook” module to compose the email.
To attach the downloaded workbook, click “Add an attachment” and select the “Excel > Download a Workbook” option as the Source file.
Step 3: Testing the scenario
This scenario doesn’t use a webhook so you can just hit the “Run once” button to start the automation.
It should execute like this:
Here’s a closer look at the report in the email:
When you switch this scenario on, the “Schedule settings” will appear. Here, you can choose when you want this scenario to execute (once a week, once every two weeks, or whatever frequency suits you best.
Once set up, click the “Activate” button.
And that’s it!
These two scenarios will help you export issues from Jira to Excel and then report the issues automatically to the relevant stakeholders within your organization.
Conclusion
If you skimmed through the use case, you might be feeling a bit overwhelmed by all the information displayed above.
We get it - it does take a bit of work to get this automation running.
However, it’s nothing in comparison to all the hours you’ll save by having your Jira data exported to Excel automatically.
Spending a couple of hours tinkering with Make often translates into huge time savings, and Excel - Jira integrations are not the exception.
Happy automating!
