cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ClaireAllen
Post Prodigy
Post Prodigy

Hyper Links from Excel to SharePoint

Hello Community,

 

I have built a workflow that takes my Excel data and Uploads any changes or new items to a SharePoint List, which runs really well.

 

What I would now like to do is have a column in my Excel File that I can put a hyper link to a document in (see image below)

Excel Links.png

 

If I enter a Column in to my SharePoint List as a Single line of Text, I get this (see image below) just the text from the file name.

PFPE SP.png

 

I tried added a Column in SharePoint for Hyper Link, but received an error message:

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Links' is required to be of type 'String/uri'. The runtime value '"Mobile equipment check_V2 (1)"' to be converted doesn't have the expected format 'String/uri'.

 

Can anyone advise what the string/uri format should be, as this is the FIRST time that I have tried using a Hyperlink in one of my flows.

 

Thank you in advance
Kind regards
Claire

 

1 ACCEPTED SOLUTION

Accepted Solutions
Paulie78
Super User III
Super User III

You can do it two ways:

 

First way with a "Hyperlink" type column, isn't easy, but something like this does it:

UpdateLink.png

Second way, which is much easier, is just to use a multiline text field and make sure that you have Enhanced rich text turned on:

2020-11-20_13-53-43.png

Then you can just compile the link with HTML and dynamic content, like:

<a href="https://www.tachytelic.net/">Paulies wonderful blog</a>

and put that directly into the field.

 

View solution in original post

12 REPLIES 12
krootz
Solution Supplier
Solution Supplier

@ClaireAllen,

The list rows present in table does not return the hyperlink, only the text.

So you might have to create another column in your excel table that has the hyperlink itself. 

 

You can create a function in excel to extract it:

Function GetURL(rng As Range) As String

   On Error Resume Next

   GetURL = rng.Hyperlinks(1).Address

End Function

 

Then create a new column in your table to call this function =GetURL(I1) and use this instead in your flow to update the SP link.

@krootz ,

 

Thank you for the reply, I did try adding a hyperlink column in originally and that's when I got the error message I mentioned in my post.

I'm not familiar with excel functions, but will have a look.

Thank you

Kind regards

Claire

@ClaireAllen  from your screenshot I can see that added the hyperlink but with a displayed text. If you hover on top of it, you'll see the actual URL. The displayed text is what's being read by the list row action, not the hyperlink itself. If you use the URL address as the display text itself, then it will work. If you have displayed text in your data with the underlying hyperlink, then you could create that function in excel and use it to extract the URL.

sumurthy
Microsoft
Microsoft

You can easily achieve this using Office Scripts. 

See here if this will solve your problem: 

https://github.com/sumurthy/officescripts-projects/tree/main/Return%20Table%20Data%20as%20Array%20of...

 

The previous response of creating function and using that in the Excel to extract the hyperlink may have following issues: for one, you'll have to save that as a macro file, which is not supported in Power Automate. Secondly, you may not have prior access to the Excel file to create/store/manage the UDF function. If you wish to run the same logic against an incoming file someone uploads/emails, etc. you won't be able to do that since UDF functions are specific to a file. 

 

The advantage of doing with Office Script is that you can expand the this to any file stored in SP/OneDrive without having to worry about altering the file. The script I showcase in the link can run against any Excel file. 

 

If you need help with setting this up, please look at other detailed videos on the site that I linked.

krootz
Solution Supplier
Solution Supplier

@sumurthy Thanks for this! I'll have to try office scripts. 🙂

@sumurthy ,

Thank you for the information, this looks helpful
I was not able to see any details video on how to get this set up, are you able to send this over so that I can take a look at this solution.

Thank you & regards

Claire

Paulie78
Super User III
Super User III

I would use a different approach in your Excel table personally. I would have three columns:

  1. Hyperlink Text
  2. Hyperlink URL
  3. Hyperlink

The third column would use a formula to create a hyperlink from the previous two columns. You would then hide the first two columns. But then with Power Automate you would get distinct fields with the information you want:

Hyperlnk.PNG

@Paulie78 ,

Thank you for the suggestion

I can get the Excel list to update my SharePoint Column, but I would like the SharePoint Column to update it as an actual hyperlink.

 

I've tried adding various types of columns to my SharePoint list, single line of text, hyperlink and multiple line of text and I've not yet achieved to get the column to update as an actual hyper link.

When I use the text columns, it just pull in the text which is what you would expect, and if I use the hyperlink option I get an error message.

 

OpenApiOperationParameterTypeConversionFailed. The 'inputs.parameters' of workflow operation 'Update_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/Links' is required to be of type 'String/uri'. The runtime value '"Mobile equipment check_V2 (1)"' to be converted doesn't have the expected format 'String/uri'.

 

Kind regards
Claire

Paulie78
Super User III
Super User III

Yes, you have to formulate the request in a special type of way. I have an example, I will dig it out for you.

Paulie78
Super User III
Super User III

You can do it two ways:

 

First way with a "Hyperlink" type column, isn't easy, but something like this does it:

UpdateLink.png

Second way, which is much easier, is just to use a multiline text field and make sure that you have Enhanced rich text turned on:

2020-11-20_13-53-43.png

Then you can just compile the link with HTML and dynamic content, like:

<a href="https://www.tachytelic.net/">Paulies wonderful blog</a>

and put that directly into the field.

 

View solution in original post

@Paulie78 ,

Thank you again for the suggestions

I did do a Multi line of text as mentioned,

I'm not sure how to or where to configure the dynamic content, in Power Automate

Does the hyperlink need to be to a web address? mine will a link to documents

 

Hello @Paulie78 ,

Thanks for the information,

After our discussion, I the hyperlink now works in my excel file and updates back to my SharePoint list,

This was how we configured the workflow

Screenshot 2020-11-23 082806.png

Thanks for the Solution,

Regards

Claire

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,920)