gspread error code 400 "Range ('name'!name) exceeds grid limits"

Question

I am using python 2.7 on a Raspberry Pi with version 3.0.0 of gspread.

When I use gspread's get_all_values() function, I get the following error:

File "/home/pi/DB/GSheets/GoogleSheets.py", line 121, in GetAll
    listOfLists = googleSheetName.worksheet(GSTabName).get_all_values()
  File "/home/pi/.local/lib/python2.7/site-packages/gspread/models.py", line 444, in get_all_values
    data = self.spreadsheet.values_get(self.title)
  File "/home/pi/.local/lib/python2.7/site-packages/gspread/models.py", line 110, in values_get
    r = self.client.request('get', url, params=params)
  File "/home/pi/.local/lib/python2.7/site-packages/gspread/client.py", line 79, in request
    raise APIError(response)
gspread.exceptions.APIError: {
  "error": {
    "code": 400,
    "message": "Range ('name'!name) exceeds grid limits. Max rows: 52, max columns: 17",
    "status": "INVALID_ARGUMENT"
  }
}

This issue did not occur for me in a previous version of gspread. The current version, previously known as "v4", seems to have introduced this. As far as I can tell, the get_all_values function has not changed between the two versions. Does anyone know what might be causing this suddenly?

Thanks in advance!

EDIT:
'name' is being used as a placeholder for the worksheet's name.

EDIT2:
Note: I have connected the project to a completely new Google Sheet with identical worksheets (and names) and the error still occurs. All of the worksheet names are alphanumeric and contain no spaces or special characters.

EDIT3:
The name of the tab that it gets stuck on is in the form of "ABC1", but this is the first tab that gets read.

After changing it to "SheetA", if response.ok: in client.py succeeds 4 times and then MySQLdb crashes due to an SQL syntax error in its own distribution files (likely caused by bad data being used to build a command). Before changing the tab name to be "SheetA", response.ok would succeed 3 times and then fail. When I force the exception to not be thrown, the data sent (a list of lists containing the worksheet values) is no different than what is sent when the exception is not triggered.

The actual dimension of the worksheet are 52 x 17 (rows x columns). The dimensions in the APIError always match the dimensions of the worksheet.

EDIT4:
This has been discovered: The following worksheet (tab) name formats throw an APIError (code 400): "ABC1", "ABC123", however "ABCD123" or "ABC1D" work. I also noticed that when I move a worksheet/tab with a valid name (Called FirstSheet below) before a worksheet with a non-valid name that the error thrown includes the FirstSheet name like this: read.exceptions.APIError: { "error": { "code": 400, "message": "Range (FirstSheet!ABC123) exceeds grid limits. Max rows: 132, max columns: 17", "status": "INVALID_ARGUMENT" }, but only upon crashing at the non-valid name. The previous, valid, worksheets are read without issue. The worksheets all vary in size.

Wait

Could you please share the name of the worksheet you're getting the values from? – Series

This is not for a personal project, so I am reluctant to at the moment. Why is the name of the worksheet needed? – Wait

Thank you for your attention to this, by the way. I hope the current limitations I have mentioned do not hinder your ability to assist me. – Wait

@Series I have updated the question to show my most recent efforts. The previous sheet name (not worksheet/tab) was in the form of "ABCD1 Database" and the new one is "XYZ_Proxy". I realize this is not really related to the issue, but it needed to be done anyways. It does however show me that the Google Sheet name was not the cause, and I feel that the worksheet/tab names should not be causing any issues either. Again, I greatly appreciate your attention to my issue and would like to discuss it further. – Wait

I can't reproduce this issue and since the name of the worksheet (tab) is used in the failing line of code (e.g. data = self.spreadsheet.values_get(self.title)) my first guess was that something could be wrong with it. It's really hard to track the problem when you only have a partial view of the context. Can you at least mock the name of the worksheet/tab as you did with the spreadsheet name? Is there anything suspicious about it? Try to rename it to SheetA and see if the problem goes away. Also, can you share the actuall dimensions of the worksheet? – Series

@Series Thanks for your reply. I have updated my question to show the outcomes. I would like to note that in my client.py file it is importing V2 from .urls, not V4. I don't know if that is of significance or not. Also, on a previous Pi I was working on my installation of gspread had a v4 folder that __init__.py was using for Client and I was able to solve the issue I'm currently having by changing it to the Client class (in client.py) found outside of v4. The release this Pi has is not the same, so I was unable to use that solution. It seems something has changed in gspread – Wait

I have tried rearranging the order of the tabs, and it seems that this issue only occurs for this specific tab (though, bad data seems to be sent when the exception is not thrown). – Wait

By the way, when I force the exception to not be thrown, the data sent (a list of lists containing the worksheet values) is no different than what is sent when the exception is not triggered. – Wait

@Series This has been discovered: The following worksheet (tab) name formats throw an APIError (code 400): "ABC1", "ABC123", however "ABCD123" or "ABC1D" work. I noticed that when I move a worksheet/tab that is valid (Called FirstSheet below) that the error thrown includes the FirstSheet name like this: read.exceptions.APIError: { "error": { "code": 400, "message": "Range (FirstSheet!ABC123) exceeds grid limits. Max rows: 132, max columns: 17", "status": "INVALID_ARGUMENT" } Both worksheets are of different sizes – Wait

Answer 1

I had the same issue, although my code was in ruby. Underscores did not work.

What happens is that google gets confused when the file name ends with an integer, for example your sheet name is called ABC123. So ABC123X would had worked fine.

To fix it I had to escape it using single quotes - FirstSheet!'ABC123'

My code looked something like this:

service.get_spreadsheet(file_id, ranges: "'ABC123'")

Amphiaster

To prevent errors like this, make a habit of always surrounding the name of the sheets with single quotes. So 'sheet1'!A2:C33 instead of sheet1!A2:C33 – Aribold

Holy moly this deserves more upvotes. THANK YOU. – Michamichael

Your answer helped me to understand my issue, but your fix showed me another error, and the filename was added with the single quotes. I used more than three letters at first and it solved my issue. – Digitoxin

Answer 2

I'm unsure why this works, but a workaround that I discovered is to surround the worksheet name(s) with underscores (in Google Sheets):

_Worksheet1_

Wait

Answer 3

I got the same error when attempting to append cells via the range method. I discovered if an additional empty cell/col was added to the sheet this error didn't happen.

Squish

I noticed also that if you put underscores _ before and after the worksheet name like so _Worksheet1_ the error does not occur. I had it as an answer but for some reason it was downvoted, so I removed it. – Wait

Answer 4

I had the same issue and after trying and trying realized that I was trying to update a cell that was not in a range contained in the sheet (sheet had columns until F and a function was calling an "P" column). Probably not all cases are because that, but it was in mine, so perhaps someone come here looking for solution and this could be useful.

Liveried

Answer 5

In my case, I had to open the Google Sheet file and click on the "Add 1000 rows" button at the bottom of the sheet to fix the problem.

Effect