How can I make a batch request for an entire Google Sheet's formatting in Python?-CodePudding

Using Python, I'm trying to check each cell in a Google Sheet for a strikethrough and return the rows without a strikethrough. The problem I run into is reaching my quota limit for the current minute since I'm checking 150 rows. Plus I need to check 11 sheets with anywhere from 50-200 rows each.

Is there a way to "batchGet()" formatting, not just values, so I don't hit my quota limit?

I'm not sure what example code is really needed so here's my working "is this cell formatted with a strikethrough" definition:

def row_has_strikethrough(sheet, i):
    return 'strikethrough=True' in str(get_user_entered_format(sheet, 'A'   str(i)))

This works within my while loop but again, I hit the quota:

last_row = int(sheet.acell('C1').value)  # 166
i = 3
while i <= last_row:
    if not row_has_strikethrough(sheet, i):
        records  = get_record(sheet, MAPPING, i)
    i  = 1

Any help would be greatly appreciated!

CodePudding user response：

I believe your goal is as follows.

About I'm trying to check each cell in a Google Sheet for a strikethrough and return the rows without a strikethrough., from your script of return 'strikethrough=True' in str(get_user_entered_format(sheet, 'A' str(i))), I thought that you might have wanted to check whether the cell value of column "A" has the strikethrough.
And, from your showing script, I thought that you might be using gspread for python.
You want to reduce the process cost of the script.

If my understanding is correct, how about the following sample script?

Sample script:

spreadsheetId = '###' # Please set your Spreadsheet ID.
client = gspread.authorize(credentials) # Here, please use your credentials and client
sheetNames = ['Sheet1', 'Sheet2'] # Please set the sheet names you want to retrieve the values.

# 1. Retrieve textFormat from each sheet using one API call.
access_token = credentials.access_token
ranges = '&'.join(['ranges='   e for e in sheetNames])
url = 'https://sheets.googleapis.com/v4/spreadsheets/'   spreadsheetId   '?fields=sheets(data(rowData(values(userEnteredFormat(textFormat(strikethrough))))))&'   ranges
res1 = requests.get(url, headers={'Authorization': 'Bearer '   access_token})
obj = res1.json()
rowNumbers = {}
for i, d in enumerate(obj['sheets']):
    sheet = []
    for j, r in enumerate(d['data'][0].get('rowData')):
        if 'values' not in r:
            sheet.append(j)
    rowNumbers[sheetNames[i]] = sheet

# 2. Retrieve values from each sheet using one API call.
spreadsheet = client.open_by_key(spreadsheetId)
res2 = spreadsheet.values_batch_get(sheetNames)['valueRanges']

# 3. Retrieve the rows from each sheet by checking the rows without the strikethrough at the column "A".
values = {}
for i, s in enumerate(res2):
    temp = []
    for j, r in enumerate(s['values']):
        sheet = sheetNames[i]
        if j in rowNumbers[sheet]:
            temp.append(r)
    values[sheet] = temp

print(values) # Here, you can see the result value.

In this script, 2 API calls of Sheets API are used.

Testing:

When this script is used in a sample Spreadsheet, the following result is obtained.

{
  'Sheet1': [['a2', 'b2', 'c2'], ['a5', 'b5', 'c5'], ['a6', 'b6', 'c6']],
  'Sheet2': [['a3', 'b3', 'c3'], ['a5', 'b5', 'c5'], ['a6', 'b6', 'c6']]
}

This output value has the rows of each sheet. Those rows are the rows without the strikethrough at column "A".

Sample script:

Testing:

References: