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

Question

How to get the last row with a 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 determine the last row with a value in a worksheet?

Answer 1

You can set the range to "A2: D" and this will extract to the last row of data on your sheet.

Answer 2

You do not need. Set a huge range (e.g. A2:D5000 ) to ensure that all your lines will be located in it. I do not know if this has any further influence, memory consumption or something like that may be increased, but at the moment this is normal.

 private List<String> getDataFromApi() throws IOException { String spreadsheetId = "1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms"; String range = "A2:D5000"; List<String> results = new ArrayList<String>(); ValueRange response = this.mService.spreadsheets().values() .get(spreadsheetId, range) .execute(); List<List<Object>> values = response.getValues(); if (values != null) { results.add("Name, Major"); for (List row : values) { results.add(row.get(0) + ", " + row.get(3)); } } return results; } 

Look at the for loop for (List row : values) . If you have two tables in a table, you will get two elements in the values list.

Answer 3

I managed to get it by counting the full rows from the current tables. Then add the data to the next line.

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