-2

I am trying to play with google Sheets using python but it is giving me the error. It may be due to not having optimizing code logic.

Error

gspread.exceptions.APIError: {'code': 429, 'message': "Quota exceeded for quota metric 'Write requests' and limit 'Write requests per minute per user' of service 'sheets.googleapis.com' for .", 'status': 'RESOURCE_EXHAUSTED', 'details': [{'@type': 'type.googleapis.com/google.rpc.ErrorInfo', 'reason': 'RATE_LIMIT_EXCEEDED', 'domain': 'googleapis.com', 'metadata': {'quota_limit': 'WriteRequestsPerMinutePerUser',, 'quota_metric': 'sheets.googleapis.com/write_requests', 'service': 'sheets.googleapis.com'}}]}

shop_names = ['wilmermobel', 'masonheron', 'flashree', 'jochiwon', 'senangku', 'dutchgaming', 'hiranai', 'chairsy', 'genelotrics', 'bahemas', 'gameniture', 'martenkaiser', 'secretracer']
web_data_non_ful = ['0', '5.13', '0', '0', '0.00', '0.00', '0', '0', '6.25', '0.58', '0', '0', '0.00']
web_data_late_ship = ['0', '8.89', '0', '0', '8.33', '0.00', '0', '0', '0', '1.51', '0', '0', '0.00']
web_data_pre_time = ['0', '1.50', '2.02', '1.49', '2.17', '0.93', '2.04', '0.94', '1.30', '1.77', '', '', '1.07']
web_data_resp_rate = ['57.00', '96.01', '95.45', '100.00', '98.26', '98.32', '92.97', '91.05', '98.70', '97.44', '100.00', '57.00', '97.04']
web_data_shop_rate = ['0', '4.85', '5.00', '5.00', '4.70', '4.78', '4.89', '4.74', '4.88', '4.74', '5.00', '0', '4.71']
web_data_penal_points = ['0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0']

def sheet_updater('sheet1', col_num, col_data):

    for cn, cd in zip(col_num, col_data):
        try:
            r = 2
            x = 0
            for i in range(13):
                try:
                    time.sleep(1)
                    t = sheet.update_cell(r, cn, cd[x])
                    print(t)
                    r += 1
                except:
                    pass
                x += 1
        except:pass


col_data = [shop_names, web_data_non_ful, web_data_late_ship,web_data_pre_time, web_data_resp_rate, web_data_shop_rate, web_data_penal_points]
col_num = [1, 2, 3, 4, 5, 6, 7]
sheet_updater('sheet1', col_num, col_data)
Tanaike
  • 181,128
  • 11
  • 97
  • 165
Muhammad Usama Hameed
  • 15
  • 5

1 Answers1

0

In your script, how about the following modification?

Modified script:

shop_names = ['wilmermobel', 'masonheron', 'flashree', 'jochiwon', 'senangku', 'dutchgaming', 'hiranai', 'chairsy', 'genelotrics', 'bahemas', 'gameniture', 'martenkaiser', 'secretracer']
web_data_non_ful = ['0', '5.13', '0', '0', '0.00', '0.00', '0', '0', '6.25', '0.58', '0', '0', '0.00']
web_data_late_ship = ['0', '8.89', '0', '0', '8.33', '0.00', '0', '0', '0', '1.51', '0', '0', '0.00']
web_data_pre_time = ['0', '1.50', '2.02', '1.49', '2.17', '0.93', '2.04', '0.94', '1.30', '1.77', '', '', '1.07']
web_data_resp_rate = ['57.00', '96.01', '95.45', '100.00', '98.26', '98.32', '92.97', '91.05', '98.70', '97.44', '100.00', '57.00', '97.04']
web_data_shop_rate = ['0', '4.85', '5.00', '5.00', '4.70', '4.78', '4.89', '4.74', '4.88', '4.74', '5.00', '0', '4.71']
web_data_penal_points = ['0', '0', '0', '0', '0', '0', '1', '0', '0', '0', '0', '0', '0']

def sheet_updater(shop_names, col_num, col_data):
    values = []
    for cn, cd in zip(col_num, col_data):
        try:
            temp = []
            r = 2
            x = 0
            for i in range(13):
                try:
                    temp.append(cd[x])
                    r += 1
                except:
                    pass
                x += 1
            values.append(temp)
        except:
            pass
    return [list(x) for x in zip(*values)]

col_data = [shop_names, web_data_non_ful, web_data_late_ship,web_data_pre_time, web_data_resp_rate, web_data_shop_rate, web_data_penal_points]
col_num = [1, 2, 3, 4, 5, 6, 7]
res = sheet_updater('sheet1', col_num, col_data)
sheet.update('A1', res, value_input_option='USER_ENTERED')
  • In this modification, the values are put to a list, and the list is put to the Spreadsheet using Sheets API. By this flow, the values are put into the Spreadsheet by one API call. By this, I thought that your issue can be removed.

Reference:

Tanaike
  • 181,128
  • 11
  • 97
  • 165