"exceeds grid limits" when calling get_all_values on worksheet

[amuuz74]

Describe the bug I got an exception of "Range ('tab01'!TAB1) exceeds grid limits" from googleapiclient.

To Reproduce Write a very simple demo program as t.py:

def main():
    import pygsheets

    gc = pygsheets.authorize(client_secret="credentials.oauth2.json")
    sh = gc.open("sample001")
    tabs = sh.worksheets()
    for tab in tabs:
        print("%s : col %d , row %d" % (tab.title, tab.cols, tab.rows))

        data = tab.get_all_values()
        for row in data:
            for col in row:
                print(col, end="\t")
            print("")

if __name__ == '__main__':
    main()

# see error
Traceback (most recent call last):
  File "t.py", line 23, in <module>
    main()
  File "t.py", line 15, in main
    data = tab.get_all_values() 
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/worksheet.py", line 464, in get_all_values
    include_tailing_empty_rows=include_tailing_empty_rows, **kwargs)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/utils.py", line 178, in wrapper
    return method(self, *args, **kwargs)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/worksheet.py", line 355, in get_values
    date_time_render_option=date_time_render_option, **kwargs)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/client.py", line 209, in get_range
    date_time_render_option)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/sheet.py", line 344, in values_get
    return self._execute_requests(request)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/sheet.py", line 359, in _execute_requests
    response = request.execute(num_retries=self.retries)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/googleapiclient/_helpers.py", line 134, in positional_wrapper
    return wrapped(*args, **kwargs)
  File "/Users/charlesz/.virtualenvs/gapi/lib/python3.7/site-packages/googleapiclient/http.py", line 898, in execute
    raise HttpError(resp, content, uri=self.uri)
googleapiclient.errors.HttpError: <HttpError 400 when requesting https://sheets.googleapis.com/v4/spreadsheets/SHEET_ID/values/tab01?majorDimension=ROWS&valueRenderOption=FORMATTED_VALUE&dateTimeRenderOption=SERIAL_NUMBER&alt=json returned "Range ('tab01'!TAB1) exceeds grid limits. Max rows: 20, max columns: 7">

I guess maybe there are something wrong with the range calculation?

System Information

• OS: MacOS 10.15.3 with python 3.7.6
• pygsheets version : 2.0.3.1
• pygsheets installed from (github or pypi): pypi

[amuuz74]

BTW, get_values("A1", "G20") works fine for me. I could calculate the upper-left and bottom-right position by myself, but it feels strange the get_all_values is not working properly.

[amuuz74]

I have tried this:

    import pprint
    import pygsheets

    gc = pygsheets.authorize(client_secret="credentials.oauth2.json")
    sh = gc.open("sample001")
    tabs = sh.worksheets()
    for tab in tabs:
        print("%s : col %d , row %d" % (tab.title, tab.cols, tab.rows))
        print("-" * 80)
        grange = pygsheets.GridRange(worksheet=tab, start=None, end=None)
        grange.set_worksheet(tab)
        print(repr(grange))
        print(repr(grange.start.label))
        print(repr(grange.end.label))
        print(grange.label)

the output is:

tab01 : col 7 , row 20
--------------------------------------------------------------------------------
<GridRange tab01>
''
''
tab01
tab02 : col 52 , row 50
--------------------------------------------------------------------------------
<GridRange tab02>
''
''
tab02

I guess you cannot use range=tab_name, but have to use range=tab_name!START:END for latest google sheet api call.

possible related library version:

google-api-core 1.16.0 google-api-python-client 1.8.0 google-auth 1.11.3 google-auth-httplib2 0.0.3 google-auth-oauthlib 0.4.1 googleapis-common-protos 1.51.0 oauth2client 4.1.3 oauthlib 3.1.0

Also, inside address.py

