Search code examples
pythongspread

gspread delete a range of cells (up)

Using the GSpread package for Python, how would I use a batch_update to delete a range of cells upwards?

Example code:

        sheet_id = self.worksheet.gs_worksheet._properties['sheetId']
        start_index_col = self.cell_data_by_row_col[0][0].col - 1
        end_index_col = self.cell_data_by_row_col[0][-1].col - 1
        start_index_row = self.cell_data_by_row_col[0][0].row
        end_index_row = self.cell_data_by_row_col[0][0].row

        self.worksheet.gs_worksheet.batch_update({
            'requests': [
                {
                    'deleteRangeRequest': {
                        'range': {
                            'sheetId': sheet_id,
                            'startRowIndex': start_index_row,
                            'endRowIndex': end_index_row,
                            'startColumnIndex': start_index_col,
                            'endColumnIndex': end_index_col,
                        },
                        'shiftDimension': 'ROWS',
                    }
                }
            ]
        })

Response:

  File "C:\Program Files\Python37\lib\site-packages\gspread\utils.py", line 559, in wrapper
    return f(*args, **kwargs)
  File "C:\Program Files\Python37\lib\site-packages\gspread\models.py", line 1166, in batch_update
    for vr in data
  File "C:\Program Files\Python37\lib\site-packages\gspread\models.py", line 1166, in <listcomp>
    for vr in data
TypeError: string indices must be integers
Solution

  • I believe your goal as follows.

    • You want to delete range using deleteRangeRequest of the method of "spreadsheets.batchUpdate" in Sheets API.
    • You want to achieve this using gspread with python.

    Modification points:

    • At gspread, it seems that batch_update(body) is the method of class gspread.models.Spreadsheet. In your script, I thought that you might use it as the method of class gspread.models.Worksheet. I think that this is the reason of the error message of string indices must be integers.
      • batch_update(data, **kwargs) of class gspread.models.Worksheet is the method of "spreadsheets.values.batchUpdate".
    • When the DeleteRangeRequest is used in the "spreadsheets.batchUpdate" method, please use it as deleteRange.

    When above point is reflected to the script, it becomes as follows. Unfortunately, from your script, I couldn't understand about the variable of self.worksheet.gs_worksheet. So in this modification, I use other variable name.

    Modified script:

    spreadsheetId = "###"  # Please set the Spreadsheet Id.
sheetName = "Sheet1"  # Please set the sheet name.

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sheet_id = spreadsheet.worksheet(sheetName)._properties['sheetId']

start_index_col = self.cell_data_by_row_col[0][0].col - 1
end_index_col = self.cell_data_by_row_col[0][-1].col - 1
start_index_row = self.cell_data_by_row_col[0][0].row
end_index_row = self.cell_data_by_row_col[0][0].row

spreadsheet.batch_update({
    'requests': [
        {
            'deleteRange': {
                'range': {
                    'sheetId': sheet_id,
                    'startRowIndex': start_index_row,
                    'endRowIndex': end_index_row,
                    'startColumnIndex': start_index_col,
                    'endColumnIndex': end_index_col,
                },
                'shiftDimension': 'ROWS',
            }
        }
    ]
})

    References: