io:fwrite("JIRA vs Excel\n").

ENG ( Link to Portuguese versiao )

If you work in any of hiper-big, intergalactic enterprise that is in TOP of the richest companies there I'm almost sure that you have possibility to work with the oldest JIRA and Excel that is secure enough :-].
Colleague ask me if it possible to do Excel reporting from JIRA. As must have requirement: report must have information about issue comments.
Below you can find a quick, dirty, UNSECURED PoC. Be sure you use it only in your lab. Remember, that below there is Base 64 ENCODING (not encryption).

Lets take a few assumptions:
>you can use Excel 2016,
> information are stored on local JIRA Server,
> you can use Base64 ENCODING as authentication,
> you must work @ WIN environment.

We can start be preparing Base64 string that we will use in authentication. In powershell:
$user=michal
$password=CDkew8273%34wjs8
$pair="$($user):$($pass)"
$enc64=[System.Convert]::ToBase64String([System.Text.encoding]::ASCII.GetBytes($pair))

Now we can play with Excel. In Data -> New Query -> From Other Sources choose "Blank Query". For the start you should be able to create in advance editor:
let
Source = "Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]]))"
in
Source

After that you can play with the data without the pain. The write-only method use {row-number} and [column-name] to do the filtering.

Nicer way is to use dedicated features like: Table.Transpose, Table.SelectColumns, …

List of JIRA issues id you can get as:
let
   Source = "Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]]))"
   #"Convered to Table" = Record.ToTable(Source),
   #"Transposed Table" = Table.Transpose(#"Convered to Table"),
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
   #Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"issues"}),
   issues = #"Removed Other Columns"{0}[issues],
   #"Converted to Table1" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#Converted to Table1", "Column1", {"key"}, {"Column1.id"})
in
   #"Expanded Column1"

The next step would be to create excel-like, click-oriented query where user can choose what he would like to observe (solution from: https://community.powerbi.com/t5/Desktop/JIRA-amp-PowerBI/m-p/161986):
let
   // The same query as before
   Source = Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]])),
   // Converting data from List to Table, so
   #"Converted to Table" = Record.ToTable(Source),
   // we will be able to transpose it
   #"Transposed Table" = Table.Transpose(#"Converted to Table"),
   // and make the field names the column headers
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
   // Now we are going to assign the correct data types for each column
   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),
   // And keep only the total column
   #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),
   // Now we are going to get the first (and only) record and assign it to a variable named #"total"
   #"total" = #"Removed Other Columns"{0}[total],
   // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
   #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),
   // Converting the startAt list into Table - not sure if this is really necessary
   #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   // and renaming its single column
   #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),
   // Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
   #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "http://jira.my.domain/rest/api/latest/search?&maxResults=100&jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED&startAt=" & Text.From([startAt])),
   // And finally retrieve them all
   data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_, [Headers=[Authorization="Basic value_of_enc64"]])))
in
   data

This should be the perfect moment for end but there is no comments in the query results.
After a moment with JIRA GUI you can find them in XML for each issue in: https://jira.my.domain/si/jira.issueviews:issue-xml/TEST-1021/TEST-1021.xml. The URL for each issue is so nice that we should be able to build it based on our initial list of issues:

let    Source = "Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]]))"
   #"Convered to Table" = Record.ToTable(Source),
   #"Transposed Table" = Table.Transpose(#"Convered to Table"),
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
   #Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"issues"}),
   issues = #"Removed Other Columns"{0}[issues],
   #"Converted to Table1" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Issue Key" = Table.ExpandRecordColumn(#Converted to Table1", "Column1", {"key"}, {"Column1.id"}),
   #"Insert Last Comment" = Table.AddColumn(#"Issue Key", "Last Comment",
each
   Table.Last(
     Table.SelectRows(
       Table.SelectRows(
         XML.Document(
           Web.Contents(
             Text.Combine({"https://jira.my.domain/si/jira.issueviews:issue-xml/", [Column1.id], "/", [Column1.id], ".xml"}),
             [Headers=[Authorization="Basic value_of_enc64"]]
           )
         ){0}[Value]{0}[Value], each [Name] = "item"){0}[Value],
       each [Name] = "comments"
     )[Value]{0}
   )[Value]
in
   #"Insert Last Comment"

If you will play with the code anywhere else than your lab - remember to change Base64 for OAuth or session cookie.

PT

Se trabalhas em alguma empresa hiper-grande e intergaláctica que está no TOP das empresas mais ricas de lá, tenho quase certeza de que tens a possibilidade de trabalhar com o JIRA e o Excel mais antigos que são seguros o suficiente :-].
Colega me pergunte se é possível fazer relatórios em Excel a partir do JIRA. Como deve ter o requisito: o relatório deve ter informações sobre os comentários do JIRA issue.
Abaixo, podes encontrar um PoC rápido, sujo e NÃO SEGURO. Certifique-se de usá-lo apenas em seu laboratório. Lembre-se que abaixo está a ENCODING em Base 64 (não encryption).

