Google Drive Direct Folder Connection

[malradi88]

To Whom it May Concern,

I hope this finds you well. Is there a way to create a connection to a google drive folder? Using the Google Drive & Sheets connector in Qlik Web Connectors, and the regular web file connector I am able to connect to individual google sheets but not a specific folder.  Thank you for your support!

Best,

Mohammed

[Fernando_Fabregas]

Hi Mohammed.

You can read and write files in a Google Drive folder using the last path of folder's URL instead the folder name.

Example: 

LOAD * FROM [lib://Desarrollo:GDrive/1orRIXXXXXX9999-WmXXXXXXXdv999/ConfigPedidos.xlsx]

Space Name (for Qlik Sense Business): Desarrollo

Connection name: GDrive

Google Drive folder Name: ME (don't use it)

Last path of URL: 1orRIXXXXXX9999-WmXXXXXXXdv999

Another way to get the URL is using Data Manager, chosing any file and copy the script.

[malradi88]

Hello I am sorry for the late reply to this. I tried replicating this but it doesn't seem to work. Have you been able to do this successfully? I ran across this article:

https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

which seems to offer a solution to loading multiple files from google drive/dropbox. I can't seem to find a 'meta data' connector in Qlik Sense Enterprise though. Is this only available for Qlik Sense Business? Thank you for your support.

Best,

Mohammed 

[stevedark · Accepted solution]

Hi,

The connectors on the Cloud are slightly more helpful than those on QWC for use on premise. As you note there is no metadata connector.

You can however find folders and work through them by parsing the output of the List Files connection.

This connection returns both files and folders in the list.  Folders have a mimeType of application/vnd.google-apps.folder and the title is the name of the folder.

Once you have located the folder you want to list files for you can grab the id for that folder.

The files in that folder will have a parents_id of the id you have grabbed.

You can construct queries in the connector to just pull the information that you need at each point.

Hope that helps!

Steve

[malradi88]

Thank you very much Steve! At the moment I don't have time to try this solution but it's good to know there is a way to do it and that it has been done before 🙂 I will set sometime aside to try this solution (also hope that webconnectors introduces the metadata connectors in the future).

Best,

Mohammed

[Henri_Rufin]

Hi Steve,

I have a similar issue where I try to upload all files located in a single Google Drive folder at once. I followed your suggestion and this helped me retrieve the list of spreadsheets saved in that folder. I also found the parentsid (id of the folder) you were talking about but I coudn't use it in a query to retrieve data from each file. Problem is I don't know how to write that query. The Web connector console can not help me here. Can you provide a syntax?

[stevedark]

Hi @Henri_Rufin 

The article posted by @malradi88 above might be useful.

The syntax for writing queries against the Google API isn't obvious, and I found I needed a bit of trial and error to get it right.

There is some documentation on the query syntax here:
https://developers.google.com/drive/api/v2/search-shareddrives

I would look to get it working by copying and pasting an ID into the Select Data wizard and getting the code pasted into your load script. Once you have this you can look at substituting in the variable.

Good luck!

[Henri_Rufin]

Thank you Steve. I actually managed to generate a script that worked just fine. I didn't find a way to use the parent id (folder id) in the query so I decided to create a loop that fetches the data from the files I needed. Several loops are actually needed for this. 

STEP 1:

So again the goal here is to fetch all files included in a given folder. In my case I am fetching only Google Spreadsheets which share a common partial title. First step will be to retrieve the list of files that share the same partial title. To do so use the web connectors console and choose ListFiles in the Google Drive connector. Then enter title contains 'Your partial title'. Run the query and get the script to paste in your app. Then arrange the code as following :

LOAD
Num(RowNo()) as Id,
id as worksheetKey,
parents_id as folder,
mimeType as DocumentType
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListFiles&query=title+contains+'YOUR PARTIAL TITLE QUERY RESULT'&appID=], qvx);

MaxTable:
Load
Max(Id) as NbFiles
Resident WorksheetList;

Let vNbFiles = Peek('NbFiles',0,[MaxTable]);
Trace NbFiles is $(vNbFiles);
Drop Table MaxTable;

