Create a table in Confluence from a REST API call

[#1]

Hello!

In this article I would like to show how to create a table in Confluence from a REST API call.

We will create a page in Confluence with a table which provides information about projects in Jira. We will get data for our table by a Jira public REST API call called project.

You can watch this video on this tutorial.

Install Power Scripts for Confluence

We will make our REST API call by the Power Scripts for Confluence app. It is a free app, that is why this solution is free for you.

First of all install Power Scripts for Confluence into your Confluence. You can find a detailed explanation how to install this app over here.

Write the script

Now go to cog item -> Manage apps -> SIL Manager.

Create a file called getProjects.sil and paste this code into this file:

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 http://host.docker.internal:8080/ to you Jira instance.

Run the script to check that the data are fetched from your Jira instance:

Create a Confluence page

Now create a Confluence page with the SIL table macro. Set getProjects.sil for the script field:

Publish the page and you will see the table:

Make it more complicated

Now let's add the following functionality to our table:

• fetch information about the project lead
• make the names of the columns more meaningful

First let's modify our getProjects.sil script.

We will also make some refactoring.

Our flow of actions is this one:

• fetch project data by the REST API call
• convert project data to table data
• return the result

Now let's put it to code:

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

return tableRows;

Next let's get the project data.

Our flow of actions is:

• define the request variable
• define the authentication header for the request
• define the expand url parameter. We need to take data about the project lead but in the default response from the project REST API call this parameter is not present that is why we need to modify our url to this one: http://host.docker.internal:8080/rest/api/2/project?expand=description,lead,url,projectKeys. As you can see we added the expand parameter.
• execute the request
• return the data

Here is how this function looks in 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 our structs for the table data.

Here is the response of our HTTP request (I removed all fields which are not used in our table to make the json below shorter and hence more understandable):

[
   {
      "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 you can see we have: key, name and projectTypeKey values in the first level of our json. But the lead field does not have a value in the first level, instead it has another json in the first level and the value is provided in the second level. That is why let's first define a struct for the lead's second level:

struct Lead {
    string name;
    string displayName;
}

Now we can define the first level of our json:

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

But the problem is that our SIL table macro can work only with one level of hierarchy. That is why we need to flatten our struct to one level when providing the result from our script to the SIL Table macro. That is why I created a flat struct for our table:

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

And now we have to convert our project data in the Project struct to our table data in the TableRow struct:

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;
}

And that is all we have to do!

Here is the final code of getProjects.sil:

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 refresh the Projects from Jira page and you will see two new columns with the project lead information:

But the names of the columns do not look clean. Let's give the columns more meaningful names.

Edit the page, Edit the SIL table macro and enter "Project Key, Project Name, Project Type, Project Lead, Project Lead Display Name" into the columns field:

Now save changes and publish the page:

Exactly what we wanted to get!

[psabdulkader]

can you share the procedure to return 2 Dimensional array as table  instead of using structure

[Eric Hansen]

This looked so promising... but the Power Scripts for Confluence plug-in is no longer free but more importantly is is not available for Confluence Cloud.  

[Micha Brans]

Hey @Alexey Matveev 

Do you maybe have a suggestion for another plug-in that is still free and con do the same (or simular)?

Thanks,

Micha