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

[Michael63]

Hello community.

I don't know whether this is the right space to post this question, but I haven't found a better place. 

Here is my problem statement: 

I need to collect inputs from a variable number of people through an Excel spreadsheet. Each person will get an individual Excel file to fill in the requested data. The structure of this file is the same for all because it is based on a template. Basically, there is a table that people should populate. 

After people returned the Excel files, they are stored in a Sharepoint folder (with arbitrary filenames!). 

Now, I would like to collect the data from all files in that Sharepoint folder and copy it into one single table in a separate Excel master spreadsheet automatically. 

The master spreadsheet is used to analyze the data, run statistics, create dashboards etc. 

How would you do this? 

Thanks for your suggestions. 

Michael. 

[#2]

1) If you're using SharePoint then you could just use one spreadsheet and "share" it to those that need to populate it and they can individually go in and update it.

2) If that doesn't work for you then try using Power Automate Desktop (https://flow.microsoft.com/en-us/blog/power-automate-desktop-march-2021-updates/) to pull the data together once you have it on your machine (or even use Power Automate/FLOW).

3) Or take a look at YouTube https://www.youtube.com/watch?v=dse9h2W4ejw (is one example) for a manual solution.

4) Final suggestion: Use Microsoft Forms. Create a Form to capture the data you need. Once all forms have been submitted go back in to Forms, go to "responses" and click "Open in Excel". All the replies will be in a spreadsheet. Manipulate it, save to SharePoint. Very little work required.

[#3]

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

[#4]

Only other suggestion I have would be to use Excel Power Query. It allows you to pull data from multiple spreadsheets. Try this for a heads up: https://www.youtube.com/watch?v=0aeZX1l4JT4

[#5 · Accepted solution]

Thanks, Allan.
I finally found a solution by this video: https://www.youtube.com/watch?v=3GIz50pftZ0
It shows how to apply data transformation on multiple Excel files with arbitrary filenames in a Sharepoint folder.

Thanks so much,
Michael