How to find the first empty row of a google spread

问题:

I am struggling to write codes that find me the first empty row of a google sheet.

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

I would be glad if someone can help :)

I currently have just imported modules and opened the 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 the worksheet with a function, which means everytime the old empty row is filled, it will find the next empty row See here

回答1:

I solved this using:

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)

回答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 kinda sloppy function to find the first empty cell. I can't find an empty row because the other columns already have values.

Oh, and there are some issues between 2.7 and 3.6 with mapping that required me to turn the alphabet into a string.