How to delete a row in Google Sheets getting the max of a column?

I need to read the Google sheet and getting the max value of a column and deleting the data to update them.

I tried to get the max value and pass to delete_rows method, but don't work well.

spreadsheet = client.open("Test")
worksheet = spreadsheet.worksheet("Test")
data = worksheet.get_all_records(head = 1, value_render_option= 'UNFORMATTED_VALUE')
dataPag = worksheet.col_values(8, value_render_option= 'UNFORMATTED_VALUE')[-1:] # Get the max value of column

Here an example in Pandas what I need

df = pd.DataFrame(data)

max = df['pages'].max()
dropLastValueDf = df.loc[(df['pages']) == max]
dfPages = df.drop(dropLastValueDf.index)

Anyone have an idea that can help me ?

I believe your goal is as follows.

• You want to retrieve the rows of the maximum value from a specific column from a Google Spreadsheet.
• You want to delete the retrieved rows.
• You want to achieve this using gspread.

In this case, how about the following sample script?

From dataPag = worksheet.col_values(8, value_render_option= 'UNFORMATTED_VALUE')[-1:] # Get the max value of column in your script, I guessed that you might want to check column "H" (column number is 8).

Sample script:

Please set your column number to column_number.

import gspread

client = ### Please use your client.

column_number = 8  # Column H.

spreadsheet = client.open("Test")
worksheet = spreadsheet.worksheet("Test")
data = worksheet.get_all_values()[1:]
max_value = max(data, key=lambda x: x[column_number - 1])[column_number - 1]
reqs = [{
    "deleteDimension": {
        "range": {
            "sheetId": worksheet.id,
            "startIndex": i + 1,
            "endIndex": i + 2,
            "dimension": "ROWS"
        }
    }
} for i, e in enumerate(data) if e[column_number - 1] == max_value]
reqs.reverse()
spreadsheet.batch_update({"requests": reqs})

When this script is run, the following flow is run.

1. Retrieve all values from a sheet in a Google Spreadsheet.
2. Retrieve the rows of the maximum value from column "H".
3. Create the request body for deleting rows.
4. Request Sheets API and delete rows.

Reference:

• batch_update of gspread