How to delete rows in google spreadsheet using SHEET API v4 by Python?

Question

My code is the following

def write_cells(spreadsheet_id, update_data):
updating = sheet_service.spreadsheets().values().\
        batchUpdate(spreadsheetId=spreadsheet_id, body=update_data)
updating.execute()

spreadsheet_data = [
    { 
        "deleteDimension": {
        "range": {
          "sheetId": sheet_id,
          "dimension": "ROWS",
          "startIndex": 5,
          "endIndex": 100
         }              
        }
       }
]    

update_spreadsheet_data = {
    'valueInputOption': 'USER_ENTERED',
    'data': spreadsheet_data
}

update_data = update_spreadsheet_data

write_cells(spreadsheet_id, update_data)

I have the following error message

HttpError                                 Traceback (most recent call last)
<ipython-input-64-0ba8756b8e85> in <module>()
----> 1 write_cells(spreadsheet_id, update_data)

2 frames

/usr/local/lib/python3.6/dist-packages/googleapiclient/http.py in execute(self, http, num_retries)
    838       callback(resp)
    839     if resp.status >= 300:
--> 840       raise HttpError(resp, content, uri=self.uri)
    841     return self.postproc(resp, content)
    842 

HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/1lAI8gp29luZDKAS1m3P62sq0kKCn8eaMUvO_M_J8meU/values:batchUpdate?alt=json returned "Invalid JSON payload received. Unknown name "delete_dimension" at 'data[0]': Cannot find field.">

I don't understand this: "Unknown name delete_dimension". I'm unable to resolve it. Any help is appreciated, thanks.

score 6 · Accepted Answer · answered May 16 '19 at 12:39

6

You want to delete rows using Sheets API with Python.

If my understanding is correct, how about this modification?

Modification points:

When delete rows in Spreadsheet, please use spreadsheets().batchUpdate().
I think that spreadsheet_data is correct.
In this case, please modify the request body to {"requests": spreadsheet_data}.

Modified script:

def write_cells(spreadsheet_id, update_data):
    # Modified
    updating = sheet_service.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheet_id, body=update_data)
    updating.execute()


spreadsheet_data = [
    {
        "deleteDimension": {
            "range": {
                "sheetId": sheet_id,
                "dimension": "ROWS",
                "startIndex": 5,
                "endIndex": 100
            }
        }
    }
]

update_spreadsheet_data = {"requests": spreadsheet_data} # Modified

update_data = update_spreadsheet_data
write_cells(spreadsheet_id, update_data)

Note:

This modified script supposes that you have already been able to use Sheets API.

Reference:

DeleteDimensionRequest

If I misunderstood your question and that was not the result you want, I apologize.

answered May 16 '19 at 12:39

Tanaike

181,128
11
97
165

2

How do you specify the sheet's tab name? That DeleteDimensionRequest only takes a sheetID, dimension, startIndex, and endIndex. – Michael Brant Aug 28 '19 at 01:34
@Michael Brant Thank you for your comment. Although I'm not sure about your detail script, how about retrieving the sheet ID from the sheet name? So is this thread useful for your situation? https://stackoverflow.com/q/57166738/7108653 If this didn't resolve your issue, please post it as new question by including the detail information for replicating your issue. By this, it will users think of the issue and solution. – Tanaike Aug 28 '19 at 03:30
2

@MichaelBrant This is a little bit misleading. The sheet_id in the data field is not the ID of the document, but of the tab. So the integer after #gid in the URL. The ID of the sheet itself is contained in the batchUpdate call. – JonnyZoo Apr 29 '20 at 09:22
Why are you no longer using `valueInputOption` in the request body as specified in the docs here https://developers.google.com/resources/api-libraries/documentation/sheets/v4/python/latest/sheets_v4.spreadsheets.values.html#batchUpdate and here https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate#request-body? Where in the docs does it say to use request bodies formatted like `{"request": data}`? – lampShadesDrifter Jul 24 '20 at 00:46
I see my confusion: The `spreadsheet` API (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate) is different from the `spreadsheet.values` API (https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values). When just googling for answers ad hoc, the difference in the documentation is not obvious to notice and can cause confusion. – lampShadesDrifter Jul 24 '20 at 02:29
@lampShadesDrifter I would like to support you. But I have to apologize for my poor English skill. Because I cannot understand what you want to do. This is due to my poor English skill. I deeply apologize for this. So can you post your issue as new question by including the detail information. It will help users think of the solution. If you can cooperate to resolve your issue, I'm glad. – Tanaike Jul 24 '20 at 03:54
Why do people do this: update_data = update_spreadsheet_data, in my opinion just clutters the code with redundant lines, no? – okkko Dec 06 '20 at 11:54
@okkko I have to apologize for my poor skill. Unfortunately, I cannot answer for your question. Because in my answer, I just modified the OP's script. I think that the owner of this question might have the reason. So how about asking it to the owner of this question? – Tanaike Dec 06 '20 at 22:13
1

No problem man, it was just a comment. I upvoted your answer. Thank you for sharing. – okkko Dec 08 '20 at 07:58
@okkko Thank you for replying and your support. – Tanaike Dec 09 '20 at 01:01

How to delete rows in google spreadsheet using SHEET API v4 by Python?

1 Answers1

Modification points:

Modified script:

Note:

Reference:

Linked