What's the best way to collect data from a number of Excel spreadsheets within a Sharepoint folder?

Thanks, Allan.
I probably should have been more explicit in the problem description.

1) I am collecting data from different teams with their own objectives, and I want to keep the spreadsheets for those teams separate to allow some customization. Another reason why I did not consider this option is that, in my experience, Sharepoint often fails to sync the changes of multiple editors at the same time.
So option 1) does not work.

2) Power AUtomate seems to be a powerful tool. However, I have zero experience with that. I would need to deep-dive into it to evaluate. Any further hints are highly appreciated.

3) I have thought about something similar to this option because I use PowerQuery a lot. I know how to import data from an external Excel file by using PowerQuery. However, the challenge is to determine an unknown number of files with arbitrary filenames in a directory.
If I could resolve that problem, this approach would be my preferred one.

4) I already tried MS Forms, but I decided this is not the right tool for this problem. It might be good to run surveys but I am collecting a quite huge amount of quantitative data.

Thanks,
Michael