How to insert row after the last row with value?

9
votes

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.

4
androidgoogle-sheetsgoogle-sheets-api
This answer to your earlier question still applied: request the values from the sheet, see how many rows you actually get (empty trailing rows are omitted). It seems wasteful to request the contents when you only need the data range, but I didn't see another way with API v4. – user3717023
thanks but i need a simple equivalent to .getLastRow from Google Apps Script, is a missing feature this ? – seba123neo

4 Answers

5
votes

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.

2
votes

You can use AppendCellsRequest to append a row. The below methods should get you going. I haven't included the getRowDataListForCellStrings method as it is rather application specific.

First create a Request object containing a AppendCellsRequest:

public BatchUpdateSpreadsheetResponse appendWorksheet(String cellValues) throws SpreadsheetException {
    AppendCellsRequest appendRequest = new AppendCellsRequest();
    appendRequest.setSheetId( mSheet.getProperties().getSheetId() );
    appendRequest.setRows( getRowDataListForCellStrings(cellValues) );
    appendRequest.setFields("userEnteredValue");

    Request req = new Request();
    req.setAppendCells( appendRequest );

    return executeBatchRequest(req);
}

Then call batchUpdate on the spreadsheets() interface:

BatchUpdateSpreadsheetResponse executeBatchRequest(Request request) throws SpreadsheetException {
    List<Request> requests = new ArrayList<>();
    requests.add( request );

    BatchUpdateSpreadsheetRequest batchRequest = new BatchUpdateSpreadsheetRequest();
    batchRequest.setRequests( requests );

    try {
          return  mService.spreadsheets().batchUpdate(mSpreadsheet.getSpreadsheetId(), batchRequest).execute();
    } catch (IOException e) {
        throw new SpreadsheetException(e);
    }
}

Unfortunately there doesn't seem to be a way to know which rows were updated. Not does is seem possible to set valueInputOption when appending in this way.

2
votes

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.

1
votes

There is actually a way to ask the API. I am using this on my node.js client (pretty sure it's very similar)

    var sheets = google.sheets('v4');
  sheets.spreadsheets.get({
    auth: auth,
    spreadsheetId: 'spreadsheet_id',
    includeGridData: true
  }, function (err, response) {
    if (err) {
      console.log('The API returned an error: ' + err);
    } else {

      var last_row = response.sheets[0].data[0].rowData.length;

    }
  });