Python gspread - get the last row without fetching all the data?-CodePudding

Looking on tips how to get the data of the latest row of a sheet. I've seen solution to get all the data and then taking the length of that.

But this is of course a waste of all that fetching. Wondering if there is a smart way to do it, since you can already append data to the last row 1 with worksheet.append_rows([some_data])

CodePudding user response：

I found this code in another question, it creates a dummy append in the sheet.

After that, you can search for the location later on:

def get_last_row_with_data(service, value_input_option="USER_ENTERED"):
    last_row_with_data = '1'
    try:
        # creates a dummy row

        dummy_request_append = service.spreadsheets().values().append(
            spreadsheetId='<spreadsheet id>',
            range="{0}!A:{1}".format('Tab Name', 'ZZZ'),
            valueInputOption='USER_ENTERED',
            includeValuesInResponse=True,
            responseValueRenderOption='UNFORMATTED_VALUE',
            body={
                "values": [['']]
            }
        ).execute()

        # Search the dummy row

        a1_range = dummy_request_append.get('updates', {}).get('updatedRange', 'dummy_tab!a1')
        bottom_right_range = a1_range.split('!')[1]
        number_chars = [i for i in list(bottom_right_range) if i.isdigit()]
        last_row_with_data = ''.join(number_chars)

    except Exception as e:
        last_row_with_data = '1'

    return last_row_with_data

You can see a sample of Append in this documentation.

However, for me it is just easier to use:

# The ID of the sheet you are working with. 

Google_sheets_ID = 'ID_of_your_Google_Sheet' 

# define the start row that has data
# it will later be replace with the last row
# in my test sheet, it starts in row 2

last_row = 2 

# code to the get the last row
# range will be the column where the information is located
# remember to change "sheet1" for the name of your worksheet. 

response = service.spreadsheets().values().get(
    spreadsheetId = Google_sheets_ID,
    range = 'sheet1!A1:A'
)excecute()

#Add the initial value where the range started to the last row with values 
Last_row  = len(response['values']) - 1 

#If you print last row, you should see the last row with values in the Sheet. 
print(last_row)

CodePudding user response：

I used the solution @buran metnion. If you init the worksheet with

add_worksheet(title="title", rows=1, cols=10)

and only append new data via

worksheet.append_rows([some_array])

Then @buran's suggestion is brilliant to simply use

worksheet.row_count