Get Data from Multiple Folders in GoogleDrive - Possible?

[chandelirious]

Hi all,

I'm not at all familiar with PowerBI, so please bear with me!

I am sending a survey out to 120 establishments, and then need to collate the data when it returns. The survey is on an Excel spreadsheet. (They are expecting this, they are part of a network - it isn't just a mailshot!)

I was thinking of doing something like this:

Setting up a folder on GoogleDrive, called "Survey". Within that folder will be 120 folders. Within each folder will be one Excel spreadsheet, with the name of the establishment.

My aim is that once all the establishments have completed the survey, I can use the Get Data feature of PowerBI to merge all of this data into one table.

I have two questions:
1: Is this possible - particularly since I will need to set each GoogleDrive folder as accessible only to those with the link?

2: If the establishment then updates their survey, would that be reflected in the PowerBI table?

Originally, I had looked at doing this in Excel using GetData, but I think that PowerBI will be better able to handle the data.

When this has been done before (it's a quarterly task), establishments have been sent the survey by email, have returned it by email, and then someone has copied the data from each individual spreadsheet into one master spreadsheet. I hope to be able to eliminate that, and also would like to be able to keep the data up-to-date.

Thanks in advance!

 

[alansidman]

This may prove helpful Power Query from Google Drive - Excel University

 

[chandelirious]

Thank you for this - I'm on a heavily locked down laptop, so I'll need to contact the IT department to see if they'll allow me to install Google Drive (hint: they'll say no!) But hopefully if I can convince them of the needs of the department, they'll let me - this looks like it will work.