Internal Server Error everytime I load the script

YassineB_23 — Wed, 20 Apr 2022 07:52:11 GMT

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.

Re: Internal Server Error everytime I load the script

BREEZE — Thu, 14 Apr 2022 07:50:40 GMT

Same problem here!!

Re: Internal Server Error everytime I load the script

NadiaB — Wed, 20 Apr 2022 13:25:14 GMT

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/Administer/Advanced-configuration.htm

Kind Regards.

Re: Internal Server Error everytime I load the script

Onyi_Nwabia-Robbins — Sat, 28 May 2022 22:13:22 GMT

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"
        }

I am looping through 243 spreadsheet keys to get all the relevant worksheet keys
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

topic Re: Internal Server Error everytime I load the script in App Development

Internal Server Error everytime I load the script

Re: Internal Server Error everytime I load the script

Re: Internal Server Error everytime I load the script

Re: Internal Server Error everytime I load the script