Internal Server Error everytime I load the script

[YassineB_23]

Hello everyone,

Im having a problem while loading my script which uses Google Drive Connector (WebConnector). Im looping through 26 google sheet files to load them into Qlik and then concatenate them all. But I always get the same error ,"Internal Server Error", and it doesn't always fail at the same line. I already managed to make it work, but with less files.

Each file represents a different MonthYear value. Please find an example of the script log attached. Thanks for your Help.

[BREEZE]

Same problem here!!

[NadiaB]

Hi @YassineB_23 

Could you increase the Logging level to "Error" and verify if you get a more descriptive error?

https://help.qlik.com/en-US/connectors/Subsystems/Web_Connectors_help/Content/Connectors_QWC/Adminis...

Kind Regards.

[Onyi_Nwabia-Robbins]

Did you find a solution to this issue? @YassineB_23 or @BREEZE . I ended up putting the command

sleep 1000;

in my loops which helps it get further in the load but then I still ended up with Internal Server Error. 

This would suggest to me its a limit issue. 

I updated my logging level to error as suggested above and in the error details section of the latest web connector log file I get this 

  "error": {
    "code": 429,
    "message": "Quota exceeded for quota metric 'Read requests' and limit 'Read requests per minute per user' of service 'sheets.googleapis.com' for consumer 'project_number:560254647742'.",
    "status": "RESOURCE_EXHAUSTED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.ErrorInfo",
        "reason": "RATE_LIMIT_EXCEEDED",
        "domain": "googleapis.com",
        "metadata": {
          "consumer": "projects/560254647742",
          "quota_metric": "sheets.googleapis.com/read_requests",
          "quota_limit": "ReadRequestsPerMinutePerUser",
          "quota_limit_value": "100",
          "service": "sheets.googleapis.com",
          "quota_location": "global"
        }

1. I am looping through 243 spreadsheet keys to get all the relevant worksheet keys
2. Then I am looping to get all the data from those sheets (729 worksheets)

So definitely hitting the 100 limit several times

I know google sheets api has a default limit of 300 Per minute per project so I am not sure why its 100 on the google connector. https://developers.google.com/sheets/api/limits

I don't know who sets this limit, Qlik, Google or Local IT admin.

But as a temporary measure, I decided to use the for next loop variable to check if the current load is divisible by 100, wait 5 secs, then continue to load, otherwise just loop as normal. 

Here is a crude example below

Let SSNumOfRows= NoOfRows('GetSpreadsheets');
For i=0 to $(SSNumOfRows)-1
	//due to limit Read requests per minute per user of service 'sheets.googleapis.com, adding delay after every 100 loads
    //without this it will cause an Internal Server Error
    
	if Mod($(i), 100 )=0 THEN
         TRACE;
         TRACE reached another 100 requests, waiting 5 secs;
         TRACE;
         Sleep 5000;
    
	Let vSpreadsheetKey = Peek('SpreadsheetKey', $(i),'GetSpreadsheets');
   
   	GetWorksheets:
      LOAD
    replace(worksheetKey,':','%3a') as WorksheetKey //needed for correct URL format to load sucessfully

      FROM [$(vConnectionName)]
      (URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vSpreadsheetKey)&appID=], qvx);
      
      ELSE //load count not divisible by 100
      
      Let vSpreadsheetKey = Peek('SpreadsheetKey', $(i),'GetSpreadsheets');
   
        GetWorksheets:
          LOAD
              replace(worksheetKey,':','%3a') as WorksheetKey //needed for 
          FROM [$(vConnectionName)]
          (URL IS [http://localhost:5555/data?connectorID=GoogleDriveConnector&table=ListWorksheets&spreadsheetKey=$(vSpreadsheetKey)&appID=], qvx);
      
      ENDIF
      

This worked fine for part 1 above, but I still have an error in part 2!

Turns out this was a different error!

<Message>Error requesting table GetWorksheetV2 for GoogleDriveConnector</Message>
        <Exception>APIConnectorInterfaces.Exceptions.QwcException: No columns returned for QlikWebConnectors:GoogleDriveConnector.GetWorksheetV2

Now I need to check whether the table is populated BEFORE loading. 

Anyone know how to do that?

• I am using GetWorksheet V2 and ListWorksheet. 
• Using Qlik WebConnectors March-2022 (2.122.1.25087)
• found correct errors by searching times they stopped loading/failed