Sheets API erroneously rejects new rowCount if (new rowCount * old columnCount) > 5000000 (was: Bug in the number of cells of the dataframe about gspread-dataframe

[robin900]

@devironl So I discovered the cause of your initially reported bug: If you submit to the Sheets API a request to change both a worksheet's rowCount and columnCount, the Sheets API does the following:

1. Using the new rowCount value and the existing columnCount value, calculates whether rowCount * columnCount > 5000000. If the cell count limit is exceeded, a 400 response is returned.
2. Using the new rowCount and new columnCount values, calculate whether rowCount * columnCount > 5000000. If the cell count limit is exceeded, a 400 response is returned.
3. Otherwise, apply the changes to rowCount and columnCount and return a 200 response.

Your particular case -- of wanting rows=200000 and cols=2 for a worksheet with the default size of rows=100, cols=26 -- caused step 1 to return a 400 response.

I am working on a pull request that addresses this case, by calling worksheet.resize() separately for columnCount and then again for rowCount. I'll add you as a reviewer when it's done.

[robin900]

@devironl Sorry, can't add you as a reviewer to PR, but there is full test coverage on the new code and I'll be making a new PyPI release in about 30 min.

[robin900]

@devironl Release 3.2.1 is now in PyPI.

[devironl]

I was finally able to fix this by pre-defining the number of rows and colons of the sheets before setting the dataframe

output_sheet = client.open("sheet_name").add_worksheet(sheet_name, df.shape[0], df.shape[1])
set_with_dataframe(output_sheet, df, resize=True)

I hope this post will help someone else !