Pulling Data From ~300 Individual Sheets Onto a Master Sheet

Any all all help is appreciated!

I’m operating out of a company SharePoint, as multiple people need to be able to access these sheets. I want to have a Master sheet, that tracks information from ~300 individual site portfolios. I want the Master sheet to be able to automatically pull about 30+ data points off each portfolio and drop them into a single row, in a specific order. One row for one portfolio.

In my head, this works by me pasting a hyperlink (or something equivalent) in Column A, and all the needed data points automatically populating in the same row. This link serves two purposes. One, to provide a source for the Master to pull data points. Two, easy access to each site portfolio from the Master. Here’s a look at what I’ve tried so far:

VLOOKUP with HYPERLINK: This one I’ve had a little bit of success with, but I’m running into a logistical issue. I can get this to work in the sense that data points pulled get from the linked portfolio in Column A and get dropped into the same row. The problem is the data points only update when I “Open in Desktop”. If I edit a data point in a site portfolio while in Sharepoint, nothing gets updated on the Master. This won’t work for me, as most of the time my team will be editing portfolio sheets in Sharepoint.

Power Query: This one has promise, but I’m unfamiliar with PQ. Biggest hurdle I’m running into here is getting all wanted data points into a specific order in a single row. Each individual site portfolio has a ton of information, much more than what I want displayed on the Master sheet. With how much information in on each site portfolio, the data I want to pull is “all over the place”. Any advice on how to consolidate dispersed data into a single row using power query would be excellent.

Using VBA: This is by far the option that I have the least amount of experience in and would like to avoid if possible. I’ve never been skilled in any sort of coding or messing with macros. However, I’m always willing to try and learn, so if this is the best option, please let me know what I can try.

I appreciate the help from everyone. Please let me know if there are any questions or anything that I need to clarify. Thank you!