APIError thrown when trying to write on subarea of spreadsheet
- Dominant language
- Python
- Stars
- 7.5k
- Forks
- 979
- PR merge metrics
- No merged PRs in 30d
Description
Apparently, there is a couple of bugs on calculating the range of cells that need to be updated through update_cells. First of all, it assumes the cells are sorted and only the first and last cells are important to calculate the range. Depending on how someone needs to go through the data, this is not always the case.
Second, if someone does not need to write over the whole table, say you write on n cells in the first column but on the first n-1 for all others, the range can be miscalculated and an API error will be thrown.
### Environment info
Operating System: Ubuntu 18.04 LTS
Python version: 3.6.5
gspread version: ?? (latest from pip)
### Steps to reproduce
1. Open a n x m spreadsheet (or create a new one)
PS: for simplicity, I'm assuming I'll be accessing a 2x2 spreadsheet
2. Modify values from cells that are only in or above the secondary diagonal of the spreadsheet.
PS: on my example, this means modifying only cells A1, B1 and A2.
3. Create a list containing the cells we changed.
PS: cells_to_update = [A1, B1, A2]
4. Call update_cells on that list created on step 3.
PS: range is calculated as "worksheet!A1:A2". Notice that, even if I sort the list as [A1, A2, B1] this will still be an issue, since the new range will be A1:B1, which does not include A2.
### Stack trace or other output that would be helpful
Traceback (most recent call last):
File "/home/user/triangle.py", line 360, in
main()
File "/home/user/triangle.py", line 271, in write_to_sheet
sheet.update_cells(cells_to_update)
File "/home/user/.local/lib/python3.6/site-packages/gspread/models.py", line 609, in update_cells
'values': values_rect
File "/home/user/.local/lib/python3.6/site-packages/gspread/models.py", line 115, in values_update
r = self.client.request('put', url, params=params, json=body)
File "/home/user/.local/lib/python3.6/site-packages/gspread/client.py", line 79, in request
raise APIError(response)
gspread.exceptions.APIError: {
"error": {
"code": 400,
"message": "Requested writing within range ['_Test-0_'!A1:B1], but tried writing to row [2]",
"status": "INVALID_ARGUMENT"
}
}
Contributor guide
Research direction
Start at gspread/models.py, specifically Worksheet.update_cells and the values_update call shown in the traceback. Reproduce the 2x2 case with cells A1, B1, and A2, then inspect how the update range and rectangular values are calculated. Done means non-contiguous changed cells no longer produce an API range error.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python
- Domain
- api
- Issue type
- Bug
- Difficulty
- 3/5
- Estimated time
- 1-2 days
- Activity status
- Stale
- Clarity
- Mostly clear
- Newbie friendliness
- 35/100