Get best of the week

Table in Atlassian Confluence based on data from a REST request

Hello!

In this article I will tell you how to make a page in Atlassian Confluence with a table, the data to which will come from a REST request.

We will create a page in Confluence with a table that will contain project data in Jira. We will get this data from Jira using the project method from the standard Jira REST API.

You can watch the video on this article here .

Install Power Scripts for Confluence


We will make a call to the Jira REST API using the Power Scripts for Confluence plugin . This is a free plugin, so this solution will not cost you anything.

Well, the first thing we need to do is install the Power Scripts for Confluence plugin in our Confluence. Detailed instructions on how to do this can be found here .

Write a script


Now go to the gear menu item -> Manage apps -> SIL Manager.



Create the getProjects.sil file with the following code:

struct Project {
    string key;
    string name;
    string projectTypeKey;
}

HttpRequest request;
HttpHeader authHeader = httpBasicAuthHeader("admin", "admin");
request.headers += authHeader;
 
Project [] projects = httpGet("http://host.docker.internal:8080/rest/api/2/project", request);
runnerLog(projects);
return projects;

Change the address of host.docker.internal : 8080 / to the address of your Jira instance.

Run the script to verify that the data is selected from Jira:



Create a page in Confluence


Now create a page in Confluence with the SIL table macro . In the scripts field, enter the name of our script getProjects.sil:



Publish the page and you will see this result:



Let's complicate the task


Add the following functionality to the table:

  • display information about the project manager
  • give the fields more understandable names and preferably in Russian

First, make changes to the getProjects.sil script.

At the same time, we refactor a little.

Here is what our script will do:

  • get project data from Jira through a call to the Jira REST API
  • we convert the received data on projects into a tabular view
  • print the result

This is how it looks in code:

Project [] projects = getProjectData();
TableRow [] tableRows =  convertProjectDataToTableData(projects);

return tableRows;

Now let's specify how we get project data:

  • create a request
  • create a header in the request with information about the user who receives data from Jira
  • Add the expand parameter to our query. We need to select data about the project manager, but in the default answer there is no such data. Therefore, we need to tell Jira that we want to see the data about the project manager in the response. For this, the expand parameter is used.
  • we fulfill the request
  • return data

But the words turned into code:

function getProjectData() {
    HttpRequest request;
    HttpHeader authHeader = httpBasicAuthHeader("admin", "admin");
    request.headers += authHeader;
    HttpQueryParam param = httpCreateParameter("expand", "description,lead,url,projectKeys");
    request.parameters += param;
    Project[] projects = httpGet("http://host.docker.internal:8080/rest/api/2/project", request);
    return projects;
}

Now let's define data structures for our projects.

Here is the answer that the project method returns from the Jira REST API (I deleted unnecessary data to make the answer shorter and therefore more readable):

[
   {
      "key":"KAN",
      "lead":{
         "name":"admin",
         "displayName":"Alexey Matveev",
      },
      "name":"kanban",
      "projectTypeKey":"software"
   },
   {
      "key":"SCRUM",
      "description":"",
      "lead":{
         "name":"admin",
         "displayName":"Alexey Matveev",
      },
      "name":"scrum",
      "projectTypeKey":"software"
   }
]

As we can see, the values ​​for the key, name and projectTypeKey fields are defined at the first level of our json. But for the lead field, instead of the value, we see json. And this json already contains the values ​​of the name and displayName fields. Therefore, first we create a structure for json in the lead (Lead) field:

struct Lead {
    string name;
    string displayName;
}

Now we are ready to make the structure for the first level of our json (Project):

struct Project {
    string key;
    string name;
    string projectTypeKey;
    Lead lead;
}

But the problem is that the SIL table macro can only work with json with one level of nesting, so we need to convert our structure with two levels of nesting (Project) into a structure with one level of nesting (flat structure). But first, create a flat structure (TableRow):

struct TableRow {
    string key;
    string name;
    string projectTypeKey;
    string lead;
    string leadDisplayName;
}

And now we’ll write a function to convert the data in the Project structure to the TableRow structure:

function convertProjectDataToTableData(Project [] projectData) {
    TableRow [] tableRows;
    for (Project project in projectData) {
        TableRow tableRow;
        tableRow.key = project.key;
        tableRow.name = project.name;
        tableRow.projectTypeKey = project.projectTypeKey;
        tableRow.lead = project.lead.name;
        tableRow.leadDisplayName = project.lead.displayName;
        tableRows = arrayAddElement(tableRows, tableRow);
    } 
    return tableRows;
}

All. The script is ready!

Here is the final getProjects.sil code:

struct Lead {
    string name;
    string displayName;
}

struct Project {
    string key;
    string name;
    string projectTypeKey;
    Lead lead;
}

struct TableRow {
    string key;
    string name;
    string projectTypeKey;
    string lead;
    string leadDisplayName;
}

function getProjectData() {
    HttpRequest request;
    HttpHeader authHeader = httpBasicAuthHeader("admin", "admin");
    request.headers += authHeader;
    HttpQueryParam param = httpCreateParameter("expand", "description,lead,url,projectKeys");
    request.parameters += param;
    string pp = httpGet("http://host.docker.internal:8080/rest/api/2/project", request);
    runnerLog(pp);
    Project[] projects = httpGet("http://host.docker.internal:8080/rest/api/2/project", request);
    return projects;
}

function convertProjectDataToTableData(Project [] projectData) {
    TableRow [] tableRows;
    for (Project project in projectData) {
        TableRow tableRow;
        tableRow.key = project.key;
        tableRow.name = project.name;
        tableRow.projectTypeKey = project.projectTypeKey;
        tableRow.lead = project.lead.name;
        tableRow.leadDisplayName = project.lead.displayName;
        tableRows = arrayAddElement(tableRows, tableRow);
    } 
    return tableRows;
}Project [] projects = getProjectData();
TableRow [] tableRows =  convertProjectDataToTableData(projects);

return tableRows;

Now we’ll refresh the page in Confluence and see that our data on the project manager pulled up:



But the column names are somehow unintelligible. Let's give more beautiful names.

We edit the page, Edit the SIL table macro and enter “Project Key, Project Name, Project Type, Project Manager, Name of the Project Manager” in the columns field:



Save the page and here is the result:



Everything turned out!

More articles:


All Articles