Hey, I'm trying to write user macro for confluence that run CQL search and the results will show in a table with columns. I have written a user macro that gets all child pages form parent and divide then to columns ## @noparams #set ( $descendantPages = $pageManager.getDescendents($content) ) #set( $urlbase = "http://test.com" ) Page name Page URL Created by Last updated by Last update date #foreach ( $page in $descendantPages ) $page.title $urlbase${req.contextPath}$page.urlPath $page.creator.name $content.getLastModifierName() $action.dateFormatter.format($page.lastModificationDate) #end I need that the CQL search will be -> cql=macroName:widget Thanks!

nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

User macro with CQL result

Neta Elyakim

Hey,

I'm trying to write user macro for confluence that run CQL search and the results will show in a table with columns.

I have written a user macro that gets all child pages form parent and divide then to columns

## @noparams
#set ( $descendantPages = $pageManager.getDescendents($content) )
#set( $urlbase = "http://test.com" )
<table>
<tr>
<th>Page name</th>
<th>Page URL</th>
<th>Created by</th>
<th>Last updated by</th>
<th>Last update date</th>
</tr>
 #foreach ( $page in $descendantPages )
 <tr>
 <td>$page.title</td>
 <td>$urlbase${req.contextPath}$page.urlPath</td>
 <td> $page.creator.name </td>
 <td> $content.getLastModifierName()</td>
<td>$action.dateFormatter.format($page.lastModificationDate)</td>
</tr>
 #end
</table>

I need that the CQL search will be -> cql=macroName:widget

Thanks!

Accepted answers

Stephen Deutsch

Hi Neta,

This is actually more difficult than you might expect from a user macro. I wrote something very hacky that piggybacks on the results of the "Content By Label" macro and is pretty slow, but maybe it will work for you:

## @param CQL:title=CQL Query|type=string|required=true|default=type = page and |desc=Enter CQL Query here, use pageId() to refer to current page ID and spaceKey() to refer to current space key.
#set ( $containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager') )
#set ( $getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null) )
#set ( $containerManager = $getInstanceMethod.invoke(null,null) )
#set ( $containerContext = $containerManager.containerContext )
#set ( $pageManager = $containerContext.getComponent('pageManager') )
#set ( $contentEntityManager = $containerContext.getComponent('contentEntityManager') )
#set ( $long = $generalUtil.getSystemStartupTime() )
#set ( $cql = $paramCQL.replace("pageId()", $content.id.toString()).replace("spaceKey()", $space.key) )
#set ( $htmlString = $action.getHelper().renderConfluenceMacro("{contentbylabel:showLabels=false|showSpace=false|max=500|cql=$cql}") )
#set ( $tableListings = "" )
#set ( $displayMatch = $req.contextPath + '/display/' )
#set ( $pagesMatch = $req.contextPath + '/pages/' )
#set ( $results = 0)
#foreach ( $linkPart in $htmlString.split('href="') )
 #if ( $linkPart.startsWith($displayMatch) || ($linkPart.startsWith($pagesMatch) && !$linkPart.contains('focusedCommentId=')) )
  #set ( $results = $results + 1 )
  #if ( $linkPart.startsWith($displayMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #set ( $spaceAndPage = $link.replace($displayMatch, "" ) )
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $spaceKey = $part )
     #break
    #end
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $pageTitle = $generalUtil.urlDecode($part) )
    #end
    #set ( $page = $pageManager.getPage($spaceKey, $pageTitle) )
    #break
   #end
  #end
  #if ( $linkPart.startsWith($pagesMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #foreach ( $section in $link.split('pageId=') )
     #set ( $pageId = $section )
    #end
    #break
   #end
   #set ( $pageId = $long.parseLong($pageId) )
   #set ( $page = $contentEntityManager.getById($pageId) )
  #end
  #set ( $tableListings = $tableListings + '<tr>' )
  #set ( $tableListings = $tableListings + '<td><a href="' + $req.contextPath + $page.urlPath + '">' + $page.title + '</a></td>')
  #set ( $tableListings = $tableListings + '<td>' + $page.creator.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.creationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $page.lastModifier.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.lastModificationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '</tr>' )
  #set ( $page = "" )
 #end
