Forum Discussion
OAuth setup for Jira - Power BI Integration
Hi everyone,
I'm looking for some guidance on an OAuth setup issue I'm currently facing for Jira to Power BI integration. I'm in the process of switching my dashboard from Basic Auth to OAuth using Client Credentials. The OAuth token generation is working fine in Postman but I am unable to connect in Power BI. I have cleared the cache credentials and updated the Power Query code to use OAuth (Bearer token handled inside M). If anyone has experience with the OAuth setup for Jira Cloud in Power BI or has dealt with similar issues, I'd really appreciate any pointers on what else I may need to check.
Thanks in advance for your help.
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.
12 Replies
- nielsvdc
Super User
Hi ck_data_analyst,
Did you have a look this post https://community.fabric.microsoft.com/t5/Service/Export-Jira-V3-api-data-to-PowerBI-dataflow/m-p/4828997 ?
And if you are willing to pay for a Power BI connector, have a look at this: Power BI Connector for Jira | Atlassian Marketplace
Hi nielsvdc,
My issue is not related to that topic(pagination). I am trying to get the data from Jira using OAuth to PowerBI. Eventhough I have given the correct credentials it says we couldn't authenticate with the credentials provided. Also I set both https://auth.atlassian.com and Jira base URL to Anonymous + Organizational.
I have used the below code:
let
//--------------------------------------------
// CONFIG
//--------------------------------------------
JiraBaseUrl = #"Jira instance URL",
BoardId = "856",
jira_expand = "renderedFields",
jira_maxResults = 1000,//--------------------------------------------
// OAUTH TOKEN
//--------------------------------------------
ClientId = ClientID,
ClientSecret = Client_Secret,TokenUrl = "https://auth.atlassian.com/oauth/token",
TokenBody =
"grant_type=client_credentials" &
"&client_id=" & ClientId &
"&client_secret=" & ClientSecret,TokenResponse =
Web.Contents(
TokenUrl,
[
Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(TokenBody)
]
),
AccessToken = Json.Document(TokenResponse)[access_token],//--------------------------------------------
// SERVICE SAFE CALL
//--------------------------------------------
GetJson = (startAt as number) =>
let
QueryString =
Uri.BuildQueryString(
[
expand = jira_expand,
maxResults = Text.From(jira_maxResults),
startAt = Text.From(startAt)
]
),FullUrl =
JiraBaseUrl &
"/rest/agile/1.0/board/" & BoardId & "/issue?" &
QueryString,Raw =
Web.Contents(
FullUrl,
[
Headers=[
Accept="application/json",
Authorization="Bearer " & AccessToken
]
]
),Json = Json.Document(Raw)
in
JsonAlternately if I change the TokenUrl="https://auth.atlassian.com"
Then I am getting error as DataSource.Error: The downloaded data is HTML, which isn't the expected type. The URL may be wrong or you might not have provided the right credentials to the server.
Not able to understand how to fix this.
Any help would be greatly appreciated. Thanks in advance
v-aatheequeCommunity Support
Hi ck_data_analyst
Atlassian does support Client Credentials (2‑legged OAuth / 2LO) for service accounts BUT Jira Cloud does NOT allow you to use that token directly against your site’s base URL:
https://yourdomain.atlassian.net/rest/agile/1.0/...
Client‑credentials tokens only work with the Atlassian Cloud (Unified) API:
https://api.atlassian.com/ not with yourInstance.atlassian.net.
Please follow below steps.
OAuth 2.0 flow for Jira Cloud (Client Credentials).
1. Get Access Token.
2. Get your CloudID
Call:
GET https://api.atlassian.com/oauth/token/accessible-resources
Authorization: Bearer <access_token>
This returns:
[
{
"id": "your-cloud-id",
"url": "https://yourdomain.atlassian.net",
"name": "Jira Software",
...
}
]
3. Call Jira APIs using the Cloud API gateway, NOT your Jira URL
https://api.atlassian.com/ex/jira/{cloudId}/rest/agile/1.0/board/{boardId}/issue?...
Please refer below M code.
let
//--------------------------------------------
// CONFIG (EDIT THESE)
//--------------------------------------------
ClientId = "YOUR_CLIENT_ID",
ClientSecret = "YOUR_CLIENT_SECRET",
BoardId = "856",
JiraScope = "read:jira-work read:jira-user", // adjust if needed
//--------------------------------------------
// TOKEN ENDPOINT (Atlassian OAuth 2.0)
//--------------------------------------------
TokenUrl = "https://auth.atlassian.com/oauth/token",
TokenBody =
"grant_type=client_credentials" &
"&client_id=" & ClientId &
"&client_secret=" & ClientSecret &
"&scope=" & Uri.EscapeDataString(JiraScope),
TokenResponse =
Json.Document(
Web.Contents(
TokenUrl,
[
Headers=[#"Content-Type"="application/x-www-form-urlencoded"],
Content=Text.ToBinary(TokenBody)
]
)
),
AccessToken = TokenResponse[access_token],
//--------------------------------------------
// STEP 2: GET CLOUD ID
// Required because Jira Cloud APIs must be called via api.atlassian.com
//--------------------------------------------
CloudList =
Json.Document(
Web.Contents(
"https://api.atlassian.com/oauth/token/accessible-resources",
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
),
CloudId = CloudList{0}[id],
//--------------------------------------------
// PAGED CALL FUNCTION
//--------------------------------------------
PageSize = 100,
GetPage = (StartAt as number) as record =>
let
Url =
"https://api.atlassian.com/ex/jira/" &
CloudId &
"/rest/agile/1.0/board/" & BoardId &
"/issue?maxResults=" & Number.ToText(PageSize) &
"&startAt=" & Number.ToText(StartAt),
Response =
Json.Document(
Web.Contents(
Url,
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
)
in
Response,
//--------------------------------------------
// LOOP THROUGH ALL PAGES
//--------------------------------------------
FirstPage = GetPage(0),
Total = FirstPage[total],
Pages = List.Generate(
() => FirstPage,
each _ <> null and Record.HasFields(_, "issues"),
each
let
NextStart = _[startAt] + _[maxResults],
NextPage = if NextStart < Total then GetPage(NextStart) else null
in
NextPage
),
//--------------------------------------------
// COMBINE ALL ISSUES INTO A TABLE
//--------------------------------------------
AllIssues = List.Combine(
List.Transform(
List.RemoveNulls(Pages),
each _[issues]
)
),
IssuesTable = Table.FromList(AllIssues, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
Expanded = Table.ExpandRecordColumn(IssuesTable, "Column1")
in
Expanded
Note: Permissions / Privacy Levels in Power BI:
Use:
Authentication: Anonymous
Privacy Level: OrganizationalHi ,Thank you for the response.
I have tried the method as you said but still it says we couldn't authenticate the credentials provided.
Below is the code that I tried:
let
//--------------------------------------------
// CONFIG
//--------------------------------------------
JiraBaseUrl = #"Jira instance URL",
BoardId = "856",
MaxResults = 100,ClientId = client_id,
ClientSecret = client_secret,//--------------------------------------------
// STEP 1 — OAUTH 2.0 TOKEN
//--------------------------------------------
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],
//--------------------------------------------
// STEP 2 — GET CLOUD ID
//--------------------------------------------
CloudList =
Json.Document(
Web.Contents(
"https://api.atlassian.com/oauth/token/accessible-resources",
[
Headers = [
Authorization = "Bearer " & AccessToken,
Accept = "application/json"
]
]
)
),CloudId = CloudList{0}[id],
//--------------------------------------------
// STEP 3 — PAGED CALL FOR ISSUES
//--------------------------------------------
GetPage = (startAt as number) =>
let
QueryString =
Uri.BuildQueryString(
[
expand = "renderedFields",
maxResults = Text.From(MaxResults),
startAt = Text.From(startAt)
]
),// CALL THROUGH CLOUD API GATEWAY
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,//--------------------------------------------
// STEP 4 — PAGINATION
//--------------------------------------------
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),
//--------------------------------------------
// STEP 5 — TABLE + FIELD EXPANSION (YOUR ORIGINAL FIELDS)
//--------------------------------------------
Table0 = Table.FromList(AllIssues, Splitter.SplitByNothing(), {"Column1"}),Also I have added permissions as:Authentication: Anonymous
Privacy Level: OrganizationalThanks
- nielsvdc
Hi 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-aatheeque
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.
