Python Google Sheets API Limit 429 Error with Loop

Question

I am running a python script that is taking specific columns from one Google Sheet to another Google Sheet via the Google Sheets API and Gspread package, but I am running into a 429 Error when I am pushing a list to the new spreadsheet. This error is connected to too many requests, but I am not sure what is making my push run multiple times, rather than once. Is it possible that my worksheet.update_cells(updated_values) is being included in the loop?

Error:

APIError: {
  "error": {
    "code": 429,
    "message": "Quota exceeded for quota group 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:*id*'.",
    "status": "RESOURCE_EXHAUSTED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.Help",
        "links": [
          {
            "description": "Google developer console API key",
            "url": "https://console.developers.google.com/project/*id*/apiui/credential"
          }
        ]
      }
    ]
  }
}

Code:

# column names
print(columns) # ['date', 'b_clicks', 'b_cpc']

# store count of column names
gs_columns = []

# count columns
for i in range(0,len(columns)):
    gs_columns.append(i+1)

print(gs_columns) # [1,2,3]

updated_values = []

for col_val, col_name in zip(gs_columns, columns):
    worksheet_range = worksheet.range(1, col_val, 500, col_val); # [row_start, col_start, row_end, col_end]
    print(type(worksheet_range))
    column_data = df_full[col_name].values.tolist();
    for cell, data in zip(worksheet_range, column_data):
        cell.value = data
        updated_values.append(cell)

worksheet.update_cells(updated_values)
print(updated_values)
print(type(updated_values))
print(len(updated_values))

Print:

['date', 'b_clicks', 'b_cpc']
[1, 2, 3]
<class 'list'>
<class 'list'>
<class 'list'>
[<Cell R1C1 1514764800000000000>, <Cell R2C1 1514851200000000000>, <Cell R3C1 1514937600000000000>....<Cell R345C3 3.21>, <Cell R346C3 3.92>]
<class 'list'>
1038

`update_cells()` should only result in one HTTP call. I'd suspect `worksheet.range` in your sample code instead. You could try to debug further and put a counter into 'Client. request()` method. — Burnash, Dec 13 '18 at 16:33
Mind elaborating on what you suspect about `worksheet.range`? I'm not sure I follow. Also what would the `Client.request()` method look like to debug? That is definitely something I would like to investigate. Another thought came to mind is the length of the list and if updating 1038 at the same time on `update_cells()` is too much to handle for the API call, but I don't think that size should be issue for the method as this is not a large amount relative to many Google Sheet/Excel tasks. — cphill, Dec 13 '18 at 16:53
It's just that I see that the `range` call is in the loop, while `update_cells` gets called only once. What I mean by debugging `Client.request` is locating the source code of this method and inserting breakpoint or print statement to see how many actual API requests you're sending. Could you also reduce the number of items in the first `for` loop and see if the problem goes away? — Burnash, Dec 13 '18 at 20:29
@Burnash I figured out the issue. The problem was that the list I was looping through and mapping to the worksheet_range values had a different length. It appears that this does not work nicely and was throwing off the API call — cphill, Dec 13 '18 at 20:40

score 5 · Accepted Answer · edited Feb 02 '19 at 13:21

Encountering the same problem is there a way we can limit the program as per the following criteria such as below in the for loop that we can limit the no of requested to api and match the following criteria

time.sleep(10)

About developers.google.com :

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.

Python Google Sheets API Limit 429 Error with Loop

1 Answers1