cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ca2020
Helper I
Helper I

Create an Excel list of Sharepoint url column names for quick reference making app

I wanted to simply create a quick list in Excel of the url names for my columns that I could reference so I am self answering this question in the reply below in case it is useful to others. 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ca2020
Helper I
Helper I

3 min quick solution

 

1) Open your list and then click on the gear in the top right and then list settings

 

ca2020_3-1665701418565.png

 

Drag over the column names and type so they are all selected and then copy via the right click or ctrl+c

 

ca2020_4-1665701481872.png

 

 

2) Open a new workbook and in a sheet select cell A1 and paste your data

This will now show a hyperlink version of your column name and the column type in column B

 

ca2020_5-1665701577957.png

 

 

Click on the header of column C and if it is now a merged column, unmerge it and delete any icons that have also pasted that you don't need

 

3) Then on "Sheet1" name tab right click and select 'View Code'

 

ca2020_1-1665701255559.png

 

in the VBA window copy and paste this code:

 

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 2).Value = HL.Address
Next
End Sub

 

Then save and the click the play (green triangle) icon at the top of the developer window

 

ca2020_2-1665701350598.png

 

 

4) Go back to sheet 1 and you will see the full url as text in column C

Now Click on cell D2 and paste in this formula:

=TEXTAFTER(C2,"=",2,0,0,"")

Copy that down as far as needed

This will be the url field name used in fields such as in your power app

 

5) If you would also like a trigger text then in cell E2 paste in:

="@{triggerOutputs()?['body/"&D2&"']}"

Again copy that down as far as needed.

 

You should end up with something like this :

ca2020_0-1665701212289.png

 

 

Save as a macro workbook and you can use for future lists, you just have to remember to open the code and run it each time to get the url text or create a macro button to run the function if you prefer.

View solution in original post

2 REPLIES 2
ca2020
Helper I
Helper I

.

ca2020
Helper I
Helper I

3 min quick solution

 

1) Open your list and then click on the gear in the top right and then list settings

 

ca2020_3-1665701418565.png

 

Drag over the column names and type so they are all selected and then copy via the right click or ctrl+c

 

ca2020_4-1665701481872.png

 

 

2) Open a new workbook and in a sheet select cell A1 and paste your data

This will now show a hyperlink version of your column name and the column type in column B

 

ca2020_5-1665701577957.png

 

 

Click on the header of column C and if it is now a merged column, unmerge it and delete any icons that have also pasted that you don't need

 

3) Then on "Sheet1" name tab right click and select 'View Code'

 

ca2020_1-1665701255559.png

 

in the VBA window copy and paste this code:

 

Sub ExtractHL()
Dim HL As Hyperlink
For Each HL In ActiveSheet.Hyperlinks
HL.Range.Offset(0, 2).Value = HL.Address
Next
End Sub

 

Then save and the click the play (green triangle) icon at the top of the developer window

 

ca2020_2-1665701350598.png

 

 

4) Go back to sheet 1 and you will see the full url as text in column C

Now Click on cell D2 and paste in this formula:

=TEXTAFTER(C2,"=",2,0,0,"")

Copy that down as far as needed

This will be the url field name used in fields such as in your power app

 

5) If you would also like a trigger text then in cell E2 paste in:

="@{triggerOutputs()?['body/"&D2&"']}"

Again copy that down as far as needed.

 

You should end up with something like this :

ca2020_0-1665701212289.png

 

 

Save as a macro workbook and you can use for future lists, you just have to remember to open the code and run it each time to get the url text or create a macro button to run the function if you prefer.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

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

Community Call Conversations

Introducing the Community Calls Conversations

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

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (1,903)