At the end of this stage we now have a list of worksheetid corresponding to your partial title. DocumentType helps you filter between the different types of document (xlsx, googlespreadsheet,etc...). The variable vNbFiles shows the number of files for the loop.

Step 2:

Now we need to get matching spreadsheetid associated with worksheetid. To do so we loop through the listspreadsheet using a variable to match the worksheet key at each step :

// Initialisation
Trace file number 1;

Temp_WorksheetList:
NoConcatenate
Load *
Resident WorksheetList where Id='1' and DocumentType='application/vnd.google-apps.spreadsheet';

Let vworksheetKey = Peek('worksheetKey',0,[Temp_WorksheetList]);
Trace worksheetKey is $(vworksheetKey);
Drop Table Temp_WorksheetList;

Worksheets:
LOAD
'1' as Id,
SubField(worksheetKey,':_',1) as WorksheetKey,
SubField(worksheetKey,':_',2) as SpreadsheetKey
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vworksheetKey)&appID=], qvx);

// Itération à NbFiles
Set a=2;

Do while a<$(vNbFiles)+1

Trace file number $(a);

Temp_WorksheetList:
NoConcatenate
Load *
Resident WorksheetList where Id='$(a)' and DocumentType='application/vnd.google-apps.spreadsheet';

Let vworksheetKey = Peek('worksheetKey',0,[Temp_WorksheetList]);
Trace worksheetKey is $(vworksheetKey);
Drop Table Temp_WorksheetList;

Concatenate(Worksheets)
LOAD
$(a) as Id,
SubField(worksheetKey,':_',1) as WorksheetKey,
SubField(worksheetKey,':_',2) as SpreadsheetKey
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vworksheetKey)&appID=], qvx);

Let a=a+1;

Loop;

Drop Table WorksheetList;

Step 3 :

Now we have a table in which we find worksheetid and matching spreadsheetid for the partial title we were looking for and the document type specified. We now need to to run a loop to fetch data in each file using both worksheetid and spreadsheetid in two distinct variables.

// Initialisation
Trace file number 1;

Temp_Worksheets:
NoConcatenate
Load *
Resident Worksheets where Id='1';

Let vworksheetKey = Peek('WorksheetKey',0,[Temp_Worksheets]);
Trace worksheetKey is $(vworksheetKey);
Let vspreadsheetKey = Peek('SpreadsheetKey',0,[Temp_Worksheets]);
Trace spreadsheetKey is $(vspreadsheetKey);
Drop Table Temp_Worksheets;

DATA:
LOAD *
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=GetWorksheet&worksheetKey=$(vworksheetKey)%3a_$(vspreadsheetKey)&appID=], qvx);

//Itérations

Set a=2;

Do while a<$(vNbFiles)+1

Trace file number $(a);

Temp_Worksheets:
NoConcatenate
Load *
Resident Worksheets where Id='$(a)';

Let vworksheetKey = Peek('WorksheetKey',0,[Temp_Worksheets]);
Trace worksheetKey is $(vworksheetKey);
Let vspreadsheetKey = Peek('SpreadsheetKey',0,[Temp_Worksheets]);
Trace spreadsheetKey is $(vspreadsheetKey);
Drop Table Temp_Worksheets;

Concatenate(DATA)
LOAD *
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=GetWorksheet&worksheetKey=$(vworksheetKey)%3a_$(vspreadsheetKey)&appID=], qvx);
Let a=a+1;

Loop;
Drop Table Worksheets;

And there you go 🙂

[Henri_Rufin]

Thank you Steve. I actually managed to generate a script that worked just fine. I didn't find a way to use the parent id (folder id) in the query so I decided to create a loop that fetches the data from the files I needed. Several loops are actually needed for this. 

STEP 1:

So again the goal here is to fetch all files included in a given folder. In my case I am fetching only Google Spreadsheets which share a common partial title. First step will be to retrieve the list of files that share the same partial title. To do so use the web connectors console and choose ListFiles in the Google Drive connector. Then enter title contains 'Your partial title'. Run the query and get the script to paste in your app. Then arrange the code as following :

