BlogUse Cases

How to Export From Jira to Excel Using Integromat [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 that way - neither boring, nor 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 Integromat.

In this use case, we will show you how to export from Jira to Excel automatically. Plus, we added a second Integromat 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:

  • An Integromat 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!

excel-spreadsheet-used-to-store-jira-data


First scenario: Adding Jira issues to Excel

The first Integromat 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 Integromat account.

On your dashboard, click the “Create a new scenario” button on the top right corner.

the-create-a-scenario-button-on-the-dashboard


You will be taken to the scenario builder, which is where you will create your automated scenarios.

the-scenario-builder


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 Integromat modules for this app will appear in front of you.

available-jira-modules


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.

jira-watch-issues-module


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 Integromat, and then create a Webhook.

Start by clicking the “Add” button under “Webhook”, and then the “Add” button next to the “Connection” selector.

connecting-jira-to-integromat


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 from Jira.

creating-a-connection to-integromat


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.

creating-a-webhook


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.

the-excel-add-a-worksheet-row-module


Now, you will need to add a connection and grant some permissions so Integromat can do the heavy lifting for you.

excel-connection-to-integromat


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.

workbook-and-worksheet-selection


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:

excel-module-mapping


For the Bug, Improvement and New Feature fields, you will have to use one of Integromat’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.

if-function-used-in-excel-module


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.

if-function-configuration


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:

scenario-1-execution


If you’re happy with the results, simply switch the scenario on as shown below:

scenario-switch-button


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 for 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.

excel-watch-worksheet-rows-module


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.

the-watch-worksheet-rows-module-configuration


Next, you will have to use one of Integromat’s built-in tools - the Array aggregator - to aggregate the data.

There are two reasons for aggregating the data here:

  1. Aggregates the new Row IDs so that the correct range can be used to calculate the totals in the next module
  1. 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.

array-aggregator-under-tools-menu


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

array-aggregator-configuration


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”.

excel-update-a-worksheet-row


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:

counta-function-in-the-module


Step 2: Downloading and emailing the Excel workbook

Before we get to downloading 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.

text-aggregator-in-the-tools-menu


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.

text-aggregator-configuration


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”.

html-and-total-data-elements


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.

excel-download-a-workbook-module-configuration


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.

gmail-module-configuration


To attach the downloaded workbook, click “Add an attachment” and select the “Excel > Download a Workbook” option as the Source file.

gmail-add-an-attachment-field


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:

scenario-execution-2


Here’s a closer look at the report in the email:

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.

scheduled-setting


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 Integromat often translates into huge time savings, and Excel - Jira integrations are not the exception.

If, on the other hand, you successfully went through the steps and are thirsty for more, you can start by looking at our recent Excel automation and automated reporting articles.

Once you get a grip of Integromat, there’s no turning back.

Happy automating!

Do you like this article?
Don't forget to share it!
Share

Get started now!

Sign up for a free Integromat account today!