I have data in a google sheet with the following structure:

I'd like to use pygsheets in order to delete the rows that match date == '2022-01-02', or any given date that I want to delete.

Is there an easy way to do so by using pyghseets?

CodePudding user response：

I believe your goal is as follows.

• You want to search a value from the column "A" of a sheet. And, you want to delete the searched rows.
• For example, when a value of 2022-01-02 is found at column "A" of row 3 in a sheet, you want to delete the row.
• You want to achieve this using pygsheets for python.

In this case, how about the following sample script?

Sample script:

import pygsheets

client = ### # Please use your client.

spreadsheet_id = "###" # Please set your Spreadsheet ID.
sheet_name = "Sheet1" # Please set your sheet name.
search = "2022-01-02" # Please set the search value.
searchCol = 1 # Please set the search column. 1 is column "A".

sh = client.open_by_key(spreadsheet_id)
wks = sh.worksheet_by_title(sheet_name)
values = wks.get_all_values(value_render="FORMATTED_VALUE")
deleteRows = [i for i, r in enumerate(values) if r[searchCol - 1] == search]
if deleteRows == []:
    exit()
reqs = [
    {
        "deleteDimension": {
            "range": {
                "sheetId": wks.id,
                "startIndex": e,
                "endIndex": e   1,
                "dimension": "ROWS",
            }
        }
    }
    for e in deleteRows
]
reqs.reverse()
client.sheet.batch_update(spreadsheet_id, reqs)

• When this script is run, the value of search is searched from the column "A" of "Sheet1", and the searched rows are deleted.

Reference:

• Sheet API Wrapper