CODEWITHSUNDEEP
pythongoogle-sheets-api
KiDo
KiDo

Reputation: 286

Delete a Specific Row From Google Sheets using API

I need to delete a specific row from sheets using Python. There are thousands of records on our sheets, and the data gets updated regularly, and since everything is getting done using Python, this task needs to be done using Python as well.

Now this is what I've got so far, from the documentation and other tutorials:

def connect_to_sheet():
    creds = None
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)    
    service = build('sheets', 'v4', credentials=creds)
    sheet = service.spreadsheets()
    return sheet

The connection to the sheet had to be made in a different function cause I'm using it several times elsewhere, but in the next function is where I'm making the request and getting the error:

request_body = {
              "requests": [
                {
                  "deleteDimension": {
                    "range": {
                      "sheetId": SheetID,
                      "dimension": "ROWS",
                      "startIndex": startIndex,
                      "endIndex": endIndex
                    }
                  }
                }
                ]
            }
                result = sheet.values().batchUpdate(spreadsheetId=SPREADSHEET_ID_4G,body=request_body).execute()

The error:

<HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/sheetID/values:batchUpdate?alt=json returned " Invalid JSON payload received. Unknown name "requests": Cannot find field.". Details: "[{'@type': 'type.googleapis.com/google.rpc.BadRequest', 'fieldViolations' : [{'description': 'Invalid JSON payload received. Unknown name "requests": Cannot find field.'}]}]">

Every example and similar question on SOF I've searched for, is using the exact same request! But I still couldn't figure the problem causing this error.
Any help will be really appreciated, thanks.

Upvotes: 1

Views: 1366

Answers (1)

Tanaike
Tanaike

Reputation: 201378

Modification points:

  • deleteDimension is used for the method of spreadsheets.batchUpdate in Sheets API. But in your script, that is used with the method of spreadsheets.values.batchUpdate. I think that this is the reason of your issue.

When above points are reflected to your script, it becomes as follows.

Modified script:

From: 
result = sheet.values().batchUpdate(spreadsheetId=SPREADSHEET_ID_4G,body=request_body).execute()
To: 
result = sheet.batchUpdate(spreadsheetId=SPREADSHEET_ID_4G,body=request_body).execute()
  • In your script, I think that your request body for the method of spreadsheets.batchUpdate is correct.
  • As the additional information, for example, when you want to delete the rows from 1 to 2, please set startIndex and endIndex as 0 and 2, respectively.

Note:

  • In this modification, it supposes that your sheet can be used for using the method of spreadsheets.batchUpdate.

References:

Upvotes: 3

Related Questions