CODEWITHSUNDEEP
pythongoogle-apigoogle-sheets-apigoogle-api-python-client
resolver101
resolver101

Reputation: 2255

Google sheets API call receives error RATE_LIMIT_EXCEEDED and doesnt retrieve the data i need

When running the code below I get the follow error "RATE_LIMIT_EXCEEDED". I've added a few delays but not sure what else I can do to correct the issue. Does anyone know how to correct the issue?

> Exception has occurred: HttpError <HttpError 429 when requesting
> https://sheets.googleapis.com/v4/spreadsheets/1FJiVwQu2AAca06dpkoh20zQYGyyQ8RBZJ8xWZgGlcS4/values/form%20%20-%20v1.01%211%3A200?alt=json
> returned "Quota exceeded for quota metric 'Read requests' and limit
> 'Read requests per minute per user' of service 'sheets.googleapis.com'
> for consumer 'project_number:1010550314584'.". Details: "[{'@type':
> 'type.googleapis.com/google.rpc.ErrorInfo', 'reason':
> 'RATE_LIMIT_EXCEEDED', 'domain': 'googleapis.com', 'metadata':
> {'quota_metric': 'sheets.googleapis.com/read_requests', 'consumer':
> 'projects/1010550314584', 'quota_limit':
> 'ReadRequestsPerMinutePerUser', 'service':
> 'sheets.googleapis.com'}}]">   File
> "OEE.py", line 43, in
> fetch_data_from_google_spreadsheet
>     range=RANGE_NAME).execute())   File "Forge.py", line 43, in <module>
>     forge_OEE = OEE.fetch_data_from_google_spreadsheet('blhalblahblha',google_credentials=creds)


# gets data from google spreadsheet 
def fetch_data_from_google_spreadsheet(SPREADSHEET_ID, google_credentials):
    
    # looks at connecting to the google api service 
    # If modifying these scopes, delete the file token.pickle.
    SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']


    service = build('sheets', 'v4', credentials=google_credentials)

    # Call the Sheets API
    sheet = service.spreadsheets()
    
    # Returns all sheets (tabs) in a spread sheet
        time.sleep(3)
    return_sheet_ID = sheet.get(spreadsheetId=SPREADSHEET_ID, ranges=None, includeGridData=None, x__xgafv=None).execute()

    # extracts just the sheets 
    individual_sheets = return_sheet_ID.get("sheets")

    list_of_dict_sheets = []

    # loops through each tab and returns the rows within each sheet 
    for item in individual_sheets:
        # returns the sheet names to pass into sheet values
        RANGE_NAME = item.get("properties").get("title") + "!" + "1" + ":" + str(item.get("properties").get("gridProperties").get("rowCount"))
        time.sleep(3)
        # returns the rows within the spreadsheets 
        list_of_dict_sheets.append (sheet.values().get(spreadsheetId=SPREADSHEET_ID,
                                range=RANGE_NAME).execute())
    df2 = None 
    # loops through each sheet and returns the details  
    for each_sheet in list_of_dict_sheets:
        df = pd.DataFrame(each_sheet.get("values"))
        df = df.rename(columns=df.iloc[0])
        df = df.iloc[1:]
        df2 = pd.concat([df,df2])
    return df2

Upvotes: 1

Views: 1204

Answers (1)

Linda Lawton - DaImTo
Linda Lawton - DaImTo

Reputation: 116918

RATE_LIMIT_EXCEEDED is flood protection you are going to fast.

You need to slow down the number of requests are you are sending. detect the error and implement exponential back off. This way when it fails you wait for a short time and then try again, if it fails again you wait a little longer until the api will let you run again.

So the solution is to add more delays.

Upvotes: 1

Related Questions