Google Sheets API- How do I append new rows on TOP of table?(Android)

Question

In my Android App, I would like to append a row (with new food order information) to the top of a google sheets table. From the https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append documentation it sounds like I'm only able to append to the bottom of a table. But I realy need to append the new row from TOP, and I doubt that's impossible.
I was able to append rows to the bottom, using this code:

String ORDER_SHEET_ID = "<my speardsheet id here>";
String ORDERS_RANGE_DINING = "DiningOrders!C5:I";
//...                       
this.mService.spreadsheets().values().append(ORDER_SHEET_ID,ORDERS_RANGE_DINING, body)
.setValueInputOption("RAW").setInsertDataOption("INSERT_ROWS").execute();

Is it possible to append the row to the top of the table? How? Thanks!

score 2 · Answer 1 · edited Jan 31 '18 at 14:52

This is what I use to insert a new row. It creates the row and uses the paste command to set the data so I can do everything in one HTTP round trip. Alternatively you could update the data using a second request if you want. I'm not sure if the Paste command can insert rows or not, but this works.

InsertDimensionRequest insertRow = new InsertDimensionRequest();
insertRow.setRange(new DimensionRange().setDimension("ROWS").setStartIndex(0).setEndIndex(1).setSheetId(sheetId));
PasteDataRequest data = new PasteDataRequest().setData("Data").setDelimiter("\t")
        .setCoordinate(new GridCoordinate().setColumnIndex(0).setRowIndex(0).setSheetId(sheetId));

BatchUpdateSpreadsheetRequest r = new BatchUpdateSpreadsheetRequest().setRequests(Arrays.asList(
        new Request().setInsertDimension(insertRow),
        new Request().setPasteData(data)
        ));
BatchUpdateSpreadsheetResponse response = this.mService.spreadsheets().batchUpdate(SHEET_ID, r).execute();

score 0 · Answer 2 · answered Oct 23 '17 at 15:35

You may refer with this thread.

In case if you need to add a new row on top (first row) and copy formula from the first top row then you'll need to copy formulas across using getFormulas() and setFormulas() functions. You can change the value of firstRow to 2 if your spreadsheet has headers for example.
function addFirstRow() {
    var firstRow = 1;
    var sh = ss.getActiveSheet();
    var lCol = sh.getLastColumn();
    var range = sh.getRange(firstRow, 1, 1, lCol);
    var formulas = range.getFormulas();
    sh.insertRowsAfter(1, 1);
    newRange = sh.getRange(firstRow, 1, 1, lCol);
    newRange.setFormulas(formulas);
}

You may also check this thread: Having new entries show up top in google sheets.

Google Sheets API- How do I append new rows on TOP of table?(Android)

2 Answers2

Linked