How to find the first empty row of a google spread sheet using python GSPREAD?

Question

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

Syndicalism

Show us what you have so far. – Thoria

That's not what I meant. Show some code iterating through the rows and an attempt to check for an empty one. – Thoria

Hi Alex, I have no idea how to write the codes as this is a new package that i just found and I have not known it very well – Stutman

Then you will have to look at some documentation. The github page you linked looks helpful. Try the get_all_values method. – Thoria

Thanks Alex, I will have a look! – Stutman

Answer 1

I solved this using:

def next_available_row(worksheet):
    str_list = list(filter(None, worksheet.col_values(1)))
    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)

Nazarius

In Python 3, add 'list()' to the function of 'next_available_row': str_list = list(filter(None, sheet.col_values(1))) – Towhead

@PedroLobito this is a great solution! I was using this for many months but suddenly it broke: it's currently inserting 1 row before the next available row.. Any idea why this could be happening? – Tamarisk

@Tamarisk it works fine for me. But in your case, why don't you just increment by one the row number? – Glaydsglaze

@Tamarisk maybe it's because you have empty rows between rows with data. in this case filter() skips them and decrease the count by these rows. I wonder why filter() is using here. – Tsarevna

list(filter(None, worksheet.col_values(1))) when there is a empty line in middle this answer gives wrong value – Casals

Answer 2

This alternative method resolves issues with the accepted answer by accounting for rows that may have skipped values (such as fancy header sections in a document) as well as sampling 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

Avalon

Answer 3

If you can count on all of your previous rows being filled in:

len(sheet.get_all_values()) + 1

will give you the first free row

get_all_values returns a 2D list of the sheet's data. Each nested list is a row, so the length of the 2D list is the number of rows that has any data.

Similar problem is first free column:

from xlsxwriter.utility import xl_col_to_name
# Square 2D list, doesn't matter which row len you check
column_count = len(sheet.get_all_values()[0]) 
column = xl_col_to_name(column_count)

Draconic

Answer 4

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.

Counteraccusation

Answer 5

Updated version @jonathan-b:

def next_available_row(sheet, cols=None):
    cols = sorted([1, 2] if not isinstance(cols, list) or len(cols) != 2 else cols)
    cols = sheet.get_values(
        (1, cols[0]), (sheet.rows, cols[1]),
        returnas='cells', include_tailing_empty_rows=True
    )
    return max([cell.address.index[0] for row in cols for cell in row if cell.value.strip()]) + 1


sh = gc.open_by_key('***')
ws = sh.worksheet_by_title('***')
next_row = next_available_row(ws, [1, 3])
print(next_row)

Aara

Answer 6

import pygsheets        
gc = pygsheets.authorize(service_file='************************.json')
ss = gc.open('enterprise_finance')
ws = ss[0]
row_count = len(ws.get_all_records()) + 2
ws.set_dataframe(raw_output,(row_count,1), copy_index = 'TRUE', copy_head = 'TRUE')
ws.delete_rows(row_count , number=1)

Perplexity

Remember that Stack Overflow isn't just intended to solve the immediate problem, but also to help future readers find solutions to similar problems, which requires understanding the underlying code. This is especially important for members of our community who are beginners, and not familiar with the syntax. Given that, can you edit your answer to include an explanation of what you're doing and why you believe it is the best approach? – Kyongkyoto