How to handle Quota exceeded error 429 in google sheets when inserting data through python?

Question

I am new to python and currently handling one of freelancing assignments. In my project I am given subject name xls file which on weekly occasions get updated with new names. I am able to scrape data for the given name and through python insert the obtained data in the google sheet. I have more than 5,000 names in file for now. I thought my code was ready but after 8-10 names I encountered error 429 which states quota limit exceeded. I checked in the website and it seems Google allows limit of 500 requests per 100 seconds per project, and 100 requests per 100 seconds per user. Taking the limitation in consideration I made changes in my code and added sleep so this error is not faced but it seems I am in a misunderstanding here, as per my thinking, my code does 7 requests in a loop run and I run 9 loops before making a sleep(500) execution but I still face the same error. I am sure I am missing out something very noticeable but after 3 days of trying to figure out on my own, I am losing confidence so any help is appreciated, below is the code for reference.

import requests
from bs4 import BeautifulSoup
import gspread
import pandas as pd
from oauth2client.service_account import ServiceAccountCredentials
from pandas import ExcelWriter
import time


# define the scope
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

# add credentials to the account
creds = ServiceAccountCredentials.from_json_keyfile_name('/content/drive/MyDrive/ListUpdate.json', scope)

# authorize the clientsheet
client = gspread.authorize(creds)

# get the instance of the Spreadsheet
sheet = client.open('JP_combined_Strip')

# get the first sheet of the Spreadsheet
sheet_instance = sheet.get_worksheet(0)


list_of_lists = sheet_instance.get_all_values()        # req 1
print(len(list_of_lists))

start = int((sheet_instance.cell(2, 1).value))         # req 2 this column is for recording the last row number where this program left off to continue from there next on next run
end = len(list_of_lists) + 1

for i in range(start,end,1):

    ##
    ## code for scraping
    ##
    ##
    ##
    ## scraped data 
    ##

        sheet_instance.update_cell(i, 3, data_1 )        # req 3
        sheet_instance.update_cell(i, 4,data_2)     # req 4
        sheet_instance.update_cell(i, 5, data_3)        # req 5
        sheet_instance.update_cell(i, 6, data_4)                # req 6
        sheet_instance.update_cell(i, 7, data_5)            # req 7
        sheet_instance.update_cell(i, 8, data_6)            # req 8
        sheet_instance.update_cell(i, 9, data_7)        # req 9 (req 7 under loop)
        if i%9 == 0:
            sheet_instance.update_cell(2, 1, i) # req 8 under loop when loop is run9 times = 9 * 7 = 63 requests total
            ## total requests should be 66 in total before each sleep statement is executed which is less than 100 requests as stated in google
            print("sleep")
            time.sleep(500)

The code runs successfully till first sleep, 7 records doo get executed but next batch failsw with this error.

Answer 1

The issue is that you sleep only after a certain amount of requests and ignore that it can fail anywhere in between, so any API call is a potential failure.

There are many solutions to this issue. The best, from my perspective, is to wrap every call into a function with a try-catch block and sleep functionality there.

import time

def api_call_handler(func):
  # Number of retries
  for i in range(0, 10):
    try:
      return func()
    except Exception as e:
      print(e)
      time.sleep(2 ** i)
  print("The program couldn't connect to the Google Spreadsheet API for 10 times. Give up and check it manually.")
  raise SystemError

Example of usage for this code:

# Before
sheet_instance.update_cell(i, 3, data_1)
# Now
api_call_handler(lambda: sheet_instance.update_cell(i, 3, data_1))

This solution adds extra structure to the code and makes it wordy but it is bullet-proof.