col_count in gspread provides the total number of

Question

When I use col_count in gspread I get the total number of columns of the Google sheet, even though only three columns contain data, also with the rows. It would be more useful if I could get the number of columns and rows with data. Is there a way to do that?

Answer 1

The functions of row_count and col_count will grab all active rows/columns regardless of rather they hold data or not.

Two workarounds for this: 1. build you own function to check if data exists which user user222216 stated above ( I did not check if it worked)

2. Get all the data and count it For example:

   max_rows = lens(worksheet.get_all_values()) #this is a list of list of all data and the length is equal to the number of rows including header row if it exists in data set
   max_cols = len(worksheet.get_all_values()[0]) #this will count the items in the first row, and assumes the first row is either header or has no empty cells
   

Answer 2

ADutta has provided the solution for a single row, this expands on that answer to show the size of the entire non-blank worksheet, assuming consecutive non-blank rows.

    gc = gspread.authorize(credentials)
    wks = gc.open('test1').sheet1
    print ('total rows in worksheet',wks.row_count)   # worksheet rows including empty rows
    print ('total cols in worksheet',wks.col_count)   # worksheet cols including empty cols
    max_cols = 0
    for non_empty_row_num in range(1,wks.row_count): # step thru non-empty rows
        cols_in_row = len(wks.row_values(non_empty_row_num)) # number of cols in this non-empty row
        # print (non_empty_row_num,cols_in_row)
        if cols_in_row > max_cols: max_cols = cols_in_row
        if cols_in_row == 0:  # only process if not empty
            print (non_empty_row_num,max_cols)
            break # stop getting new rows at first empty row
    print (len(wks.get_all_values()))  # just the non-empty row count 

Answer 3

That's seems a problem for me also. But for me these works nicely

gc=gspread.authorize(cred)
sh=gc.open('test').sheet1
a=len(sh.row_values(1))
print('column count',end=' ')
print(a)