Search code examples
pythongoogle-sheets-apigspread

Cannot do batch_update on Google Sheet with more than 999 rows

Getting the following error when trying to do a batch_update post to a google sheet. There are 5600 rows in the sheet I am trying to post to

('/home/xx/xxx/xx.csv', <Spreadsheet u'spreadsheet name' id:id#>, 'A5600')
Traceback (most recent call last):
  File "xx.py", line 50, in <module>
    pasteCsv(csvFile, sheet, cell)
  File "xx.py", line 38, in pasteCsv
    return sheet.batch_update(body)
  File "/home/xx/.local/lib/python2.7/site-packages/gspread/models.py", line 146, in batch_update
    'post', SPREADSHEET_BATCH_UPDATE_URL % self.id, json=body
  File "/home/xx/.local/lib/python2.7/site-packages/gspread/client.py", line 73, in request
    raise APIError(response)
gspread.exceptions.APIError: {u'status': u'INVALID_ARGUMENT', u'message': u'Invalid requests[0].pasteData: GridCoordinate.rowIndex[5599] is after last row in grid[999]', u'code': 400}

is there a way to change the grid from [999] to a higher number so that I am able to post the csv file contents?

Solution

  • Answer:

    You can make a batch request to increase the number of rows in the sheet before you insert the CSV content.

    Example using a Batch Request:

    spreadsheetId = "your-spreadsheet-id"
sheetId = "sheet-id"

sh = client.open_by_key(spreadsheetId)

request = {
    "requests": [
        {
            "insertDimension": {
                "range": {
                  "sheetId": sheetId,
                  "dimension": "ROWS",
                  "startIndex": 999,
                  "endIndex": 5599
                },
                "inheritFromBefore": false
            }
        }
    ]
}

result = sh.batch_update(request)

    You will need to change sheetId to be the gid of the sheet within the Spreadsheet you are updating.

    Remember: rows and columns are 0-indexed, so inserting rows below row 1000 will mean having a startIndex of 999.

    Example using gspread methods:

    Alternatively in gspread you can directly use the gspread.models.Worksheet.add_rows() method:

    sh = client.open_by_key(spreadsheetId)
ws = sh.get_worksheet(index)
ws.add_rows(4600)

    References: