Integrating Jira Filters with Excel – RightStar

Do you regularly download the same Jira filters to Excel to create pivot tables and reports ? If you do, you need to know how to use Excel ’ s built-in ability to retrieve CSV data directly from the Web and how to use it to make updating these reports simple and fast. As an example, I created a modern stick out using the sample data from the Jira template. I then created a filter to return all of the issues for this modern project. ultimately, I created an Excel spreadsheet to retrieve these rows. The final resultant role looks like this : Final imported spreadsheet I can nowadays update this spreadsheet whenever I want, just by pressing the “ Refresh All ” button on the Data tab. I can even set the worksheet to auto-refresh every time I open it.

here are the steps to get this work for you .

Create your filter

You may already have a percolate set up. If you don ’ t, create a new one in Jira. If you don ’ triiodothyronine know how to create a percolate, see this documentation. Make indisputable that you have identified the column that you want to import into Excel. You can import all customs fields if you want. For most projects and reports, that is overkill. rather, define the specific column that you truly need .

Test your filter

Before you start to integrate your filter into Excel, make surely that you are actually getting the datum that you need. Go to the Export button and download CSV ( Current Fields ). then open it in Excel. Make certain that these are the right column. If they aren ’ deoxythymidine monophosphate, fix them in your filter and test until you are satisfied that you have everything that you need .

Create the Excel connection

now that you have a filter that you have tested, it is prison term to create the connection between Excel and Jira. Go binding to the Export menu and right-click on the CSV ( Current Fields ) choice Capturing the link address I am using Chrome. If you are using a different browser, your menu options will look different. careless, you want to Copy Link Address. When I do it in my test environment, I get hypertext transfer protocol : // : searchrequest-csv-current-fields/10500/SearchRequest-10500.csv Your link will be similar but the base url and the percolate id will be different. Open a new Excel workbook, go to the Data tab key and cluck on From Web. Setting the URL into the Spreadsheet If you are on Jira Cloud, you will need to provide your username and password to access the filter. Use the basic authentication : The Advanced Dialog If you are on Jira Server, you will need to click on the Advanced button to this dialogue box paste the URL that you copied into the URL parts section at the top.

then add two HTTP request headers :

  • accept : text/csv
  • mandate : basic

Base64 token

Lets take a short circuit digression to explain the base64 token. When you execute the URL, you need to pass in login credentials. This requires a extra format, called a base64 keepsake. You create this token by “ base64 encoding ” the string :. The “:” between username and password is required. For example, if your username is “janedoe” and your password is “doeadeer”, then the string you need to encode is “janedoe:doeadeer” If you are running Windows, hera is a simple way to get the token that you will need. Run this in a control window ( cmd.exe ) : powershell “ [ convert ] : :ToBase64String ( [ Text.Encoding ] : :UTF8.GetBytes ( \ ” janedoe : doeadeer\ ” ) ) ” Make indisputable that you change the string in this example to your username and password. This results in a string that looks like this : amFuZWRvZTpkb2VhZGVlcg== Take that nominal that you generated and put it into the Authorization Header as “ Basic ” (see the above image). When you click “ Ok ”, you will see something like this : Import Preview This is a fond download of your data. You can check to make sure that it looks chastise. Your last pace is to click “ Load ” now you have the spreadsheet loaded


You can manually refresh this filters by clicking on “ Refresh All ” on the Data check in Excel. You can besides set your spreadsheet to reload the data whenever you open the spreadsheet. To do that, pawl on the down-arrow on the “ Refresh All ” menu. In the properties menu, click on “ Refresh data when opening the file ” This will cause the connection to be refreshed automatically. now that you have the raw data, you can create Pivot Tables, Charts and other reports in Excel .

reference : https://coinselected
Category : coin 4u

Leave a Reply

Your email address will not be published.