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.

[This answer](http://stackoverflow.com/a/37367707) 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. — , Jun 03 '16 at 14:52
thanks but i need a simple equivalent to .getLastRow from Google Apps Script, is a missing feature this ? — seba123neo, Jun 04 '16 at 17:07

score 5 · Answer 1 · answered Feb 09 '17 at 21:29

5

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.

answered Feb 09 '17 at 21:29

dvc

61
1
8

I know this is two years later, but when I have an empty column the data gets appended starting at the last column with data. So if I have A1 => "hello" C1 => "world", and do an append with the whole sheet as the range, it gets appended to C2 onwards. – Healyhatman Feb 18 '19 at 05:26
1

Although not desirable, I think that is working as designed. If you read the example shown in the link from my answer you'll see that the API looks for discrete tables in the data and appends to the last table. Tables are separated by blank cells, so in your case the last table is c1 so data is appended to c2. – dvc Feb 19 '19 at 06:26
This API is frustrating and confusing. CAN someone SIMPLY make a C# library?????? – PastExpiry.com Oct 16 '21 at 05:59

score 2 · Answer 2 · answered Jun 03 '16 at 16:20

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.

thanks but i need a simple equivalent to .getLastRow from Google Apps Script, is a missing feature this ? — seba123neo, Jun 04 '16 at 17:07

score 2 · Accepted Answer · answered Jun 07 '16 at 19:23

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.

score 1 · Answer 4 · answered Jul 18 '16 at 12:47

1

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;

    }
  });

answered Jul 18 '16 at 12:47

Delneg

11
1
2

1

You should really add some explanation as to why this code should work - you can also add comments in the code itself - in its current form, it does not provide any explanation which can help the rest of the community to understand what you did to solve/answer the question. – ishmaelMakitla Jul 18 '16 at 12:53
1

He is getting all the rows and then do a count. If the sheet is too long, it might not be as efficient as using the append method – jleviaguirre Apr 13 '19 at 02:19

How to insert row after the last row with value?

4 Answers4

Linked