LOAD
Num(RowNo()) as Id,
id as worksheetKey,
parents_id as folder,
mimeType as DocumentType
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListFiles&query=title+contains+'YOUR PARTIAL TITLE QUERY RESULT'&appID=], qvx);

MaxTable:
Load
Max(Id) as NbFiles
Resident WorksheetList;

Let vNbFiles = Peek('NbFiles',0,[MaxTable]);
Trace NbFiles is $(vNbFiles);
Drop Table MaxTable;

At the end of this stage we now have a list of worksheetid corresponding to your partial title. DocumentType helps you filter between the different types of document (xlsx, googlespreadsheet,etc...). The variable vNbFiles shows the number of files for the loop.

Step 2:

Now we need to get matching spreadsheetid associated with worksheetid. To do so we loop through the listspreadsheet using a variable to match the worksheet key at each step :

// Initialisation
Trace file number 1;

Temp_WorksheetList:
NoConcatenate
Load *
Resident WorksheetList where Id='1' and DocumentType='application/vnd.google-apps.spreadsheet';

Let vworksheetKey = Peek('worksheetKey',0,[Temp_WorksheetList]);
Trace worksheetKey is $(vworksheetKey);
Drop Table Temp_WorksheetList;

Worksheets:
LOAD
'1' as Id,
SubField(worksheetKey,':_',1) as WorksheetKey,
SubField(worksheetKey,':_',2) as SpreadsheetKey
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vworksheetKey)&appID=], qvx);

// Itération à NbFiles
Set a=2;

Do while a<$(vNbFiles)+1

Trace file number $(a);

Temp_WorksheetList:
NoConcatenate
Load *
Resident WorksheetList where Id='$(a)' and DocumentType='application/vnd.google-apps.spreadsheet';

Let vworksheetKey = Peek('worksheetKey',0,[Temp_WorksheetList]);
Trace worksheetKey is $(vworksheetKey);
Drop Table Temp_WorksheetList;

Concatenate(Worksheets)
LOAD
$(a) as Id,
SubField(worksheetKey,':_',1) as WorksheetKey,
SubField(worksheetKey,':_',2) as SpreadsheetKey
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vworksheetKey)&appID=], qvx);

Let a=a+1;

Loop;

Drop Table WorksheetList;

Step 3 :

Now we have a table in which we find worksheetid and matching spreadsheetid for the partial title we were looking for and the document type specified. We now need to to run a loop to fetch data in each file using both worksheetid and spreadsheetid in two distinct variables.

// Initialisation
Trace file number 1;

Temp_Worksheets:
NoConcatenate
Load *
Resident Worksheets where Id='1';

Let vworksheetKey = Peek('WorksheetKey',0,[Temp_Worksheets]);
Trace worksheetKey is $(vworksheetKey);
Let vspreadsheetKey = Peek('SpreadsheetKey',0,[Temp_Worksheets]);
Trace spreadsheetKey is $(vspreadsheetKey);
Drop Table Temp_Worksheets;

DATA:
LOAD *
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=GetWorksheet&worksheetKey=$(vworksheetKey)%3a_$(vspreadsheetKey)&appID=], qvx);

//Itérations

Set a=2;

Do while a<$(vNbFiles)+1

Trace file number $(a);

Temp_Worksheets:
NoConcatenate
Load *
Resident Worksheets where Id='$(a)';

Let vworksheetKey = Peek('WorksheetKey',0,[Temp_Worksheets]);
Trace worksheetKey is $(vworksheetKey);
Let vspreadsheetKey = Peek('SpreadsheetKey',0,[Temp_Worksheets]);
Trace spreadsheetKey is $(vspreadsheetKey);
Drop Table Temp_Worksheets;

Concatenate(DATA)
LOAD *
FROM [$(vQwcConnectionName)]
(URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=GetWorksheet&worksheetKey=$(vworksheetKey)%3a_$(vspreadsheetKey)&appID=], qvx);
Let a=a+1;

Loop;
Drop Table Worksheets;

And there you go 

[stevedark]

So all quite simple then. 😉

Thanks for sharing your solution.