Vamos fazer algumas suposições: > podes usar o Excel 2016,
> as informações são armazenadas no servidor JIRA local,
> você pode usar a ENCODING Base64 como autenticação,
> deves trabalhar @ ambiente WIN..

Podemos começar preparando a string Base64 que usaremos na autenticação. No powershell::
$user=michal
$password=CDkew8273%34wjs8
$pair="$($user):$($pass)"
$enc64=[System.Convert]::ToBase64String([System.Text.encoding]::ASCII.GetBytes($pair))

Now we can play with Excel. In Data -> New Query -> From Other Sources choose "Blank Query". For the start you should be able to create in advance editor:
let
Source = "Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]]))"
in
Source

Depois disso, podes brincar com os dados sem dor. O método somente gravação usa {row-number} e [column-name] para fazer a filtragem.

A maneira mais agradável é usar recursos dedicados como: Table.Transpose, Table.SelectColumns, …

Lista de IDs de pendências do JIRA que podes obter como:
let
   Source = "Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]]))"
   #"Convered to Table" = Record.ToTable(Source),
   #"Transposed Table" = Table.Transpose(#"Convered to Table"),
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
   #Removed Other Columns" = Table.SelectColumns(#"Promoted Headers", {"issues"}),
   issues = #"Removed Other Columns"{0}[issues],
   #"Converted to Table1" = Table.FromList(issues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   #"Expanded Column1" = Table.ExpandRecordColumn(#Converted to Table1", "Column1", {"key"}, {"Column1.id"})
in
   #"Expanded Column1"

O próximo passo seria criar uma consulta orientada a cliques do tipo Excel, onde o usuário pode escolher o que gostaria de observar (solução de: https://community.powerbi.com/t5/Desktop/JIRA-amp-PowerBI/m-p/161986):
let
   // The same query as before
   Source = Json.Document(Web.contents("http://jira.my.domain/rest/api/latest/search?jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED", [Headers=[Authorization="Basic value_of_enc64"]])),
   // Converting data from List to Table, so
   #"Converted to Table" = Record.ToTable(Source),
   // we will be able to transpose it
   #"Transposed Table" = Table.Transpose(#"Converted to Table"),
   // and make the field names the column headers
   #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
   // Now we are going to assign the correct data types for each column
   #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"expand", type text}, {"startAt", Int64.Type}, {"maxResults", Int64.Type}, {"total", Int64.Type}, {"issues", type any}}),
   // And keep only the total column
   #"Removed Other Columns" = Table.SelectColumns(#"Changed Type",{"total"}),
   // Now we are going to get the first (and only) record and assign it to a variable named #"total"
   #"total" = #"Removed Other Columns"{0}[total],
   // Now it is time to build a list of startAt values, starting on 0, incrementing 100 per item
   #"startAt List" = List.Generate(()=>0, each _ < #"total", each _ +100),
   // Converting the startAt list into Table - not sure if this is really necessary
   #"Converted to Table1" = Table.FromList(#"startAt List", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
   // and renaming its single column
   #"Renamed Columns" = Table.RenameColumns(#"Converted to Table1",{{"Column1", "startAt"}}),
   // Now let's create a new column URL with the URL we are interested at. Note the maxResults and startAt parameters this time
   #"Added Custom" = Table.AddColumn(#"Renamed Columns", "URL", each "http://jira.my.domain/rest/api/latest/search?&maxResults=100&jql=project+%3D+TEST+STATUS+NOT+IN+CLOSED&startAt=" & Text.From([startAt])),
   // And finally retrieve them all
   data = List.Transform(#"Added Custom"[URL], each Json.Document(Web.Contents(_, [Headers=[Authorization="Basic value_of_enc64"]])))
in
   data

Este deve ser o momento perfeito para terminar, mas não há comentários nos resultados da consulta..
Após um momento com a GUI do JIRA, podes encontrá-los em XML para cada issue em: https://jira.my.domain/si/jira.issueviews:issue-xml/TEST-1021/TEST-1021.xml. O URL para cada problema é tão bom que devemos ser capazes de construí-lo com base em nossa lista inicial de issues:

Se tu vais jogar com o código em qualquer outro lugar que não seja seu laboratório - lembre-se de alterar Base64 para OAuth ou cookie de sessão.

Cavaleiros do Calculo λambda

ENG ( Link to Portuguese versiao )

PT