topic Re: Google Drive Direct Folder Connection in Connectivity & Data Prep

Google Drive Direct Folder Connection

malradi88 — Tue, 21 Dec 2021 20:07:36 GMT

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

Re: Google Drive Direct Folder Connection

Fernando_Fabregas — Wed, 19 Feb 2020 16:12:51 GMT

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.

Re: Google Drive Direct Folder Connection

malradi88 — Wed, 08 Jul 2020 16:11:33 GMT

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

Re: Google Drive Direct Folder Connection

stevedark — Wed, 08 Jul 2020 16:39:08 GMT

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

Re: Google Drive Direct Folder Connection

malradi88 — Mon, 13 Jul 2020 08:42:45 GMT

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

Re: Google Drive Direct Folder Connection

Henri_Rufin — Thu, 03 Sep 2020 15:29:13 GMT

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?

Re: Google Drive Direct Folder Connection

stevedark — Thu, 03 Sep 2020 22:44:20 GMT

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!

Re: Google Drive Direct Folder Connection

Henri_Rufin — Fri, 04 Sep 2020 07:56:13 GMT

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 🙂

Re: Google Drive Direct Folder Connection

Henri_Rufin — Fri, 04 Sep 2020 08:22:06 GMT

STEP 1:

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;

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 :

// 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

Re: Google Drive Direct Folder Connection

stevedark — Fri, 04 Sep 2020 12:26:55 GMT

So all quite simple then. 😉

Thanks for sharing your solution.

Re: Google Drive Direct Folder Connection

Henri_Rufin — Mon, 07 Sep 2020 11:35:29 GMT

Now I am looking to do it with xlsx files instead

Re: Google Drive Direct Folder Connection

stevedark — Mon, 07 Sep 2020 19:41:42 GMT

Hi @Henri_Rufin

Is that with XLSX files stored in Google Drive? If so, that is much closer to the solution I describe in this blog post: https://www.quickintelligence.co.uk/cloud-file-services-qlik-sense/

The difference with the example code in this post is purely that your search will need to be for xlsx files and the format specifier will need to change from (txt) to (ooxml, etc.). In order to get the right format specifier, load the spreadsheet from a standard file location (such as DataFiles) and then copy and paste the format string from there to where you load from the Google Drive Files connector.

Good luck!

Re: Google Drive Direct Folder Connection

Henri_Rufin — Mon, 14 Sep 2020 11:01:47 GMT

As far as I know you can't which is why I suggested a workaround in this thread.