How to transform user mentions to mailadress

[Timon Rütten]

Hi,

I have a table on a Confluence page in which colleagues can enter their user mentions if they want to be informed about a certain topic. Zero to x users can be linked in a cell.
In addition to notifications about updates to the page, reminders are sent directly to colleagues by email.
I would like to create an e-mail distribution list from this table, i.e. preferably the names with first name.last name@abc.com. Next step would be inserting the mailadresses into outlook.
I have tried to create suitable macros with Word and Excel, but unfortunately was not successful.
I would appreciate any creative ideas!

[Andrii Maliuta]

Hello @Timon Rütten ,

Do you need to transform this data inside the Confluence page (update page body) or create this table in any format outside of Confluence?

For Confluence, you can use the script like:

final XhtmlContent xhtmlContent;
final UserAccessor userAccessor;

def page = pageManager.getPage(1111L)
String storageBody = xhtmlContent.convertWikiBodyToStorage(page).getBodyAsString();

List<Integer> indexes = [] 
List<UserKey> mentionedKeys = []
Set<ConfluenceUser> mentionedUsers = []
 int index = 0; 

while(index != -1) { 
index = storageBody.indexOf("ri:userkey=\"", index);
  if (index != -1) { indexes.add(index); index++; } 
  }

 for (Integer tempI : indexes) {
    int start = tempI + 12; 
    int end = start + 32; 
    String mentionedKey = storageBody.substring(start, end); 
    UserKey userKey = new UserKey(mentionedKey); 

    mentionedKeys.add(userKey); 
  } 

 for (UserKey tempKey : mentionedKeys) {
   mentionedUsers.add(userAccessor.getUserByKey(tempKey)); 
 }

 Now you have mentionedUsers with all data you need (full names, emails, etc.)

[Timon Rütten]

Hi @Andrii Maliuta ,
Thank you for your answer!
Do I simply have to insert the script into an HTML macro that then adjusts the entire page?
Apart from the table mentioned, there is other content on the page.

To keep an overview, it would be best to copy the existing table and save the mail addresses in the new table.
The intention is to insert the mail addresses in Outlook directly in the addressee of the mail.
Processing outside of Confluence would therefore also be possible.

[Andrii Maliuta]

@Timon Rütten ,

No, to do this with JS, I suppose the code will be different, like using REST API - this code is for a custom addon or using e.g. Script Runner (https://marketplace.atlassian.com/apps/1215215/scriptrunner-for-confluence?hosting=datacenter&tab=overview) so that it can run Java Code directly.

JS pseudo-code will be smth like:

const page = await fetch('/rest/api/content/{PAGE_ID}?expand=body.storage')
const body = await page.json()['body']['storage']

// find all mention links "ri:userkey=\""

// extract mentions and add to array

// get each user mail by https://docs.atlassian.com/ConfluenceServer/rest/8.9.0/#api/user 
    e.g. (/rest/api/user?key={mention})

// add mails to an array

// update page with https://docs.atlassian.com/ConfluenceServer/rest/8.9.0/#api/content-update
    adding new table HTML and adding a column with mails

:)