How to find the first empty line of google distribution sheet using python GSPREAD?

Question

I'm struggling to write codes that find me on the first blank line of a google sheet.

I am using the gspread package from github.com/burnash/gspread

I would be glad if someone can help :)

Currently, I just imported modules and opened a worksheet

scope = ['https://spreadsheets.google.com/feeds'] credentials = ServiceAccountCredentials.from_json_keyfile_name('ddddd-61d0b758772b.json', scope) gc = gspread.authorize(credentials) sheet = gc.open("Event Discovery") ws = sheet.worksheet('Event Discovery') 

I want to find row 1158, which is the first empty row of a table with a function, which means that every time an old empty row is filled, it will find the next empty row. Look here

Answer 1

I solved this with:

 def next_available_row(worksheet): str_list = filter(None, worksheet.col_values(1)) # fastest return str(len(str_list)+1) scope = ['https://spreadsheets.google.com/feeds'] credentials = ServiceAccountCredentials.from_json_keyfile_name('auth.json', scope) gc = gspread.authorize(credentials) worksheet = gc.open("sheet name").sheet1 next_row = next_available_row(worksheet) #insert on the next available row worksheet.update_acell("A{}".format(next_row), somevar) worksheet.update_acell("B{}".format(next_row), somevar2) 

Answer 2

 def find_empty_cell(): alphabet = list(map(chr, range(65, 91))) for letter in alphabet[0:1]: #look only at column A and B for x in range(1, 1000): cell_coord = letter+ str(x) if wks.acell(cell_coord).value == "": return(cell_coord) 

I use this messy function to find the first empty cell. I cannot find an empty row because the other columns already have values.

Oh, and there are some problems between 2.7 and 3.6 with the display that require me to turn the alphabet into a string.

Answer 3

This alternative method solves the problems with the accepted answer by taking into account rows that may have missing values ​​(for example, sections of unusual headers in a document), as well as selections of the first N columns:

 def next_available_row(sheet, cols_to_sample=2): # looks for empty row based on values appearing in 1st N columns cols = sheet.range(1, 1, sheet.row_count, cols_to_sample) return max([cell.row for cell in cols if cell.value]) + 1