Obtaining changelog history in Excel using API

Niall Smith
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 20, 2024

Hi,

 

I am using this line in power query in excel to get the history of an issue via JIRA API

 

Table.AddColumn(#"Changed Type2", "Custom", each (Web.Contents("https://xxxxx.atlassian.net/rest/api/3/issue",[RelativePath="/" & [Key] & "changelog"])))

 

It returns a binary column but when I try to expand it I get the message "evaluating query" and then nothing happens.

 

I have tried variations such as:

& "changelog/list"]

& "changelog/list headers: {'Accept': 'application/json','Content-Type': 'application/json'}"]

These don't seem to make any difference.

 

I used a similar approach to get comments and that worked fine but it does not work with the change log

 

1 answer

0 votes
Carlos Faddul
Community Leader
Community Leader
Community Leaders are connectors, ambassadors, and mentors. On the online community, they serve as thought leaders, product experts, and moderators.
May 21, 2024

@Niall Smith , how are you ?

 

For you to do this, you need to thread your code to do two things

First > run to get all issues

Second > to get changelog issue by issue (it's more costly but works)

Niall Smith
I'm New Here
I'm New Here
Those new to the Atlassian Community have posted less than three times. Give them a warm welcome!
May 21, 2024

Hi,

 

Yes I have that. I should have mentioned the first part of the query is

 

UrlQueryPart = "?jql=project=xxxx",
JiraIDPerPage = 50,
GetJson = (Url) =>
let
RawData = Web.Contents("https://xxxxxx.atlassian.net/rest/api/3/search",[RelativePath=Url]),
Json = Json.Document(RawData)
in Json,

GetJiraIDCount = () =>
let Url = UrlQueryPart & "&maxResults=0",
Json = GetJson(Url),
Count = Json[#"total"]
in Count,

GetPage = (Index) =>
let Skip = "&startAt=" & Text.From(Index * JiraIDPerPage),
Top = "&maxResults=" & Text.From(JiraIDPerPage),
Url = UrlQueryPart & Skip & Top,
Json = GetJson(Url),
Value = Json[#"issues"]
in Value,

JiraIDCount = List.Max({ JiraIDPerPage, GetJiraIDCount() }),
PageCount = Number.RoundUp(JiraIDCount / JiraIDPerPage),
PageIndices = { 0 .. PageCount - 1 },
Pages = List.Transform(PageIndices, each GetPage(_)),
JiraID = List.Union(Pages),
Table = Table.FromList(JiraID, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(Table, "Column1", {"id", "key", "fields"}, {"Column1.id", "Column1.key", "Column1.fields"})

 

 

it is on each row that I have 

 

#"Added Custom2" = Table.AddColumn(#"Changed Type2", "Custom", each (Web.Contents("https://xxxx.atlassian.net/rest/api/3/issue",[RelativePath="/" & [Key] & "/changelog"]))),

 

I did find changelog/list needs the id's for the changes you want to get.

I have the changelog working now. Rookie error I was missing a "/" in the URL

Suggest an answer

Log in or Sign up to answer
DEPLOYMENT TYPE
CLOUD
PRODUCT PLAN
PREMIUM
TAGS
AUG Leaders

Atlassian Community Events