CODEWITHSUNDEEP
pythonpython-2.7gspread
Thành Đạt
Thành Đạt

Reputation: 327

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

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

Upvotes: 19

Views: 25148

Answers (6)

maksam07
maksam07

Reputation: 41

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)

Upvotes: 0

Rakshit Jain
Rakshit Jain

Reputation: 1

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)

Upvotes: -2

Pedro Lobito
Pedro Lobito

Reputation: 99001

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)

Upvotes: 39

David Jay Brady
David Jay Brady

Reputation: 1164

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)

Upvotes: 2

Jonathan B.
Jonathan B.

Reputation: 2980

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

Upvotes: 7

G_L
G_L

Reputation: 103

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.

Upvotes: 0

Related Questions