Forum Discussion
OAuth setup for Jira - Power BI Integration
let
ClientId = client_id,
ClientSecret = client_secret,
BoardId = "856",
MaxResults = 100,TokenUrl = "https://auth.atlassian.com/oauth/token",
TokenBody =
"grant_type=client_credentials" &
"&client_id=" & ClientId &
"&client_secret=" & ClientSecret &
"&scope=" & Uri.EscapeDataString("read:jira-work read:jira-user"),TokenResponse =
Json.Document(
Web.Contents(
TokenUrl,
[
Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(TokenBody)
]
)
),AccessToken = TokenResponse[access_token],
CloudList =
Json.Document(
Web.Contents(
"https://api.atlassian.com/oauth/token/accessible-resources",
[
Headers=[
Authorization="Bearer " & AccessToken,
Accept="application/json"
]
]
)
),CloudId = CloudList{0}[id],
GetPage = (startAt as number) as record =>
let
QueryString = Uri.BuildQueryString(
[
maxResults = Text.From(MaxResults),
startAt = Text.From(startAt)
]
),FullUrl =
"https://api.atlassian.com/ex/jira/" &
CloudId &
"/rest/agile/1.0/board/" & BoardId & "/issue?" &
QueryString,Raw =
Web.Contents(
FullUrl,
[
Headers=[
Accept="application/json",
Authorization="Bearer " & AccessToken
]
]
),Json = Json.Document(Raw)
in
Json,First = GetPage(0),
PageSize = First[maxResults],
Total = First[total],PageCount = Number.RoundUp(Total / PageSize),
IndexList = {0 .. PageCount - 1},AllPages =
List.Transform(
IndexList,
each GetPage(_ * PageSize)[issues]
),AllIssues = List.Combine(AllPages),
Table0 = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Column1"}),
Final = Table.ExpandRecordColumn(Table0, "Column1")
in
Final
FYI : As Suggested In the above screenshot to Replace with your the client_id , client_secret and BoardId.Hope this helps!!
Thank You.
Super UserHi ck_data_analyst,
Please see attached example file for connecting to Jira using Power Query and basic authentication using a user email address and user API token.
## Basic Authentication Setup
1. JiraEmail
Your Atlassian account email address.
---
2. ApiToken
Go to: https://id.atlassian.com/manage-profile/security/api-tokens
Click Create API token or Create API token with scopes, give it a name, and copy the token.
---
3. CloudId
Go to: https://<tenant_name>.atlassian.net/_edge/tenant_info
Replace <tenant_name> with your Jira subdomain.
Copy the cloudId value from the result.
---
4. JiraConfig
Set AuthMethod = "Basic" in Power Query in the JiraConfig and fill in the three values above.
Unfortunately I have not yet been able to get OAuth 2.0 authentication working.
Hope this helps. If so, please give kudos 👍 and mark as Accepted Solution ✔️ to help others.
v-aatheequeCommunity Support
Hi ck_data_analyst
As suggested by nielsvdc is the issue is resolved ?- v-aatheeque
Hi ck_data_analyst
Have you had a chance to look through the responses shared earlier? If anything is still unclear, we’ll be happy to provide additional support.- v-aatheeque
Hi ck_data_analyst
We wanted to follow up to check if you’ve had an opportunity to review the previous responses. If you require further assistance, please don’t hesitate to let us know.
Hi v-aatheeque ,
Issue is not resolved yet. The method that nielsvdc shared is Basic Authentication which is not accepted by my client. I have to use only OAuth. So still trying to figure it out. Thanks
- v-aatheeque
let
ClientId = client_id,
ClientSecret = client_secret,
BoardId = "856",
MaxResults = 100,TokenUrl = "https://auth.atlassian.com/oauth/token",
TokenBody =
"grant_type=client_credentials" &
"&client_id=" & ClientId &
"&client_secret=" & ClientSecret &
"&scope=" & Uri.EscapeDataString("read:jira-work read:jira-user"),TokenResponse =
Json.Document(
Web.Contents(
TokenUrl,
[
Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(TokenBody)
]
)
),AccessToken = TokenResponse[access_token],
CloudList =
Json.Document(
Web.Contents(
"https://api.atlassian.com/oauth/token/accessible-resources",
[
Headers=[
Authorization="Bearer " & AccessToken,
Accept="application/json"
]
]
)
),CloudId = CloudList{0}[id],
GetPage = (startAt as number) as record =>
let
QueryString = Uri.BuildQueryString(
[
maxResults = Text.From(MaxResults),
startAt = Text.From(startAt)
]
),FullUrl =
"https://api.atlassian.com/ex/jira/" &
CloudId &
"/rest/agile/1.0/board/" & BoardId & "/issue?" &
QueryString,Raw =
Web.Contents(
FullUrl,
[
Headers=[
Accept="application/json",
Authorization="Bearer " & AccessToken
]
]
),Json = Json.Document(Raw)
in
Json,First = GetPage(0),
PageSize = First[maxResults],
Total = First[total],PageCount = Number.RoundUp(Total / PageSize),
IndexList = {0 .. PageCount - 1},AllPages =
List.Transform(
IndexList,
each GetPage(_ * PageSize)[issues]
),AllIssues = List.Combine(AllPages),
Table0 = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Column1"}),
Final = Table.ExpandRecordColumn(Table0, "Column1")
in
Final
FYI : As Suggested In the above screenshot to Replace with your the client_id , client_secret and BoardId.Hope this helps!!
Thank You.
