Changing Python code logic to avoid Quota exceeded for quota metric 'Write requests' error

Question

I have a python code ready with me which is working correctly. It uses gspread to connect with Google Sheets. E is the input column and K is the output column My code does below things :

Step 1 : clears the cell and put white color

Step 2 : Find the content between round brackets with help of regular expression

Step 3: It appends 'A' for the first value in the cell and 'B' value for the rest of the values and puts red color

However, after a while, it throws this error : gspread.exceptions.APIError: {'code': 429, 'message': "Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service I am aware of this error. It is because I am updating the cells too many times.

Question 1 :Is there an alternate way by which I can clear the cells at once without updating it each time.

Question 2 : Is there an alternate way by which I can store all the values of the cells at one time and then print the output all at once without updating one at a time.

val_list = sheet.col_values(5)
for j in range(1, len(val_list)):
  x = val_list[j]
  sheet.update('K' + str(j + 1), '')
  sheet.format('K' + str(j + 1), {'backgroundColor': {'red': 1.0,
             'green': 1.0, 'blue': 1.0}})

  m = re.findall(r'[(](.*?)[)]', x)
  count = len(m)
  for i in range(0, count):
    if i == 0:
        val = m[0] + 'A'
        sheet.update('K' + str(j + 1), val + ' is updated now')
        sheet.format('K'+ str(j+1), {
                            "backgroundColor": {
                             "red": 1.0,
                              "green": 0.0,
                              "blue": 0.0
                              }
                              })
    elif i > 0:
        val = val + '
' + m[i] + 'B'
        sheet.update('K' + str(j + 1), val + ' are updated now')
        sheet.format('K'+ str(j+1), {
                            "backgroundColor": {
                             "red": 1.0,
                              "green": 0.0,
                              "blue": 0.0
                              }
                              })

question from:https://stackoverflow.com/questions/65868613/changing-python-code-logic-to-avoid-quota-exceeded-for-quota-metric-write-reque

Answer 1

Waitting for answers