Table from JSON macro - Data from Azure DevOps using PAT

[Green_ Desmond]

Hi,

I am trying to use the 'Table from JSON' macro to retrieve data from Azure DevOps, via the API endpoints exposed for query data from that platform.

I want to use a personal access token (PAT) from Azure DevOps, but I'm really not clear how to configure the macro to use this, and certainly not getting anything back into the macro, so far!

I enter the JSON URL (obviously with placeholders to not expose company specifics!), such as:

https://<devops_org>/<devops_instance>/<devops_project>/_apis/wit/wiql/1cb4290a-7faa-4bb8-8a15-8211af5e2b14

The actual endpoint returns JSON data, no problem.

I'm not sure how to enter data into the dialogs to use the PAT? I select 'Authentication type' as 'Custom' and then click on 'Change authentication header'.

In the 'Request header' dialog and field I have tried entering each of (again substituting placeholders which are for illustration here):

Bearer <Azure PAT>

Authorization: Basic <Azure PAT>

Authorization: Basic <Azure PAT Base64 encoded>

I get a 'Failed to retrieve data from URL: Request failed: 400 Bad Request

I've referred to this page: 

https://docs.stiltsoft.com/tfac/cloud/table-from-json-58427709.html

This is great, but I do find the part I need (about setting the credentials) just a bit confusing!

The instructions are about using PAT's from Jira, Bitbucket or GitLab, but I assume the same is also relevant for Azure DevOps. Point 5 confuses me, as I don't see a 'Change request header' but instead select 'Change authentication header' and enter Bearer <Azure PAT>, as I described earlier.

1. Create a personal access token in your Jira, Bitbucket or GitLab account
2. Insert the Table from JSON macro on the Confluence page
3. Add a URL to JSON.
To specify a correct URL for GitLab, visit its API documentation
4. Select the Authentication type: Custom
5. Use Bearer Auth without the username
Change request header: Bearer Your_Token
6. Save Request header

Can I have exact instructions on how to get this working, using the data center version of Confluence?

[Stiltsoft support]

Hi @Green_ Desmond ,

Seems that you are using our Table Filter, Charts & Spreadsheets for Confluence app. If it is so, then these things have caught my eye:

The request header should be written as

• Bearer <Azure PAT>
• Authorization: Basic <Azure PAT>
• Authorization: Basic <Azure PAT Base64 encoded>

The "Autorization:" part should be present when we talk about additional headers.

Then the 400 Error means that smth is wrong with the command.

As I see, you use POST request type (presumably from this page). But our macro supports only GET requests (you may check this page instead).

Also there is no {team}/ part in your request (and it is present both in POST and GET requests):

https://<devops_org>/<devops_instance>/<devops_project>/_apis/wit/wiql/1cb4290a-7faa-4bb8-8a15-8211af5e2b14

POST https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql?api-version=7.2-preview.2

GET https://dev.azure.com/{organization}/{project}/{team}/_apis/wit/wiql/{id}?api-version=7.2-preview.2

If I got you wrong and you use some other API or smth, please tell us more details. And you may always refer to our support portal that is confidential.

[Green_ Desmond]

Hi, yes you are right I used some badly named placeholders! They are indeed {organization} {project}. I just called them something different but the true substitute values were always correct. My query does not use {team}, this one is optional.

I changed the URL in the 'Table from JSON' macro to:

https://dev.azure.com/{organization}/{project}/_apis/wit/wiql/1cb4290a-7faa-4bb8-8a15-8211af5e2b14?api-version=7.2-preview.2

That URL returns JSON data, as expected.

I selected 'Change authentication header' and in the 'Request header' textbox entered:

Bearer {Azure PAT}

I still get this:

Failed to retrieve data from URL:

Request failed: 400 Bad Request

What am I doing wrong?

Also to add, the JSON which comes back is 'flat'. For example, just a starting snippet of the response is as per the below. I mention this, because when I use the full output as an attachment instead (to try and explore this in a different way, in anticipation of getting the URL route working), it seems like I am going to struggle to represent this in a tabular format anyway! Obviously I blanked out the {organization} element in this response snippet.

{"queryType":"flat","queryResultType":"workItem","asOf":"2024-10-18T15:01:24.077Z","columns":[{"referenceName":"System.Id","name":"ID","url":"https://dev.azure.com/xxx/_apis/wit/fields/System.Id"},{"referenceName":"System.Title","name":"Title","url":"https://dev.azure.com/xxx/_apis/wit/fields/System.Title"},{"referenceName":"System.State","name":"State","url":"https://dev.azure.com/xxx/_apis/wit/fields/System.State"},{"referenceName":"Microsoft.VSTS.Scheduling.Effort","name":"Effort","url":"https://dev.azure.com/xxx/_apis/wit/fields/Microsoft.VSTS.Scheduling.Effort"},{"referenceName":"Scrumban.SAFeTeams","name":"SAFe Teams","url":"https://dev.azure.com/xxx/_apis/wit/fields/Scrumban.SAFeTeams"},

[Stiltsoft support]

And what was the full request without the macro that returned your data?

[Green_ Desmond]

https://dev.azure.com/{organization}/{project}/_apis/wit/wiql/1cb4290a-7faa-4bb8-8a15-8211af5e2b14?api-version=7.2-preview.2

Obviously, where I substituted {organization} and {project} with my actual values.

[Stiltsoft support]

I can suggest you referring to our support portal and attaching the link to this thread.

There you'll be able to share your sensitive info and we'll be able to test what we see using the link and how it is processed with the macro.