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

That's not what I meant. Show some code iterating through the rows and an attempt to check for an empty one. — Alex Hall, Nov 24 '16 at 08:41
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 — Thành Đạt, Nov 24 '16 at 08:44
Then you will have to look at some documentation. The github page you linked looks helpful. Try the `get_all_values` method. — Alex Hall, Nov 24 '16 at 08:46

Pedro Lobito · Accepted Answer · 2020-04-09T03:58:35.673

37

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)

edited Apr 09 '20 at 03:58

answered Feb 27 '17 at 01:12

Pedro Lobito

94,083
31
258
268

10

In Python 3, add 'list()' to the function of 'next_available_row': ```str_list = list(filter(None, sheet.col_values(1))) ``` – PythonSherpa Dec 25 '17 at 13:04
@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? – jed Jul 25 '19 at 21:14
3

@jed it works fine for me. But in your case, why don't you just increment by one the row number? – Dark Templar Aug 02 '19 at 11:37
1

@jed 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. – Sergey L. Aug 06 '22 at 12:42
1

`list(filter(None, worksheet.col_values(1)))` when there is a empty line in middle this answer gives wrong value – Alupotha Feb 22 '23 at 14:47

score 7 · Answer 2 · answered May 17 '19 at 23:33

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

score 2 · Answer 3 · answered Apr 09 '20 at 00:56

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)

score 0 · Answer 4 · answered Jan 14 '18 at 21:41

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.

score 0 · Answer 5 · answered Jun 18 '23 at 20:20

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)

score -2 · Answer 6 · edited Apr 21 '20 at 08:14

-2

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)

edited Apr 21 '20 at 08:14

krivard

652
6
17

answered Apr 20 '20 at 14:33

Rakshit Jain

1

2

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? – Jeremy Caney Jul 02 '23 at 00:19

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

6 Answers6

Linked