Load data from several google drive folders

[Anna12]

Dear all, 

hope you are doing fine!

I have an issue with loading data from google drive. I want to load several files from different folders on google drive. I found several options to load data from dropbox. The skrip is like this: 

LIB CONNECT TO 'Dropbox_NAME - Mail';

Ordner:
LOAD tag as [List.tag],
name as [List.name];
SELECT tag,
name
FROM List
WITH PROPERTIES (
dropboxFolder='/Name',
recursive='false',
includeDeleted='false'
)
;

x:
Load [List.name]
Resident Ordner
Order By [List.name] asc;

DROP TABLE Folder;

LIB CONNECT TO (dropbox);

for iFolder = 0 to NoOfRows('x') - 1
let vFolder = peek('List.name', iFolder, 'x');

Sales:
LOAD *
FROM [lib://dropbox -Mail/Folder/$(vFolder)/sales_x.csv] (txt, codepage is 28591, embedded labels, delimiter is ';', msq);

This works for Dropbox, but I dont know how to do this with google drive. What do i put instead of " dropboxFolder='/Name'," ?

If i just change the source, it tells me "Cannot open file". 

I appreciate your help!!!

Best

Anna

[Bjorn_Wedbratt]

Hi @Anna12 ,

You can achieve the above using a combination of Google Drive and Spreadsheets connector and the Google Drive connector.

Basically you can use Google Drive and Spreadsheets connector to get get the folders and folder id:s from Google Drive:

SELECT title,
	id
FROM ListFiles
WITH PROPERTIES (
driveId='',
query='mimeType = ''application/vnd.google-apps.folder'''
);

After identifying the folder id:s, you can loop through the selected folder(s) and filter on specific file types. As an example, let's filter out csv files only:

SELECT title,
        id,
        mimeType
    FROM ListFiles
    WITH PROPERTIES (
    driveId='',
    query='"$(folder)" in parents and mimeType = ''text/csv'''
    );

Now the Google Drive connector can be used to read in the files (csv:s in this example) located in each folder specified

for Each file_name in FieldValueList('file.title');
      Qualify *; // making sure field names are unique to aviod synthetic keys
      LOAD
         *
      FROM [lib://Google_Drive/$(folder)/$(file_name)]
      (txt, codepage is 28591, embedded labels, delimiter is ',', msq);
    next

Attached is a small example that will make it a bit clearer. 

You need to attach the Google connectors to your Google account and then run the script once to get the table id:s in the table. Then manually copy/paste the folders to the variable defined in the script (vFolderIds).

Hope this helps!

Best

Bjorn