Google Sheets API v4 - How to get the last row with value?

Question

How to get the last row with value in the new Google Sheets API v4 ?

i use this to get a range from a sheet:

mService.spreadsheets().values().get("ID_SHEET", "Sheet1!A2:B50").execute();

how to detect the last row with value in the sheet ?

Solution 1

You can set the range to "A2:D" and this would fetch as far as the last data row in your sheet.

Solution 2

I managed to get it by counting the total rows from current Sheets. Then append the data to the next row.

rowcount = this.mService.spreadsheets().values().get(spreadsheetId, range).execute().getValues().size()

Solution 3

Rather than retrieving all the rows into memory just to get the values in the last row, you can use the append API to append an empty table to the end of the sheet, and then parse the range that comes back in the response. You can then use the index of the last row to request just the data you want.

This example is in Python:

#empty table
table = {
    'majorDimension': 'ROWS',
    'values': []
}

# append the empty table
request = service.spreadsheets().values().append(
    spreadsheetId=SPREADSHEET_ID,
    range=RANGE_NAME,
    valueInputOption='USER_ENTERED',
    insertDataOption='INSERT_ROWS',
    body=table)

result = request.execute()

# get last row index
p = re.compile('^.*![A-Z]+\d+:[A-Z]+(\d+)$')
match = p.match(result['tableRange'])
lastrow = match.group(1)

# lookup the data on the last row
result = service.spreadsheets().values().get(
    spreadsheetId=SPREADSHEET_ID,
    range=f'Sheetname!A{lastrow}:ZZ{lastrow}'
).execute()

print(result)

Solution 4

😢 Google Sheets API v4 does not have a response that help you to get the index of the last written row in a sheet (row that all cells below it are empty). Sadly, you'll have to workaround and fetch all sheet rows' into memory (I urge you to comment if I'm mistaken)

Example:

spreadsheet_id = '1TfWKWaWypbq7wc4gbe2eavRBjzuOcpAD028CH4esgKw'
range = 'Sheet1!A:Z'

rows = service.spreadsheets().values().get(spreadsheetId=spreadsheet_id, range=range).execute().get('values', [])
last_row = rows[-1] if rows else None
last_row_id = len(rows)
print(last_row_id, last_row)

Output:

13 ['this', 'is ', 'my', 'last', 'row']

---

💡 If you wish to append more rows to the last row, see this

View more solutions

Comments

[Andras Gyomrey]

You don't need to set up a large limit, by just specifying "A2:D" is enough to find the last value of A,B,C,D.

[yurart]

That's right! Thanks! I was not experienced enough back then :)

[Yaakov5777]

@AndrasGyomrey do you also know how to get only the cells that have a value -- without first retireving them and filtering them out? Like is there a built-in method for only retrieving them in a certian range>

[Andras Gyomrey]

@Yaakov5777 I haven't been working lately with that. But I remember I had to do some normalization myself because the API skipped empty cells without asking.

[Pievis]

yeah but this way you might fetch a lot of data, memory allocation might be an issue.

[Jossef Harush Kadouri]

See this if you want to append more rows at the end

[Mauricio]

This is the best approach available since appending an empty table is actually a lightweight request. I've followed your advice and created a method to fetch the range with values.

[Mauricio]

It won't work if you have empty lines below the data.

[Mauricio]

Actually. Using dummy append for the range TabName!A:ZZZ workerd for me to retrieve the amount of lines.