class GridRange(object):
    ...
    def __init__(self, label=None, worksheet=None, start=None, end=None, worksheet_title=None,
                 worksheet_id=None, propertiesjson=None):
        ...
        self._worksheet_title = worksheet_title
        self._worksheet_id = worksheet_id
        self._worksheet = worksheet
        self._start = Address(start, True)
        self._end = Address(end, True)
        # if fill_bounds and self._start and not self._end:
        #     if not worksheet:
        #         raise InvalidArgumentValue('worksheet need to fill bounds.')
        #     self._end = Address((worksheet.rows, worksheet.cols), True)
        # if fill_bounds and self._end and not self._start:
        #     self._start = Address('A1', True)
···
Those lines which got commented out looks like the reason of this issue.

[nithinmurali]

I guess you cannot use range=tab_name, but have to use range=tab_name!START:END for latest google sheet api call.

No its just tab_name for all cells in a sheet. ref.

I am unable to reproduce this. In your case Range ('tab01'!TAB1) the TAB1 should not be there.

can you try

tab.get_all_values(grange=GridRange(worksheet=self, start=None, end=None))

[amuuz74]

hi, Nithin, sorry for the delay.

I have modified your test code a little bit, to make the test running, hope it's not breaking what you want to test.

        data = tab.get_all_values(grange=pygsheets.GridRange(worksheet=tab, start=None, end=None))

Here is my entire test code:

#!/usr/bin/env python

def main():
    import pprint
    import pygsheets
    from utils.gsheet_helper import GSheetHelper

    gc = pygsheets.authorize(client_secret="credentials.oauth2.json")
    sh = gc.open("sample001")
    tabs = sh.worksheets()
    for tab in tabs:
        # tab01 = sh.worksheet_by_title("tab01")
        print("%s : col %d , row %d" % (tab.title, tab.cols, tab.rows))
        print("-" * 80)

        # bgn = "A1"
        # end = GSheetHelper.idx2pos(tab.rows, tab.cols)
        # data = tab.get_values(bgn, end)
        # data = tab.get_all_values()  # returnas="matrix", majdim="ROWS")
        data = tab.get_all_values(grange=pygsheets.GridRange(worksheet=tab, start=None, end=None))
        for row in data:
            for col in row:
                print(col, end=" ")
            print("")

if __name__ == '__main__':
    main()

The output is:

py tt.py
tab01 : col 7 , row 20
--------------------------------------------------------------------------------
Traceback (most recent call last):
  File "tt.py", line 29, in <module>
    main()
  File "tt.py", line 21, in main
    data = tab.get_all_values(grange=pygsheets.GridRange(worksheet=tab, start=None, end=None))
  File "~/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/worksheet.py", line 464, in get_all_values
    include_tailing_empty_rows=include_tailing_empty_rows, **kwargs)
  File "~/.virtualenvs/gapi/lib/python3.7/site-packages/pygsheets/utils.py", line 178, in wrapper
    return method(self, *args, **kwargs)
TypeError: get_values() got multiple values for argument 'start'

Hope this could help.

[nithinmurali]

oops my bad, use get_values

data = tab.get_values(grange=pygsheets.GridRange(worksheet=tab, start=None, end=None))

[amuuz74]

Hmmm.... This works fine.

py tt.py
tab01 : col 7 , row 20
--------------------------------------------------------------------------------
A01 B01 C01 D01 E01 F01 G01
A02 B02 C02 D02 E02 F02 G02
A03 B03 α β E03 F03 G03
A04 B04 γ δ E04 F04 G04
A05 B05 C05 D05 E05 F05 G05
A06 B06 C06 D06 E06 F06 G06
A07 B07 C07 D07 E07 F07 G07
A08 B08 C08 D08 E08 F08 G08
A09 B09 C09 D09 E09 F09 G09
A10 B10 C10 D10 E10 F10 G10

[amuuz74]

Is there any DEBUG code you would like to put into the lib code? I can try that for you. I guess, most likely it should be something related to google API lib different version or something similar.

[amuuz74]

So,... that's it? Should I just close this ticket?

[nithinmurali]

Hi, sorry got busy with some other work. If you are still looking for a solution can you try

data = tab.get_values(start=None, end=None)

[nithinmurali]

closing for now. if you still have this issue, please reopen.