read sharePoint folder name and generate hyperlinks on sharepoint excel cells that links to folders

[GoallIn]

Hi everybody, I hope this post finds you all well.

Basically, I've different folders inside the sharepoint cloud that has a keyword character like ..._A1, ..._A2, ..._A3, etc. Then to simplify our lifes we wish to have an sharepoint excel that contains descriptions of the folders and the most important thing is to include a column that contains an hyperlink called _A1, _A2, etc that directs to the sharefolder that contains those characters.

Does someone knows if this is doable through sharepoint api or excel macros? or can you suggest a workaround or any other approach to get this done. We basically are trying to read sharepoint folders name and create hyperlinks on online excel sheets that directs to the right sharepoint folder.

All the best,

Miguel G

[#2]

@GoallIn 
Sure. You could create a Power Automate Flow that reads all Folders from the document library (assuming you have only one) using the "Get Files (properties only)" Action with "Include Nested Items=Yes" and a Filter "ContentType eq 'Folder'"
Then you can store the results in a CSV file and save it on SharePoint (or insert the items into an excel file using the Power Automate Excel Actions)


But the idea with this "Index" Excel File seems wierd... I personally would use the SharePoint search for that usecase.
To do so, i would install the PnP Modern Search Webparts (https://microsoft-search.github.io/pnp-modern-search/), set the query to "ContentType:Folder" and configure the webpart to display the results as a List.
This would surely be more configuration and is a little bit complicated but you would have a SharePoint page with always the current results.

[#3]

Hi Sven! thanks for answer me! My knowledge for power automate is really shallow but I'll definitely give it a try! Thank you again for this valuable insight!

[#4]

Hi @SvenSieverding, I've being playing around with power automate, but I have not succeed at all. 

i) in order to make changes I need to insert tables and it's not enabled for excel online with xlsm extension:

besides that, inserting tables might change the excel client format and might turn the overall process to be a semi manual task.

Is there any other tool you have in mind I can check out to write data from excel online to another excel online?

[#5]

You might generally have problems if you are using a xlsm file format, because macros are not working in Excel Online.

You could create a new worksheet in your file and add a table there. Then you can populate that table with data from a Power Automate Flow. After that you can use the call Office Scripts Action from Power Automate to call an Office Script
https://learn.microsoft.com/en-us/office/dev/scripts/overview/excel
In that ccript you can copy the values into the cells in your Report

Alternatively you could think about other technolgies like Power BI or a Power App