CODEWITHSUNDEEP
pythongoogle-sheetsgoogle-apigoogle-api-clientgoogle-sheets-api
Acksell
Acksell

Reputation: 61

Google Sheets API HttpError 500 and 503

Edit: solved, the issue was on Google's side. Occurs when requesting a sheet which had diagrams that had invalid intervals in them. Reported bug to Google.

Note: This issue has persisted for more than 2 days. I had it previously but it was automatically resolved after waiting a day. It has since rearisen.

I am currently using the Google Sheets API through Google's python api client. The authentication is OAuth2.0 and I did not change anything significant in my codebase but all of the sudden I am getting 100% error ratio, and it seems like it should be on Google's end. I fear that I am banned from using the API indefinitely, is this the case? My guess is that when I launched the script and immediately cancelled it with ctrl+c because I wanted to run a new version of it caused some issues.

I tried creating another project and using its credentials to make the request and got the same error. Tried having my friend run the script authenticating through his google account and he receives the same error. The independent source code can be found here

About the source code: The get_credentials() (and therefore the authentication) is entirely copied from Google's python quickstart script as seen here https://developers.google.com/sheets/quickstart/python.

Tracebacks:

Traceback (most recent call last):
  File "Google_sheets.py", line 164, in <module>
    ss=Spreadsheet(SPREADSHEET_ID)
  File "Google_sheets.py", line 83, in __init__
    spreadsheetId=self.ssId, includeGridData=True).execute()['sheets']}
  File "C:\Users\Larsson\AppData\Local\Programs\Python\Python35-32\lib\site-packages\oauth2client\util.py", line 137, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\Users\Larsson\AppData\Local\Programs\Python\Python35-32\lib\site-packages\googleapiclient\http.py", line 838, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 500 when requesting https://sheets.googleapis.com/v4/spreadsheets/12YdppOoZUNZxhXvcY_cRgfXEfRnR_izlBsF8Sin3rw4?alt=json&includeGridData=true returned "Internal error encountered.">

After retrying shortly after, I get another error:

Traceback (most recent call last):
  File "Google_sheets.py", line 164, in <module>
    ss=Spreadsheet(SPREADSHEET_ID)
  File "Google_sheets.py", line 83, in __init__
    spreadsheetId=self.ssId, includeGridData=True).execute()['sheets']}
  File "C:\Users\Larsson\AppData\Local\Programs\Python\Python35-32\lib\site-packages\oauth2client\util.py", line 137, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "C:\Users\Larsson\AppData\Local\Programs\Python\Python35-32\lib\site-packages\googleapiclient\http.py", line 838, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 503 when requesting https://sheets.googleapis.com/v4/spreadsheets/12YdppOoZUNZxhXvcY_cRgfXEfRnR_izlBsF8Sin3rw4?includeGridData=true&alt=json returned "The service is currently unavailable.">

Upvotes: 2

Views: 12205

Answers (5)

Strijder
Strijder

Reputation: 46

I've encountered the same issue. Inspired by the top answer the origin of the problem was indeed on the Gsheet itself. Disabling the filters on the sheet solved the issue.

Upvotes: 0

Srdjan Grubor
Srdjan Grubor

Reputation: 2675

I got this error too and after some fiddling I found that it's something with data validation on dropdown boxes on mine. Re-edit your validation, select the same cell and resave, rinse and repeat for all such fields and it should work.

PS: Given that this seems to affect other types of data, I'd recommend removing parts of your document until it starts working on the script side so your last action would indicate what cells are problematic.

Upvotes: 2

Acksell
Acksell

Reputation: 61

Solved, the issue was on Google's side. Occurs when requesting a sheet which had diagrams that had invalid/unselected intervals in them. Reported bug to Google.

Fix by changing all invalid diagrams to valid ranges.

Upvotes: 4

Teyam
Teyam

Reputation: 8082

As described in Standard Error Responses, error codes #500 and #503 are errors associated with servers. Recommended action for this is not to retry the query more than once.

To handle these error codes:

A 500 or 503 error might result during heavy load or for larger more complex requests. For larger requests consider requesting data for a shorter time period. Also consider implementing exponential backoff.

An exponential backoff may be a good strategy for handling those errors if a high volume of requests or heavy network traffic causes the server to return errors.

In addition to that, you should also check if your application is exceeding the usage limits. If it does, it is possible that your application code should be optimized to make fewer requests. You may opt to request additional quota in the Google API Console under the Quotas tab of a project if needed.

Upvotes: -3

Sayali
Sayali

Reputation: 366

500 and 503 are Google Server issues. You will need to implement exponential backoff so that you can retry the transaction again. Check this link - https://developers.google.com/admin-sdk/directory/v1/limits

And, there is a usage limit for all APIs. Check out this link - https://developers.google.com/gmail/api/v1/reference/quota

Upvotes: 0

Related Questions