Extracting image urls from JSON, to add to one cell in excel as hyperlinks (1) (2) (3) etc, rather than just long string of urls.

[silaman]

I use a flow to gather Forms responses into data verse and then a reference excel for management

I use the urls to go into a before and after photo columns from a site checklist.

I want to make each link a hyperlink like 1 2 3 etc.

I can extract the JSON to get the body then link select

[
  {
    "https://websitelink1.jpg": ""
  },
  {
    "https://websitelink2.jpg": ""
  },
  {
    "https://websitelink3.jpg": ""
  }
]

So far I can extract the links like that above, and I've been using a , join to them

{"https://websitelink1.jpg":""},{"https://websitelink2.jpg":""},{"https://websitelink3.jpg":""}

Thats what my excel columns have at the moment ^

I know to embed into a column for excel to hyperlink I need to go 

=HYPERLINK("https://websitelink1.jpg","1")

But I can't figure out how to do that for each link in the body, so that in the end Id just have 1, 2, 3, as in the cell.

I only just started out with power automate from nothing a few months ago so still learning the ropes.

Thankyou

[v-chengfen-msft]

Hi @silaman ,

Do you mean:
Fill the URL into the formula and add the formula to excel?
like this?
[
{
"https://websitelink1.jpg": "URL1"
},
{
"https://websitelink2.jpg": "URL2"
},
{
"https://websitelink3.jpg": "URL3"
}
]
=HYPERLINK("https://websitelink1.jpg","URL1")
=HYPERLINK("https://websitelink2.jpg","URL2")
=HYPERLINK("https://websitelink3.jpg","URL3")

Here is a test for your reference:

But there is a limit, please follow this link to set:

Solved: Updating row in Excel Table with HYPERLINK() works... - Power Platform Community (microsoft....

Best Regards

Cheng Feng

[silaman]

Thankyou @v-chengfen-msft 

I will play around with this, but it appears that this is making each url a row entry

I wanted to add the 3 Urls to one cell for one row, so that it shows up as URL1, URL2, URL3, in one cell for the before photos column for one row items.

But I've just realised that's not possible in excel. The form can only have up to 3 before and 3 after photos, so I think I'll have to create a few more columns as well

[silaman]

That last expression, I can't seem to get the syntax right, invalid every time

replace(replace(concat('https',last(split(string(items('Apply_to_each')),':'))),'}',"),"",")

Always fails saying its invalid, the end part with all the identical apostrophes and quotation marks

[v-chengfen-msft]

Hi @silaman ,

Could you show the content of [items('Apply_to_each')],

(Pay attention to protecting personal privacy)

I could set the formula according to your content

Best Regards

Cheng Feng