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

[ca2020]

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. 

[ca2020]

.

[ca2020 · Accepted solution]

3 min quick solution

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

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

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

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'

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

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 :

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.