qliksense

Load all the files from a folder in a drive Qliksense

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.

Solution

  • 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: