How to insert row after the last row with value?

Question

I am inserting data into a spreadsheet with the new Google Sheets API v4, the code works perfect and the data it is inserted well in the sheet.

But how to find out the last row with data to add the data after this ?

List<List<Object>> arrData = getData();

ValueRange oRange = new ValueRange();
oRange.setRange("Pedidos!AXXXXXXX"); // I NEED THE NUMBER OF THE LAST ROW
oRange.setValues(arrData);

List<ValueRange> oList = new ArrayList<>();
oList.add(oRange);

BatchUpdateValuesRequest oRequest = new BatchUpdateValuesRequest();
oRequest.setValueInputOption("RAW");
oRequest.setData(oList);

BatchUpdateValuesResponse oResp1 = mService.spreadsheets().values().batchUpdate("ID_SPREADSHEET", oRequest).execute();

Is there some trick in the A1 notation for this?

I need an equivalent to .getLastRow from Google Apps Script.

Answer 1

If you use the append feature and set the range to the entire sheet, the API will find the last row and append the new data after it.

This web page explains it.

https://developers.google.com/sheets/api/guides/values#appending_values

Here is some sample code:

String range="Sheet1";
insertData.setValues(rows);
AppendValuesResponse response=service.spreadsheets().values()
.append(spreadsheetId,range,insertData).setValueInputOption("USER_ENTERED")
            .execute();

Note that the response will tell you where it was inserted.

Answer 2

The v4 API has no way to ask "what is the last row with data", as it's a different style of API than the Apps Script API. You can infer the last row yourself by requesting the data and counting the offset from your first requested row to the last returned row.