Getting URL from RichText Column format URL to work in Adaptive card teams

[JimmyWork]

Hi

I have a RichText column in a SharePoint list where users enter Hyperlinks. one or many.

The data i have gotten out is the following.

Unformatted data

<div class="ExternalClassB970DA15388D4825835915ED4047D6F0"><div style="font-family&#58;Calibri, Arial, Helvetica, sans-serif;font-size&#58;11pt;color&#58;rgb(0, 0, 0);"><span style="color&#58;rgb(255, 255, 255);"><div style="margin-top&#58;14.6667px;margin-bottom&#58;14.6667px;"><a href="https&#58;//google.se/" title="File 1">File 1</a><div style="margin-top&#58;14.6667px;margin-bottom&#58;14.6667px;"><a href="https&#58;//google.com/" title="File 2">File 2</a><br></div><br></div><div style="margin-top&#58;14.6667px;margin-bottom&#58;14.6667px;"><a href="/sites/removed/_layouts/15/google.se" title="File 1"></a></div></span></div></div>

I then use a HTML to text

First file [https://google.com/]Second file [https://google.se/]



/sites/removed/_layouts/15/google.se

Then Compose = uriComponent(body('Html_to_text'))

First%20file%20%5Bhttps%3A%2F%2Fgoogle.com%2F%5DSecond%20file%20%5Bhttps%3A%2F%2Fgoogle.se%2F%5D%0A%0A%0A%0A%2Fsites%2Fremoved%2F_layouts%2F15%2Fgoogle.se%0A%0A

Then Compose = split(outputs('Compose_-_URI_Encode'),'%0A')

[
  "First%20file%20%5Bhttps%3A%2F%2Fgoogle.com%2F%5DSecond%20file%20%5Bhttps%3A%2F%2Fgoogle.se%2F%5D",
  "",
  "",
  "",
  "%2Fsites%2Fremoved%2F_layouts%2F15%2Fgoogle.se",
  "",
  ""
]

Then Compose = decodeUriComponent(first(outputs('Compose_-_Split_Encoded_Line_Break')))

"First file [https://google.com/]Second file [https://google.se/]"

For Hyperlinks to work and display correctly in a Teams Adaptive card the following format is needed.

[I'm an inline-style link](https://www.google.com)

Can anyone help me achieve this?

[JimmyWork]

To keep it simple how can I turn this.

"First file [https://google.com/]Second file [https://google.se/]"

Into this.

[First file](https://www.google.com),
[Second file](https://www.google.se)

[JimmyWork]

Bump

[v-yujincui-msft · Accepted solution]

Hi @JimmyWork ,

Do you want to convert a string into an array of a specific format?

I have made a test for your reference.

split(replace(outputs('Compose'),'"',''),']')

take(outputs('Compose_2'),add(length(outputs('Compose_2')),-1))

split(substring(item(),0,add(length(item()),-1)),'[')

concat('[',trim(first(outputs('Compose_4'))),'](',last(outputs('Compose_4')),')')

Result Screenshots:

Best Regards,

Charlie Choi

[JimmyWork]

@v-yujincui-msft Thank you for taking the time

[Paulie78 · Accepted solution]

This should work for you:

https://ibb.co/nbMjdqV

Code:

Select From:
range(0,length(xpath(xml(replace(outputs('Compose'), '<br>', '')), '//*/@href')))
Select Title:
replace(substring(xpath(xml(replace(outputs('Compose'), '<br>', '')), '//*/@title')[item()], 7), '"', '')
Select URL:
replace(substring(xpath(xml(replace(outputs('Compose'), '<br>', '')), '//*/@href')[item()],6), '"', '')

Output Produced:

[
  {
    "Title": "File 1",
    "URL": "https://google.se/"
  },
  {
    "Title": "File 2",
    "URL": "https://google.com/"
  },
  {
    "Title": "File 1",
    "URL": "/sites/removed/_layouts/15/google.se"
  }
]

Blog: tachytelic.net

YouTube: https://www.youtube.com/c/PaulieM/videos

If I answered your question, please accept it as a solution 😘

[JimmyWork]

@Paulie78 This was so clean, will have to read up on how this works, do you have any links that will help me understand your solution?

[Paulie78]

Really the secret to it is in the xml and xpath expressions, which are very powerful but rarely used. I might actually make a video about it using your example, as the example.

But if you want to have a go at it, try using the XML expression on your unformatted data, and you will soon see that it does not work. The reason is because it contains unclosed BR tags, which is why I replaced them.

Once XML works it opens you to being able to use xpath. With xpath we can extract the value of attributes.

The final part was just cleaning up the output of xpath.

[JimmyWork]

How you then procced to make the output in the correct format, I did try another select where I just added[Title](URL) 

The output then becomes.

 { "Link": "[AnotherFile 2](https.//google.com)" },

When using this in an adaptivecard They only support this format.

The n\n\ need to be there to create the new line break in the adaptive card for each link

[AnotherFile 1](https.//google.com)\n\n[AnotherFile 2](https.//google.com)\n\n\[AnotherFile 3](https.//google.com)

@Paulie78 

[JimmyWork]

@Paulie78 

I finally achieved what I wanted and your part simplified it so much.

I can't think to help that the rest of my flow complicates what I'm trying to do.

If you have the time to look it thru please do so, it's working but it's awfully long 🙂

First part was the part you solved where I actually get and extract the links.

Second part is that the links contain Microsoft own added format for example.

/:u:/r/sites/somesite/Illustrations/150Hz_modulation_predominant.svg?csf=1&web=1&e=x5PeM7"

/:u/r/sites/somesite/image.jpeg
/:i:/r/sites/somesite/file.svg

So I needed a way to replace all those formats to https://myrealadress.com/sites/xxxxxxxx
To achieve this I did the following.

Created the three variable below and then an "Apply to each" where I loop thru all the links and replace the Microsoft formatted links.

 

I also needed to remove any duplicate links because it will always create the first link as a duplicate in the data I pull. For this I used a compose.

union(body('Select'),body('Select'))

 At last I needed to format the data so it works with the teams Adaptative card markdowns for links.

I created a new string variable named Links.

Then I did another apply to each where I add the needed format and join the title and URL, I also add two new line breaks due to the adaptive card markdowns needs two to create the second link in a new line.

At the end I finish with a Html to text and a compose.

uriComponentToString(variables('TempLinks'))

My finished output looks like this and it works in adaptive cards for Teams

[First file test 1](https://mywebsite.sharepoint.com/sites/demo/Illustrations/150Hz_modulation_predominant.svg?csf=1&web=1&e=x5PeM7)

[AnotherFile 2](https://mywebsite.sharepoint.com/sites/demo/Illustrations/787_in-trail_procedures.png?csf=1&web=1&e=g2akek)

[Vide file test](https://mywebsite.sharepoint.com/sites/demo/Videos/RPReplay_Final1582536602/RPReplay_Final1582536602.MP4?csf=1&web=1&e=iinGSu)

Final result looks like this.

[Paulie78]

To be honest, I think you have done well. For sure the flow is more complicated than it needs to be, but I don't think that really matters. 

I do think it would be possible to do the entire thing in one, or perhaps two steps using the approach I took above. But is there really that much need for the added simplicity in the flow? If what you have done works, then it is a good flow 😀

[JimmyWork]

@Paulie78 Thank you again for answering.

Reason for simplicity would be for myself to improve my won knowledge and even if the flow works I'm not happy with how long it turned out, but at least I can check each part if something fails 🙂