cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
silaman
Resolver I
Resolver I

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.

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

4 REPLIES 4
v-chengfen-msft
Microsoft
Microsoft

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:

vchengfenmsft_0-1665478766260.png

vchengfenmsft_2-1665479103436.png

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
Resolver I
Resolver I

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

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

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

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (3,263)