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.
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
Json
Alternately 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
Community SupportHi 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: Organizational
Hi ,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