#end
#if ( $tableListings.contains("tr") )
 <p> Found $results results: </p>
 <table>
  <tr>
   <th>Page</th>
   <th>Created By</th>
   <th>Created Date</th>
   <th>Last Modified By</th>
   <th>Last Modified Date</th>
  </tr>
  $tableListings
 </table>
#else
 <p> No results found for CQL Query $cql </p>
#end

All comments

Stephen Deutsch

Hi Neta,

## @param CQL:title=CQL Query|type=string|required=true|default=type = page and |desc=Enter CQL Query here, use pageId() to refer to current page ID and spaceKey() to refer to current space key.
#set ( $containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager') )
#set ( $getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null) )
#set ( $containerManager = $getInstanceMethod.invoke(null,null) )
#set ( $containerContext = $containerManager.containerContext )
#set ( $pageManager = $containerContext.getComponent('pageManager') )
#set ( $contentEntityManager = $containerContext.getComponent('contentEntityManager') )
#set ( $long = $generalUtil.getSystemStartupTime() )
#set ( $cql = $paramCQL.replace("pageId()", $content.id.toString()).replace("spaceKey()", $space.key) )
#set ( $htmlString = $action.getHelper().renderConfluenceMacro("{contentbylabel:showLabels=false|showSpace=false|max=500|cql=$cql}") )
#set ( $tableListings = "" )
#set ( $displayMatch = $req.contextPath + '/display/' )
#set ( $pagesMatch = $req.contextPath + '/pages/' )
#set ( $results = 0)
#foreach ( $linkPart in $htmlString.split('href="') )
 #if ( $linkPart.startsWith($displayMatch) || ($linkPart.startsWith($pagesMatch) && !$linkPart.contains('focusedCommentId=')) )
  #set ( $results = $results + 1 )
  #if ( $linkPart.startsWith($displayMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #set ( $spaceAndPage = $link.replace($displayMatch, "" ) )
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $spaceKey = $part )
     #break
    #end
    #foreach ( $part in $spaceAndPage.split("/") )
     #set ( $pageTitle = $generalUtil.urlDecode($part) )
    #end
    #set ( $page = $pageManager.getPage($spaceKey, $pageTitle) )
    #break
   #end
  #end
  #if ( $linkPart.startsWith($pagesMatch) )
   #foreach ( $link in $linkPart.split('"') )
    #foreach ( $section in $link.split('pageId=') )
     #set ( $pageId = $section )
    #end
    #break
   #end
   #set ( $pageId = $long.parseLong($pageId) )
   #set ( $page = $contentEntityManager.getById($pageId) )
  #end
  #set ( $tableListings = $tableListings + '<tr>' )
  #set ( $tableListings = $tableListings + '<td><a href="' + $req.contextPath + $page.urlPath + '">' + $page.title + '</a></td>')
  #set ( $tableListings = $tableListings + '<td>' + $page.creator.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.creationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $page.lastModifier.fullName + '</td>' )
  #set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.lastModificationDate) + '</td>' )
  #set ( $tableListings = $tableListings + '</tr>' )
  #set ( $page = "" )
 #end
#end
#if ( $tableListings.contains("tr") )
 <p> Found $results results: </p>
 <table>
  <tr>
   <th>Page</th>
   <th>Created By</th>
   <th>Created Date</th>
   <th>Last Modified By</th>
   <th>Last Modified Date</th>
  </tr>
  $tableListings
 </table>
#else
 <p> No results found for CQL Query $cql </p>
#end

Shankar Kalaga

Hi @Stephen Deutsch , Can you please let me know how to add Labels column in the table data? I was trying to insert table column but unable to get the label data in column. It would be a great help if you can give the code with label column.

Shankar Kalaga

Hi @Stephen Deutsch , Actually I used below code to search specific text in a table data.

I am able to pull the data in tabular format and able to search text, but I added a column to pull "Label" associated to pages in table. Below is the code I am using. I am unable to pull the label names in table.

