How to load all the google sheet from a folder from a drive in Qlik Sense ?

[Flipflop]

Hi, 

I'm trying to load all the files (google sheet) from a folder from a drive on Qlik Sense but i got the following error "noFileSize". Is it a syntax issue ?

Here the following script :

LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT';

SELECT title,
        id,
        mimeType
    FROM ListFiles
    WITH PROPERTIES (
    driveId='driveId',
    query='"folderId" in parents and mimeType = "application/vnd.google-apps.spreadsheet"'
    );

for Each file_name in FieldValueList('title');
     Qualify *; // making sure field names are unique to aviod synthetic keys
     LOAD
        *
     FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)];
   next;

[vincent_ardiet_]

I don't know the mechanism to load Google spreadsheets but I'm surprised that your LOAD instruction doesn't specify the format (like (qvd),(ooxml)...) , by default this is expected to be a text file.

[Aasir]

Issue with Meta data retrieval

LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT';

// Retrieve file metadata
FilesMetadata:
SELECT title, id, mimeType
FROM ListFiles
WITH PROPERTIES (
driveId='driveId',
query='"folderId" in parents and mimeType = "application/vnd.google-apps.spreadsheet"'
);

// Load data from each file
for Each file_row in FilesMetadata
Qualify *; // making sure field names are unique to avoid synthetic keys
LOAD
*
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_row.id)];
next;

[David_Friend]

Are you using QlikSense 'on-premises' if so what version?

I posted your question to ChatGPT, it returned this code:

// Example script
GoogleSheetsTable:
LOAD *
FROM [lib://GoogleSheetsConnector/YourGoogleSheetID]
(ooxml, embedded labels, table is Sheet1);

[Flipflop]

The $(file_row.id) is empty in the console, but the FilesMetaData request retrieves 2 lines, so i don't understand why it's empty.

Maybe i need to specify the format of the googlesheet (which is ... ?) like the answer before ?

[Aasir]

Try with this small change to your original

for Each file_name in FieldValueList('title')
Qualify *; // making sure field names are unique to avoid synthetic keys
LOAD
*
FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)]
(ooxml, no labels, header is 1 lines, table is [Sheet1]);
next;

[vincent_ardiet_]

From what I've just seen, this seems to be in QVX format when using the connector for Google sheets.

[Flipflop]

I tried to added (ooxml, no labels, header is 1 lines, table is [Sheet1]); (or using qvx) to my first request but it's not working. This time the name of the $(file_name) appears, but still, i've got the noFileSize error.

I also tried to change Sheet1 by the name of the sheet of my spreadsheet, and this is the same result...

Currently, my google sheet has 1 sheet but it will contain more in the future so the (table is [Sheet1]) will be porbably useless (even if the current code isn't working...) ?

 FROM [lib://GDR_1V_WORKLOAD_MANAGEMENT/folderId/$(file_name)];

the GDR_1V_WORKLOAD_MANAGEMENT is my connector define by

LIB CONNECT TO 'GDR_1V_WORKLOAD_MANAGEMENT'

Is it the good syntax ? Obviously i'm missing something but i don't what.

[vincent_ardiet_]

Not sure that this is the proper method in fact if we compared to this video https://help.qlik.com/en-US/video/RalaPTmYBCM

[Flipflop]

I need to use a loop because i don't know how many files will be in the folder of the drive. That's why i'm using this method.

My purpose is to load all the google sheet files in a folder from a drive which is already connected to Qlik Sense.