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

Question

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 ?

Tanaike · Accepted Answer · 2025-03-26 07:23:06Z

0

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.

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

Reference:

batch_update of gspread

answered Mar 26 at 7:23

Tanaike

203k12 gold badges126 silver badges222 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Cesar Augusto Mar 26 at 12:43

It's works, thank you ! Just needed to transform for int on lambda function int(x[column_number- 1]) and works well. Thank you @Tanaike

Tanaike Mar 27 at 0:06

@Cesar Augusto Thank you for replying. I'm glad your issue was resolved. Thank you, too.

Hieu Tran Ngoc Minh · Accepted Answer · 2025-03-26 07:12:50Z

0

you can use ~ or != to remove all rows have a column pages = max value.

df = df.loc[~(df['pages']==df['pages'].max())]

# The same with
df = df.loc[(df['pages']!=df['pages'].max())]

answered Mar 26 at 7:12

Hieu Tran Ngoc Minh

11 bronze badge

Collectives™ on Stack Overflow

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

2 Answers 2

Sample script:

Reference:

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Sample script:

Reference:

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related