0

When trying to access a cell via worksheet[#][#] beyond the first 90 cells, I receive a pygsheets.exceptions.CellNotFound error from a worksheet that has almost 3000 cells.

I've run a find to find all the cells with a certain value that I need to use as a reference to retrieve data from an adjacent cell. Going through the produced list I can see all of the cells containing the identifier. Not knowing another method to use, I used worksheet[row][column] using the cell list indices as a reference to find the adjacent cell for the data to be retrieved. When I reach a cell in the 90 range, the script fails in a cell not found error. I've tried directly referencing any cell beyond this 90 range and all I receive is the same error.

The find is necessary as the data that is being retrieved is at "at least" every 13 cells, but some sections expand beyond that limit and therefore throws off a consistent iteration.

for x in range(80, 90):
    print(wks[x][41])

error happens at 89

Traceback (most recent call last):
  File "<<filepath of .py script>>", line 88, in <module>
    print(wks[379][41])
  File "<<pygsheets filepath>>", line 1483, in __getitem__
    raise CellNotFound
pygsheets.exceptions.CellNotFound

pygsheets version 2.0.2 installed via PyCharm

Geza Kerecsenyi
  • 1,127
  • 10
  • 27
user225161
  • 3
  • 1
  • 2

1 Answers1

0

The access was earlier wks[colum][row] this is resolved in the latest pygsheets update to wks[row][column].

Also this type of access is not optimized for loop, please consider getting values using get_all_values and then looping.

data = wks.get_all_values()
for x in range(80, 90):
    print(wks[x][41])

or 

col = wks.get_col(41)
for x in range(80, 90):
    print(col[x])
Nithin
  • 5,470
  • 37
  • 44
  • is there a function to set get_all_values to the cells adjacent to a search criteria? I was using the other method because I couldn't seem to find anything that worked in that manner. – user225161 Sep 06 '19 at 22:07
  • I don't understand your question. the get_all_values will return values as you see them on sheet. So you can loop on then the same way you are looping here. here i can see that you are accessing a single column, use `get_cols` to fetch only a column – Nithin Sep 07 '19 at 06:40
  • ah right, i forgot how i put this question in. the reason this situation came up was because I was using find to search for a bunch of non-contiguous cells in a column with a particular tag. I then wanted to use that list of cells to find the values that are in a column adjacent to them. It's mainly a matter of getting 300 cells vs getting 3000 cells with a bunch of unneeded data. Should I be using find and get_cols to attempt to process the data in this situation for list indices? – user225161 Sep 07 '19 at 15:47
  • well it is better than using wks[x][y], each cell access using this will invoke a get_all_values call. So will be pretty slow. Caching is not implemented (as its pretty hard to do cache invalidation). – Nithin Sep 07 '19 at 18:23