pygsheet worksheet size without empty values

Question

How to get row/col number of the last (bottom-right) non-empty cell in worksheet?

Worksheet's rows and cols attributes count also empty cells.

what do you mean by bottom-right non empty cell ? do you mean last cell in the last column with non empty values ? — Nithin, Jul 27 '18 at 19:06

score 3 · Accepted Answer · answered Jul 28 '18 at 10:15

3

There is no direct function to achieve this in pygsheets. But you can figure this out after getting all the values using get_all_values() and by excluding the empty values.

cells = wks.get_all_values(include_empty_rows=False, include_tailing_empty=False, returnas='cells')

bottom_right = cells[-1][-1]

# get row col as bottom_right.row, bottom_right.row,

NB: please use the staging version of pygsheets to run this

answered Jul 28 '18 at 10:15

Nithin

5,470
37
44

1

Thanks for the answer - that works. But, unfortunately, `wks.get_all_values(returnas='cells'))` is much more slower than `wks.get_all_values(returnas='matrix'))` - even for small sheets (less than 100 cells) it takes about one minute. At the other hand, with `returnas=matrix` param it is almost instant. I wish there was a possibility to get all values from the sheet as a matrix, but with traliling empty rows and colums trimmed. (I think there was a possibility for that in former pygsheet version?) – 3voC Jul 30 '18 at 08:39
Former comment concerns master version of pygsheets - after installing staging version as you suggested, everything works very fast. Accepting your answer. – 3voC Jul 30 '18 at 22:32

pygsheet worksheet size without empty values

1 Answers1