Connecting to Google Drive FOLDER

[ryan_b_fiting]

Hello - 

I have a client that uses Google Drive to store their files.  I am trying to connect Power BI to a FOLDER on Google Drive, but am not having any luck finding a solution for this.  I have looked at some of the threads on here, but there do not appear to be any solutions.

Does anyone know if there is a way to connect to an entire folder from Google Drive, similar to what you would do with a local folder so you can pull all the files in at once, and when a new file is added you only have to refresh the dataset?

Any insights, help or documentation on this would be appreciated.  I find it hard to believe Microsoft does not have a Google Drive connector.

Thanks

Ryan

[Anonymous]

Hi @ryan_b_fiting,

Nope, current folder connector only works with the local path.
For your scenario, you can consider using web connector(work with 'rest API'/'web service'), other connector drivers (e.g odbc driver) or create a custom connector to achieve your requirement.

Starting to Develop Custom Connectors 

Regards,

Xiaoxin Sheng

[camargos88]

Hi @ryan_b_fiting ,

I used Google Scripts and Triggers to do it.

1-> Share a folder;

2 -> Create a Google Spreadsheet to store all shared urls;

3 -> Create a script to get all shared url on the sheet;

4 -> Create a trigger to update the sheets (I have scheduled once a day, but you have many options). You can find the Trigger Editor on "Edit -> Current Project's Trigger". Just select the script function you saved and the recurrency;

5 -> Connect the Spreadsheet (item 2) to Power BI;

This is the script I used:

function onLoad() {
var ss=SpreadsheetApp.getActiveSpreadsheet();
var s=ss.getActiveSheet();
var c=s.getActiveCell();
var fldr=DriveApp.getFolderById("USE_THE_SHARED_FOLDER_URL");
var files=fldr.getFiles();
var data=[],f,str;
var ss = SpreadsheetApp.getActiveSheet();
ss.clear();
ss.appendRow(["Name", "Date", "Id"]);
while (files.hasNext()) {
f=files.next();
data = [
f.getName(),
f.getDateCreated(),
f.getId()
];
ss.appendRow(data);
}
}

I hope this helps you,

Ricardo

[amitchandak]

Refer, if these can help

https://community.powerbi.com/t5/Power-Query/import-file-from-google-drive/td-p/43218

https://www.cdata.com/kb/tech/googledrive-odbc-power-bi.rst