Why am I receiving a quota limit error? Google Cloud Platform Compute Engine VM

user_14767352 0
Viewed 11614

So I have a python script set up in Google Cloud Compute engine that is set to run with a cron tab periodically throughout a day. But recently, the script returns this error,

gspread.exceptions.APIError: {'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:583704109550'.", 'status': 'RESOURCE_EXHAUSTED', 'details': [{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'RATE_LIMIT_EXCEEDED', 'domain': 'googleapis.com', 'metadata': {'quota_metric': 'sheets.googleapis.com/read_requests', 'quota_limit': 'ReadRequestsPerMinutePerUser', 'service': 'sheets.googleapis.com', 'consumer': 'projects/583704109550'}}]}

I've tried to do research into why its throwing it but am at a loss due to my inexperience in the field. Here is the script is anyone is wondering,

from woocommerce import API
from df2gspread import df2gspread as d2g
from df2gspread import gspread2df as g2d
from collections import defaultdict
import pandas as pd
import gspread
from oauth2client.service_account import ServiceAccountCredentials
def parseData(entry):
        #Extract relectant information (all key value pairs that are listed under meta_data)
        d = {pair["key"]:pair["value"] for pair in entry["line_items"][0]["meta_data"]}
        d["Event"] = entry["line_items"][0]["name"]
        return d
wcapi = API(
    url="REDACTED",
    consumer_key="REDACTED",
    consumer_secret="REDACTED",
    version="wc/v3",
    query_string_auth="true"
)
entries = []
pageNum = 1
while True:
        #API is paginated with products per page limit of 100
        rawEntries = wcapi.get("orders", params = {"per_page": 100, "page": pageNum}).json()
        #Page until there are no entries on a page
        if len(rawEntries) == 0:
                break
        entries.extend([parseData(e) for e in rawEntries])
        pageNum += 1
rawEvents = defaultdict(list)
for entry in entries:
        #Organize entries by their event
        rawEvents[entry["Event"]].append(entry)
events = {k: pd.DataFrame(v).fillna('') for (k,v) in rawEvents.items()} #Built a dataframe for each event
#Upload to Google Sheets using gspread and df2gspread
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name('/home/shahav2016/GoogleKeyJSON.json', scope)
gc = gspread.authorize(credentials)
spreadsheet_key = 'REDACTED FOR PRIVACY REASONS' #The name of the spreadsheet - look at the URL
for event, df in events.items():
        #Filter out test events
        if event not in ['REDACTED FOR PRIVACY']:
                #Upload the data to the correct sheet
                d2g.upload(df, spreadsheet_key, event, row_names = False, credentials = credentials)
google-cloud-platform google-sheets python
1 Answers

The Google Sheets API has a limit on how many requests you can do per 100 seconds. This is from the documentation page of the Sheets API v4.

This version of the Google Sheets API has a limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Limits for reads and writes are tracked separately. There is no daily usage limit.

To view or change usage limits for your project, or to request an increase to your quota, do the following:

If you don't already have a billing account for your project, then create one. Visit the Enabled APIs page of the API library in the API Console, and select an API from the list. To view and change quota-related settings, select Quotas. To view usage statistics, select Usage.

Furthermore, there are three options to work around this limitation:

  1. Increase the quota limit of the Sheets API you are calling in the developer console.
  2. Since you are using a for loop, requests are sent very fast. I think it would be wise to put a sleep somewhere in that loop so that you will not go over the 100 requests per 100 seconds.
  3. If things then go too slow, try to upload multiple changes to spreadsheets at once, this mechanism is called batching. This will also reduce the number of API requests.
user_79505920
Related