## Macro Name: filter-all-tables
## Macro Title: Filter all tables
## Category: Confluence Content
## Macro has a body: N
## Body processing: None
##
## Source: https://community.atlassian.com/t5/Confluence-articles/The-Admin-s-tale-User-Macro-filtering-a-Confluence-table/ba-p/459369
## Date implemented: yyyy-mm-dd
## Implemented by: xxx.yyy@zzz.com

##-----------

## @param CQL:title=CQL Query|type=string|required=true|default=type = page and |desc=Enter CQL Query here, use pageId() to refer to current page ID and spaceKey() to refer to current space key.
#set ( $containerManagerClass = $content.class.forName('com.atlassian.spring.container.ContainerManager') )
#set ( $getInstanceMethod = $containerManagerClass.getDeclaredMethod('getInstance',null) )
#set ( $containerManager = $getInstanceMethod.invoke(null,null) )
#set ( $containerContext = $containerManager.containerContext )
#set ( $pageManager = $containerContext.getComponent('pageManager') )
#set ( $contentEntityManager = $containerContext.getComponent('contentEntityManager') )
#set ( $labelManager = $containerContext.getComponent("labelManager"))
#set ($labelManager = $action.getLabelManger())
#set ($labelManager.getLabel($paramLabel))

#set ( $long = $generalUtil.getSystemStartupTime() )
#set ( $cql = $paramCQL.replace("pageId()", $content.id.toString()).replace("spaceKey()", $space.key) )
#set ( $htmlString = $action.getHelper().renderConfluenceMacro("{contentbylabel:showLabels=false|showSpace=false|max=500|cql=$cql}") )
#set ( $tableListings = "" )
#set ( $displayMatch = $req.contextPath + '/display/' )
#set ( $pagesMatch = $req.contextPath + '/pages/' )
#set ( $results = 0)
#foreach ( $linkPart in $htmlString.split('href="') )
#if ( $linkPart.startsWith($displayMatch) || ($linkPart.startsWith($pagesMatch) && !$linkPart.contains('focusedCommentId=')) )
#set ( $results = $results + 1 )
#if ( $linkPart.startsWith($displayMatch) )
#foreach ( $link in $linkPart.split('"') )
#set ( $spaceAndPage = $link.replace($displayMatch, "" ) )
#foreach ( $part in $spaceAndPage.split("/") )
#set ( $spaceKey = $part )
#break
#end
#foreach ( $part in $spaceAndPage.split("/") )
#set ( $pageTitle = $generalUtil.urlDecode($part) )
#end
#set ( $page = $pageManager.getPage($spaceKey, $pageTitle) )
#break
#end
#end
#if ( $linkPart.startsWith($pagesMatch) )
#foreach ( $link in $linkPart.split('"') )
#foreach ( $section in $link.split('pageId=') )
#set ( $pageId = $section )
#end
#break
#end
#set ( $pageId = $long.parseLong($pageId) )
#set ( $page = $contentEntityManager.getById($pageId) )
#end
#set ( $tableListings = $tableListings + '<tr>' )
#set ( $tableListings = $tableListings + '<td><a href="' + $req.contextPath + $page.urlPath + '">' + $page.title + '</a></td>')
#set ( $tableListings = $tableListings + '<td>' + $page.creator.fullName + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $page.labelname + '</td>' )

#set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.creationDate) + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $page.lastModifier.fullName + '</td>' )
#set ( $tableListings = $tableListings + '<td>' + $action.dateFormatter.formatDateTime($page.lastModificationDate) + '</td>' )
#set ( $tableListings = $tableListings + '</tr>' )
#set ( $page = "" )
#end
#end
#if ( $tableListings.contains("tr") )
<p> Found $results results: </p>
<table>
<tr>
<th>Page</th>
<th>Created By</th>
<th>LabelName</th>
<th>Created Date</th>
<th>Last Modified By</th>
<th>Last Modified Date</th>
</tr>
$tableListings
</table>
#else
<p> No results found for CQL Query $cql </p>
#end

Shankar Kalaga

Hi @Stephen Deutsch , Can you please help?

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence