Load all the files from a folder in a drive Qliksense

Question

I'm trying to load all the files (google sheet) from a folder in a drive on Qlik Sense but I got the following error: noFileSize.

Is it a syntax issue ?

Here is 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

In the script driveId and folderId are the real Id, not variable.

Answer 1

It looks like you did the first step correctly, which is to get the list of Google Sheets files from your drive and desired folder(s). However, in order to actually pull any data out of the worksheets within those files, you'll need to do a few more steps.

Here's some script you can use to achieve:

Lib Connect To 'Google_Drive_&_Spreadsheets';

[Google Sheets]:
Select
    title,
    id,
    driveId,
    mimeType,
    parents_id
From ListFiles
  With Properties (
    driveId='',
    query='"folderId" in parents and mimeType = "application/vnd.google-apps.spreadsheet"'
  )
;

For vFileNum = 0 to NoOfRows('Google Sheets') - 1
    Let vCurrentFileId = Peek('id', vFileNum, 'Google Sheets');
    Let vCurrentFileName = Peek('title', vFileNum, 'Google Sheets');
    
    [Google Sheets - $(vCurrentFileName)]:
    NoConcatenate Select
        worksheetKey,
        sheetId,
        index,
        title,
        rowCount,
        colCount
    From ListWorksheets
      With Properties (
        spreadsheetKey='$(vCurrentFileId)'
      )
    ;
    
    For vWorksheetNum = 0 to NoOfRows('Google Sheets - $(vCurrentFileName)') - 1
        Let vCurrentWorksheetName = Peek('title', vWorksheetNum, 'Google Sheets - $(vCurrentFileName)');
        Qualify *;
        
        [Google Sheets - $(vCurrentFileName) - $(vCurrentWorksheetName)]:
        NoConcatenate Select *
        From GetSheetValues
          With Properties (
            spreadsheetKey='$(vCurrentFileId)',
            range='''$(vCurrentWorksheetName)''',
            valueRenderOption='FORMATTED_VALUE',
            dateTimeRenderOption='FORMATTED_STRING',
            generatedNumberedColumns='false',
            skipRows=''
          )
        ;
        
        Unqualify *;
    Next vWorksheetNum
    Drop Table [Google Sheets - $(vCurrentFileName)];
Next vFileNum

Here are some notes on what this script is doing:

• The [Google Sheets] table is collecting the list of Google Sheets files, the same way you did it.
• I use For vFileNum = 0 to NoOfRows('Google Sheets') - 1 to loop through that list of Google Sheets files I pulled in above.
• I create an ID and name variable for the current file in the loop (vCurrentFileId and vCurrentFileName).
• We then use the ListWorksheets endpoint to pull the list of worksheets for the current file in the loop.
• Then we start a new loop to go through each of the worksheets in the current file. For this we use For vWorksheetNum = 0 to NoOfRows('Google Sheets - $(vCurrentFileName)') - 1.
• We create a new variable vCurrentWorksheetName set to the current worksheet name.
• We use the Qualify *; statement to begin table qualification here.
• We use the GetSheetValues endpoint to pull all of the data from the current worksheet in our loop. We use our vCurrentWorksheetName variable to specify which worksheet to load.