Search code examples
pythongoogle-sheetsgoogle-sheets-apigspread

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

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!

Solution

  • 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".

    References: